[Competition Questions] SQL Quoting

[Guest Anonymous]

We are (hopefully) used to protecting against SQL injection, and we know that string fields (CHAR, VARCHAR, TEXT etc) need to be surrounded in single quotation marks, however there is one particular instance where enclosing the contents of a string in quotation marks is a *bad* idea.

So, given the statement:

UPDATE `competition` SET `cQuestion` = 'How do I...?', `cAnswer` = '';

write an equivalent statement that demonstrates that using quotation marks around the data for cAnswer is a bad idea.

You are not permitted to refer to any other field, or function.

Since the IRC boys are having problems with this, I'll post the PHP equivalent...

This is the real query I can run safely. $data is a simple string containing NO special characters (quotation marks, spaces etc)

mysql_query("UPDATE `competition` SET `cQuestion` = 'How do I...?', `cAnswer` = $data");

 

Here is a small test table for you to play with...

CREATE TABLE `competition` (

`cID` INT UNSIGNED NOT NULL AUTO_INCREMENT ,

`cQuestion` VARCHAR( 255 ) NOT NULL ,

`cAnswer` TEXT NULL ,

PRIMARY KEY ( `cID` )

) ENGINE = MYISAM;

INSERT INTO `competition` (`cID`, `cQuestion`, `cAnswer`) VALUES (1, "How do I...", "No idea");

[Guest Anonymous]

Re: [Competition Questions] SQL Quoting

Hmm, obviously foxed a few of you here...

Look at the following two statements:

$data = "NULL";

$sql_1 = sprintf("UPDATE `competition` SET `cQuestion` = 'How do I...?', `cAnswer` = %s;", $data);

$sql_2 = sprintf("UPDATE `competition` SET `cQuestion` = 'How do I...?', `cAnswer` = '%s';", $data);

There are times when I explicitly want to reset a field to NULL, so blindly quoting is NOT always a good idea.

[Floydian]

Re: [Competition Questions] SQL Quoting

Nice!

[POG1]

Re: [Competition Questions] SQL Quoting

Good point, if you wanted to also add other things such as unix_timestamp() it would treat it as a string..

If thats what your trying to say