Jump to content

[Competition Questions] SQL Quoting

Guest Anonymous

Recommended Posts

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` (


`cQuestion` VARCHAR( 255 ) NOT NULL ,

`cAnswer` TEXT NULL ,



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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 9 months later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...