[thelist] Re: Avoiding SQL Injection

[Brooking, John]

Thanks for all the comments so far. Let's take an example, to make sure we're all on the same page. Say I'm inserting my comment into a VARCHAR column:

   $sqlText = "INSERT INTO comments ( comment ) VALUES ( '" 
            . $_POST["comment"] . "' )";
            
Now if someone provides a comment of:
   
      foo' ); DELETE * FROM comments; INSERT INTO 
      comments ( comment ) VALUES ('foo
      
Then my $sqlText will look like:

   INSERT INTO comments ( comment ) VALUES ( 'foo' );
   DELETE * FROM comments; INSERT INTO comments
   ( comment) VALUES ('foo' )
   
So, Joshua, you're saying that in this case, simply escaping my single quotes, which I'd need to do anyway to correctly store a sentence like this one, which has two of them in it, is enough? I do believe you may be right, because the fact that I'm storing it in a string column means that a SQL attack string is going to have a close the quote that I opened in my code in order to get the rest of his nefarious scheme to parse correctly. So by definition, it has at least one single quote, so escaping all single quotes will prevent any other potential special characters, such as semi-colons, from being evaluated as anything other than characters inside a string. I hadn't thought that through before!

And I'm definitely with you on testing non-string values!

But I also see the added value in Ken's comments about prepared statements, and Matt's about restricting the database user. (It's hosted by an ISP, so I will have to talk to them about that.) To play a little devil's advocate, gents: If we're only talking about string values, such as the example above, do you know of any circumstances in which simply escaping the single quotes would not suffice (even without the other protections)? (I'm not saying this is all I'll do!)


This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.