Security Vulnerability: Using Direct User Input in SQL Statements

Because the input is untrusted and has not been checked for validity, an attacker could change the semantics of the SQL statement. In the following example, the attacker enters a completely invalid name and password, both of which are "b" or "1" = "1," which builds the following valid SQL statement:

SELECT count(*)
FROM client 
WHERE name='b' or '1'='1' and pwd='b' or '1'='1'

This statement will always return a row count value of greater than one, because the "'1' = '1'" fragment is true on both sides of the "and" clause. The attacker is authenticated without knowing a valid username or password—the input entered simply changed the way the SQL query works.

Here is another variation: an attacker who knows a username and wants to spoof that user account can do this using SQL comments—for example, two hyphens (--) in Microsoft SQL Server or the hash sign (#) in mySQL. Some other databases use the semicolon (;) as the comment symbol. Rather than entering "'b' or '1' = '1,'" the attacker enters "Cheryl' --,"which builds up the following legal SQL statement:

SELECT count(*)
FROM client    
WHERE name='Cheryl' --and pwd=''

If a user named Cheryl is defined in the system, the attacker can log in because he has commented out the rest of the SQL statement that evaluates the password, so that the password is not checked.

SQL statements can be joined. For example, the following SQL statement is valid:

SELECT * from client INSERT into client VALUES ('me', 'URHacked')

This single line is actually two SQL statements. The first selects all rows from the client table, and the second inserts a new row into the same table.

Note

• One of the reasons the INSERT statement might work for an attacker is because most people connect to SQL databases by using elevated accounts, such as the sysadmin account (sa) in SQL Server. This is yet another reason to use least-privilege principles when designing Web applications.

An attacker could use this login ASP page and enter a username of "'b' INSERT INTO client VALUES ('me', 'URHacked') --," which would build the following SQL statement:

SELECT count(*)
FROM client    
WHERE name='b' INSERT INTO client VALUES ('me', 'URHacked') --and pwd=''

The password is not checked, because that part of the query is commented out. And the attacker has added a new row containing "me" as a username and "URHacked" as the password—now the attacker can log in using "me" and "URHacked."

Copyright © 2005 Microsoft Corporation.
All rights reserved.