Tuesday, November 08, 2005

Preventing SQL injection attacks...

I still see a lot of applications that are vulnerable to SQL injection attacks bcoz of usage of dynamic SQL using string concatenation. It is a common myth that to circumvent SQL injection attacks we 'have' to use stored procedures.
Even if we are using dynamic SQL, it is pretty simple to avoid these attacks.

In .NET the following techniques can be used:
Step 1. Constrain input - Validate input using client side validation and server side validation (e.g. using regular expressions)
Step 2. Use parameters with stored procedures. There is one caveat with Stored procedures. If Ur stored procedure uses the 'EXEC' command which takes a string, then the same vulnerability exists there too.
Step 3. Use parameters with dynamic SQL. (Yepppiee...in .NET it's so simple to have named parameters even for dynamic SQL)

Code snippet :
-----------------------------------
SqlDataAdapter myDataAdapter = new SqlDataAdapter( "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", connection);
myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11); myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;
-----------------------------------

Other important points to be considred are using a least-privileged database account and avoiding disclosing error information to the user.
A good article discussing this is at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000002.asp

In Java, one can use 'Prepared Statements' and 'Stored Procedures' to prevent SQL injection attacks.