What are some common SQL injection checks I can use?

Question

I'm running through my web app and I'm trying to test various parts of the system to make sure they aren't succeptible to SQL injection.

What are some common sql injection checks I can perform on textboxes/textareas, etc that would be good checks for vulnerability?

I'm not worried about damaging my data as I'm running this on a test bench so it's ok if it kills my server or database.

I'm specifically looking for examples I can use. I'm not really looking for overall techniques, although I hope to cover a variety of different techniques with the examples that I hope to get.

Answer 1

You might find this site helpful

Answer 2

Just make sure you are using SqlParameter objects everywhere (assuming .net), and not concatinating sql queries, and you will be completely safe from SQL Injection.

-- Edit

Some people are saying that using 'Stored Procedures' makes a difference. It doesn't. If you still build the query to execute the stored procedure dynamically, you are at risk:

string sql = "exec spFoo @Hello = '" + helloValue + "'";

The only way to execute a query, with parameters, in a modern language (and specifically .net) is by using SqlParameter class, and specifying the appropriate data types.

Answer 3

If you always use parameterized queries or stored procedures, then you don't have to do any checks.

Never use dynamic SQL, where you build the SQL command based in part on input from the user. That's what requires you to sanitize the input.

Answer 4

1) if you only use stored procedures (and call them in a paramatized manner) that contain no dynamic SQL you are good to go.
2) if you only use prepared statements in your application language you are good to go.
3) if none of the above, you must do alliciation langauge specific commands to scan your text, but you don't say which application language.

EDIT
morale of the story. never build and execute a SQL sommand in a string if it contains any text given to you by the user. pass the user given text to the sql command as a parameter. SQL injection is the user changing your SQL command by the text they give you.

example:

Query="select * from users where userName='"+givenUserName+"'"

you think Query will be:

 select * from users where userName='your_friend'

but it if givenUserName is

'x';delete from users --

Query will end up being:

select * from users where userName='x';delete from users --'

Answer 5

SQL injection checks? As in, strings you should be scared of?

Much better in the long run to

1. use .NET's SqlParameters (which check themselves for injection shenanigans more completely than you could hope to in ad hoc checks)
2. avoid dynamic SQL in your SPs (because even parameterized queries can be jacked by unexpected input if you're concatenating strings and then executing them on the back end)

Answer 6

Anything with a ' in it.

Answer 7

You can use parameters in classic ASP as well, using the ASP ADO objects.

Here's a small example:

Dim conn, cmd, SQL, connstr
connstr = "My Connection String"
SQL = "SELECT Username FROM Users WHERE UserID = @UserID"

' Setup Connection and Command objects
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = connstr
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = SQL

' Create and add the parameter
cmd.Parameters.Append(cmd.CreateParameter("UserID", adParamInput, 50, ID))        

' Execute
cmd.Execute ()

It's been a long time since I wrote any classic ASP, so this code will probably not work as is. However, it gives an idea of how parameters can be used in classic ASP as well to prevent SQL injections.

Answer 8

The use of parameters is handy, but it is not the only way to protect oneself against SQL injection, nor is it foolproof.

If the stored procedure internally dynamically creates and performs an EXEC() on SQL code, you lose all of that protection. You also lose that protection if you have a single query in your application that includes those fields without using parameters or escaping.

Parameters aren't magic, and you can protect yourself by simply using an escape function when building your SQL:

Public Shared Function StringToSql(ByVal s As String) As String
   If s Is Nothing Then Return "NULL"
   Return "N'" & Replace(s, "'", "''") & "'"
End Function

Usage:

Sql = "INSERT INTO mytable(name) VALUES(" & StringToSql(username) & ")"

Boom. Easy as pie. Even takes care of the nvarchar quoting.

You can convert dates, numbers, GUIDs, etc to strings and pass them to the function above, but you're better off creating separate functions for each data type you use.

There is one caveat: you have to use it every single time you build a query-- every CRUD that includes this data, whether produced at the application layer or generated dynamically inside a stored procedure.

But you have to do the same thing to benefit from parameters! So either way, you have to change your habits and review your code. There is no escaping that (lame pun intended).