0

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!

Improve this question
19
  • 3
    Because that's how SQL syntax is and they're strings. But they shouldn't be because this is extremely unsecure. Google Mysqli prepared statements and don't do this style ever again Commented Jun 4, 2018 at 13:41
  • 4
    The PHP variables become the values when interpolated into the string like this - what's sent to the database would be VALUES ('Fred Bloggs' ... - the database server knows naff all about the state of the PHP server's memory (the value of $name for instance); it might not even be on the same machine. Commented Jun 4, 2018 at 13:42
  • 2
    " Why are there quotes around the variable names?" because they're strings, and if you supply a string in a literal SQL query it must be in single quotes. If you were supplying a number, you wouldn't put quotes around it because MySQL expects numbers not to have quotes round them. The fact you've got PHP variables within the quotes is not directly relevant, since you placed them within a double-quoted PHP string, what gets passed to MySQL will be a string containing the values of those variables. Commented Jun 4, 2018 at 13:46
  • 2
    But don't write your code like this. It looks like it is vulnerable to SQL injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. Never insert unsanitised data directly into your SQL. Commented Jun 4, 2018 at 13:48
  • 2
    @CD001 No, a correctly parameterised and prepared statement does not interpolate the data into the query. Some drivers will use "emulated prepares" for various reasons, where that happens, but a true parameterised statement send the query (with placeholders) and the data (with type information) as separate fields to the database. The database processes the query without ever combining everything into one string. Commented Jun 4, 2018 at 13:56

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Not sure if this is related then, but doesn't mysqli_real_escape_string prevent the injection? Such as: it disables stuff like < " ' % etc.
@Userwithnoname To a large extent, yes, it is an alternative solution to the problem. It doesn't disable anything, but it escapes things so that for instance ' becomes '' or \' in the SQL string, keeping the attacker from ending the string. There are awkward cases where such escaping is hard, and it's easy to miss one escape call among many, which is why parametrised queries are considered the most reliable approach.
2

To tell the MySQL driver wether the input is a string or a MySQL function/relation.

Example to write a string:

UPDATE TABLE SET name = 'name' WHERE id = 1;

(will update the field to "name")

Example where MySQL thinks it is a relation:

UPDATE TABLE SET name = name WHERE id = 1;

(will not update because it's his own column value)

Example to make MySQL update the field to "select"

UPDATE TABLE SET name = 'select' WHERE id = 1;

(will update the field to "select")

Example where MySQL thinks it is a function

UPDATE TABLE SET name = select WHERE id = 1;

(will return a syntax error because it will call the function select)

Improve this answer

Comments