Anyhoot, I've played around in a lot of languages, but not PHP. There was some minor back and forth about this as another buddy shared his thoughts. I stated that I've never seen any language or database that takes a query and automatically escapes it for you once you call it. The standard defense against SQL injection that I've always used, and most often seen implemented in production has been SQL parametrization.
My friend stated that in PHP you can call built in escape string function on the SQL string and it will take care of it for you. (Note: You can't escape the whole query string, you have to escape each individual input field and then use those escaped string in the query string).
I stated that generally as a developer you want to prevent SQL injection by using parametrization or persistence frameworks if possible. This set off some discussions, and then the Bears game got started so the discussion kind of died as my heart rate ramped up for the (disappointing) game. However, during lunch today I decided to write up on the topic.
You can checkout the SQL Injection wikipedia entry here, parametrization is the first method recommended. Escaping strings is also mentioned as a solution for PHP, although the ability to forget to escape a field makes this method error prone and cumbersome. Another SQL Injection site here recommends using escape string methods only when necessary as it creates frail code.
In Java using straight JDBC drivers you usually use parametrization of the query AKA prepared statements. (Java noobs click here). Some persistence frameworks like Hibernate will take care of parametization for you by default, but are still vulnerable to additional injection attacks you need to guard against. If your a glutten for pain your probably an Adobe Flex developer, and you also parametrize the SQL query. Shiver. I'm not a fan of developing in Adobe Flex. I spent about 6 months working with it and decided it wasn't for me!
In Force.com, we use parametization by using the bind ( :varName) ability of SOQL in Apex. Per the Force.com Documenation, "To prevent a SOQL injection attack, avoid using dynamic SOQL queries. Instead, use static queries and binding variables. "
For example:
List<String> idList = new List<String>{'XXXXXXXXX','YYYYYYYYY','ZZZZZZZZZZZ'};
List<Account> accounts = [Select Id, Name from Account where Id in :idList];
You can escape the user input, per Force.com documentation "If you must use dynamic SOQL, use the
escapeSingleQuotes method to sanitize user-supplied input.". But they strongly recommend parametrization of queries. Furthermore, Salesforce and Apex developers should be using the Force.com Security Scanner to check their code base for security holes like SOQL injection. Using the scanner is very easy, all you need to do is provide your login address to the security scanner at http://security.force.com/sourcescanner. It will automatically generate a pretty sweet code analysis report in PDF format and email it to your email address associated with the login address. It also gives you the solution on how to re-factor your code to solve the hole, which saves you time researching the solution. Pretty slick!
