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!

  • 1
    The single quotes in the above example are used in the sql part and have nothing to do with the php variables – Dimitris Filippou Jun 04 '18 at 13:41
  • 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 – clearshot66 Jun 04 '18 at 13:41
  • It has nothing to do with variables, it is because of SQL syntax. Some SQL values literals require quotes (and other quoting) - You should use proper quoting function instead of simple quotes around. – Roman Hocke Jun 04 '18 at 13:42
  • 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. – CD001 Jun 04 '18 at 13:42
  • `Why are there quotes around the variable names?` ... if you were using prepared statements (which you should be), there wouldn't be any such quotes. – Tim Biegeleisen Jun 04 '18 at 13:45
  • 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. – ADyson Jun 04 '18 at 13:46
  • Use bound parameters instead. `WHERE something = :placeholder`, and then `bindValue('placeholder', $val)`, that will protect you – delboy1978uk Jun 04 '18 at 13:48
  • 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. http://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. – ADyson Jun 04 '18 at 13:48
  • I appreciate the fact that you guys want to teach me how NOT to insert it ( didnt know this wasnt even safe lol), but @CD001 said it is because they become strings, but isn't a PHP $name = "hi"; a string either? so why will it output the variable litterly then? if someone btw has a good answer, please use it as a answer so I can upvote it afterwards. thanks –  Jun 04 '18 at 13:51
  • RE: parametrised queries with prepared statements - technically all you're doing there is delegating responsibility to the PHP engine to put the correct quotes and sanitisation in place for the database; it doesn't actually make any odds as far as the database server is concerned. **HOWEVER** interpolating the variables into the string means it's **far** more likely that you could be tricked into asking the database server to do something completely unintended (SQL injection). – CD001 Jun 04 '18 at 13:53
  • So I could also use it without quotation around the variable names? if so, will it still carry a string if ever inserted? –  Jun 04 '18 at 13:55
  • 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*. – IMSoP Jun 04 '18 at 13:56
  • @IMSoP - good point; I was thinking of emulated prepares with older MySQL drivers. – CD001 Jun 04 '18 at 14:04
  • " but isn't a PHP $name = "hi"; a string either?"...yes, it's a PHP string. And the way you've written your code means that the value the string $name will become a hard-coded part of the $sql string. To give a simple example, look at this code: `$name = "UserWithNoName"; $str2 = "Hello $name"; echo $str2;` - the output will be "Hello UserWithNoName". It combined two strings together using PHP's interpolation functionality. See http://php.net/manual/en/language.types.string.php#language.types.string.parsing . – ADyson Jun 04 '18 at 14:21
  • So...your line `$sql = ` etc. is _constructing_ a string from a combination of some hard-coded text and some variables. That constructed string is the thing which is then sent to MySQL to be executed as a SQL query. When SQL receives this query, it then looks within that, to see which bits of the query _it_ thinks of as being strings within that. Basically you're using strings from one language (PHP) to create a string of text in another language (SQL). Within that SQL text you can then make text which the SQL engine will interpret as a string according to its (not PHP's) rules.Does that help? – ADyson Jun 04 '18 at 14:22
  • @ADyson Please avoid posting answers as comments. Comments should be for *clarifying* and *improving* the question (or answer) they are attached to; they are designed to be short and ephemeral (may be deleted at any time). If you feel that your description would be helpful to the OP or other readers, and is not covered in the existing answers, feel free to post a new answer, which can then be voted on and improved, have much better formatting, and be more visible to future readers. – IMSoP Jun 06 '18 at 12:23
  • @IMSoP I didn't feel that my comments really passed muster as a full answer. Admittedly they're fairly detailed, but not quite complete IMO. And the answers posted, including yours, cover everything else I'd have mentioned, and the OP seems happy with them, so I don't see the need to post the same thing again. – ADyson Jun 06 '18 at 12:32
  • 1
    @ADyson Yeah, I've done the same myself before, and been encouraged to rethink. If there was no answer at the time, any answer would have been better than none (and you could always expand or remove it); if there *was* an answer at the time, maybe it could have been a suggested addition to that answer; if it doesn't add *anything*, why post it at all; and if it doesn't fit inside any existing answer, then by elimination it must be a new answer. Not a big deal, but as it is, it's just kind of stranded, below the "more comments" link where nobody will see it. – IMSoP Jun 06 '18 at 13:10
  • You guys are going at it full force, just to remind you both haha: I appreciate the time and effort you guys put into it, whether it's a comment or answer. so @ADyson I appreciate it very much. Thank you. –  Jun 06 '18 at 13:12

2 Answers2

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)

Remco K.
  • 644
  • 4
  • 19
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.

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • Not sure if this is related then, but doesn't mysqli_real_escape_string prevent the injection? Such as: it disables stuff like < " ' % etc. –  Jun 05 '18 at 07:28
  • 1
    @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. – IMSoP Jun 05 '18 at 08:42