12 Comments

• 
  
  Not sure I understand why you are saying that replacing ' with " is unsafe. It doesn't show that in your example.
  

• He's saying if that's the only thing you are checking you're code is not secure. He's right. You can also do worse than shut down the system. How about install a backdoor application via xp_cmdshell?
  

• That's why you should always use command parameters.
  
  Also, validating numeric query params will do the trick.
  
  

• Use stored procedures. That will protect from SQL injection (most of the time).
  

• I also don't understand why this example provided contradicts replace-quotes-with-double-quotes-is-safe claim.
  
  
  
  Don't get me wrong, I am for command parameters, and people should use it for the sake of performance as well, but replacing single quotes with double-quotes would prevent people from injecting code (even the shutdown code).
  

• Aviv, Girish, Minh: use every trick in the book you can. Layers of protection. validation, sprocs, minimally priveleged accounts, etc, etc, etc.
  

• Just to explain how double-quoting would not protect against this attack: it's because you're not passing a string, the value you put into the SQL statement string will not be enclosed in quotes as a string value would. Therefore "12345 ; shutdown" input will execute the shutdown command, especially if you get even smarter and append -- at the end of your input to comment out anything that might follow (such as WHERE or GROUP BY clauses).
  

• In ADO.NET you can construct a parameterized command and execute your queries using that. It's a portable solution, well it's as portable and the SQL that you use for your command object.
  

• Only the uninformed would code like your example, but for character data like :
  select * from blabla where id=''' || xxx || ''''
  where doubling of single quotes is practiced, this is perfectly safe and you're uninformed if you think otherwise. Don't tell me I'm wrong, prove it.
  

• Hello,
  i'm asking the same question... in case of th epost of joe coder , the protection tecnic work...
  ? is it correct?
  

• But what if the param you need to send actually is a string? Are you supposed to filter for semicolons now or something? It seems the issue you are referring to can be avoided simply by restricting the permissions of the user you use to access your DB from the web. And for others, stored procedures are only really secure if you make sure the data you are passing to it is clean. That means you still need to make sure numbers you send are actually numbers and so on. I'm still all for the replacing quotes with double quotes when you actually are passing string data. Just be careful, especially about error messages and such.
  

• Using parameterized stored procedures work great, except with db's like MS SQL which allow "stacked" commands. Consider this stored procedure call string, that calls a nice tidy parameterized stored procedure called sp_Login:
  
  "sp_Login '" & username & "', '" & password & "'"
  
  Then consider the hacker who enters the following as the username in the login form...
  
  '; some sql command --
  
  The stored procedure call suddenly becomes...
  
  sp_Login ''; some sql command --, ''
  
  ... and since MS SQL can run multiple commands separated by a semi-colon, the hacker is successful in running whatever he pleases on your server, since while the stored procedure fails, his injected command won't. In other words, he doesn't care about the stored procedure at all, if it fails his code still runs (and everything after the -- is ignored). :(
  
  The fix therefore is to do a replace procedure on the user input from querystrings or forms, e.g. replace ' with '' (single apostrophe with double apostrophe). By doing so, the hack attempt creates this sql call string...
  
  sp_Login '''; some sql command --, ''
  
  Which causes an error with the three apostrophes in a row. Legitimate data is not affected, since a double apostrophe is an "escape" for a single apostrophe in SQL.