Understanding and Preventing SQL Injection Attacks

A Work in Progress

Background

Example 1

The underlying SQL statement is:

If the user enters the following:

USERNAME: JoeBloggs

PASSWORD: Sunshine

When submitted, the SQL variable will contain:

If this SQL command is executed against a database then either it will return a record (username and password are correct) or it will return no records (either username or password or both are wrong).

Attack Start: Enter a single quote (') into the input box and submit:

USERNAME: '       

PASSWORD:         

The following response is returned from the server:

Now that the attacker knows the field names, he/she is able to move to the next level. The following "Username" and "Password" are entered:

USERNAME: ' or username like '%

PASSWORD: ' or password like '%

When submitted, the SQL variable will contain:

If this SQL command is executed against a database then it will return ALL records. If the application uses this response to determine a correct username/password sequence then it will continue with the log-in process.

The user will be considered to be the first username in the table (most likely the Administrator!) and will have all the rights associated with that login. Note: Variations of the username can change the actual user impersonated e.g.

' or username like 'A% will return all records with usernames starting with A.

Example 2 (Variation on Above)

The underlying SQL statement is:

If the user enters the following:

STAFFNUMBER: 851792   

PASSWORD: Sunshine   

When submitted, the SQL variable will contain:

If this SQL command is executed against a database then either it will return a record (username and password are correct) or it will return no records (either username or password or both are wrong).

Attack Start: Enter a single quote (') into the input box and submit:

STAFFNUMBER: '        

PASSWORD:         

The following response is returned from the server:

Now that the attacker knows the field names, he/she is able to move to the next level. It can also be seen, by the number of ', that the Staffnumber is a numeric field. The following "Staffnumber" and "Password" are entered:

STAFFNUMBER: 0 or 1=1

PASSWORD: ' or password like '%

When submitted, the SQL variable will contain:

If this SQL command is executed against a database then it will return ALL records. If the application uses this response to determine a correct staffnumber/password sequence then it will continue with the log-in process.

The user will be considered to be the first staffnumber in the table (most likely the Administrator!) and will have all the rights associated with that login.

Example 3 (from SQlSecurity.Com)

If the application is running with enough rights to add new users, the following command could be used to add a new user

USERNAME: ' exec master..xp_cmdshell 'net user newusername newuserpassword /ADD'--

The -- at the end of the command is used to comment out any code that follows

Prevention

• Do not allow a single ' to be accepted as input - always replace each submitted single ' with '' (2 x ' and not ").
• Use the submitted username to find the record and THEN compare the submitted password with the stored password.
• If the field is numeric then check that each submitted value IS numeric.
• Implement Error Handling so that the system, on detecting an error (such as wrong input) does not provide even more information
• Make sure the application is running with only enough rights to do its job

Links

• SQL Injection FAQ at SQLSecurity.Com