为什么VALUES在发送到数据库时写在引号之间？

Question

I am not entirely sure if I should post it on here or not since it does not contain any real coding, but I have been thinking about it for a long time and no one could really give me a good answer (from the people of I have been asking / the research done). I have been taught to write (when inserting something into the DB) to write:

$sql = "INSERT INTO usertimes (name,date,amount,times)
VALUES ('$name', CURDATE(), '$amount', '$times') ON DUPLICATE KEY UPDATE    
date=CURDATE(), amount='$amount', times='$times'"; 

This is just an example however, but I have been wondering; Why are there quotes around the variable names? Don't these variables remain variables technically speaking? I did some research as mentioned before, but that doesn't really explain why we do so, I just find stuff related to this: Add quotes around variable My apologies if it's a newb question and-or if it shouldn't be posted here. I'm just curious why we do so since from the start we learn a variable shouldn't have quotes around it or you will litterly take over the quote (for example: you would see $times instead of the value given to $times).

Cheers!

Answer 1

Let's look at a simplified example:

$foo = "world";
echo "Hello $foo";

$foo is a variable holding a string, and the echo statement "interpolates" into another string. The string from $foo will be placed directly into the string, and you won't be able to see the "join". The output will be Hello world.

Now let's add some quotes inside the string:

echo "Hello '$foo'";

The $foo is still interpolated and loses its identity, but the ' characters are also part of the final string. The output will be Hello 'world'.

In your SQL, this is what you are doing - you are combining several strings into one, and the result happens to be an SQL statement. Let's say the SQL you want to end up with looks like this:

SELECT * FROM things WHERE thing_name = 'world'

Those quotes are how you tell the SQL parser in the database that 'world' is a string and not, say, the name of a column.

Using our definition of $foo from earlier, we can construct this like so:

$sql = "SELECT * FROM things WHERE thing_name = '$foo'";

We still need the single-quotes, because they're part of the SQL we're trying to create.

However, as others have pointed out, this is also where the risk of "SQL injection" comes from. Imagine an attacker is able to trick us into setting $foo to a value of their choice:

$foo = "world'; DROP TABLE things; --";

Now when we build our SQL string we end up with this:

SELECT * FROM things WHERE thing_name = 'world'; DROP TABLE things; --'

Oops!

The safest protection against this actually does involve passing the variable as a variable, and not merging it into the string. In essence, you pass the database two things: a "parametrised statement", and the "parameters" to use with it. The statement might look like this:

 SELECT * FROM things WHERE thing_name = :foo

Note that unlike in our naive interpolation, we don't put quotes around the placeholder :foo. That's because when used correctly, no text will ever be substituted here. Instead, the database will "prepare" the statement as a query like "select all the columns from the table things based on a value to be matched against thing_name", and the "execute" it by saying "match this variable against thing_name".

Now when we pass our attackers string as the parameter for :foo, we just get a query looking for things with that name; since there presumably won't be any, all we'll get is an empty result.