Pseudoremedy 1: Quoting the Input

Pseudoremedy #1: Quoting the Input

Quoting the input is a method often proposed to solve the problem of database input issues, but it is definitely not a remedy. Let's see how it's used and why it's bad. Look at this code fragment:

int age = ...; // age from user string name = ...; // name from user name = name.Replace("'","''"); SqlConnection sql= new SqlConnection(...); sql.Open(); sqlstring=@"SELECT *" +             " FROM client WHERE name= '" + name + "' or age=" + age; SqlCommand cmd = new SqlCommand(sqlstring,sql);

As you can see, the code replaces single quotes with two single quotes in the user's input. So, if the attacker tries a name such as Michael' or 1=1 --, the single quote (used by the attacker to close off the name) is escaped, rendering the attack useless because it leads to an invalid SQL statement before the comment operator:

select * FROM client WHERE ID = 'Michael'' or 1=1 -- ' or age=35 

However, this does not deter our wily attacker; instead, he uses the age field, which is not quoted, to attack the server. For example, age could be 35; shutdown --. There are no quotes, and the server is shut down. Note that using ; is optional. 35 shutdown would work just as well, so don't think parsing out ; leads to safe SQL statements!

And just when you really thought you could use quotes, the attacker can use the char(0x27) function to hide the single quote in some circumstances. A variation is to use constructs such as this:

declare @a char(20) select @a=0x73687574646f776e exec(@a)

This construct, when added to another SQL query, calls the shutdown command. The hexadecimal sequence is the ASCII hex equivalent of the word shutdown.

Where am I going with this? Simply escaping a series of SQL commands might help, but it probably will not!

CAUTION
Escaping characters might not make you immune to SQL injection attacks.