Design Patterns    |     Security    |     Testing    |     Distributed Computing    |     Contact
SQL Injection

Consider a web application that requires that the user enter a userID through an HTML form. The user enters the ID and submits the form. On the server side the application builds a SQL command by simply appending the input entered by user. On the server side code may look as follows:

String userID = request.getParameter("user");
String sqlStmt = "SELECT userID, firstname, lastname FROM users 
				WHERE userID = '" + userID + "'"; 
Statement stmt = con.createStatement();

What happens if the user instead of entering an ID enters some valid SQL operators and commands? These commands will be appended to the sqlStmt and sent to the database server for execution

Attack Types

  • Unauthorized data access: make the application reveal more information than the user is authorized to see
  • Authentication bypass: make the application authenticate a user which does not have any credentials
  • Database modification: modify database structure and/or content

Attack: Unauthorized Data Access

"SELECT userID, firstname, lastname, preferences
	from users where users
	where userID = '" + userId + "';"

If instead of a valid user Id the hacker enters:
	anything' OR 'x'='x

Then the resulting SQL string on the server side becomes:

"SELECT userID, firstname, lastname, preferences
	from users
	where userID = 'anything' OR 'x'='x';"

Because X=X is always true, the condition matches all records in the table users and all records may (potentially) be displayed to the attacker

Attack: Authentication Bypass

Authenticates a user that does not have proper credentials
	"SELECT login FROM admin_users 
		WHERE login = '" + formLogin + "' 
		AND password='" + passwordLogin + "';"

Some applications will just check that the above SQL has returned at least a record (non-empty result set)

If the hacker enters anything' OR 'x'='x as in the previous example, the SQL will return records which the application will treat as successful authentication

Attack: Database Modification (delete a table)

In this kind of attack the hacker inserts data modification commands like INSERT or DROP TABLE

Suppose the hacker types:
		x'; DROP TABLE admin_users;

As a result, on the server side the command becomes:

	"SELECT userID, firstname, lastname, preferences
		from users 
		where userID = 'x'; DROP TABLE admin_users; --'"

Note that ";" is a SQL command terminator, which means that the above string represent two distinct SQL commands. After these commands are executed, if successful, the table admin_users will be deleted from database

Attack: Database Modification (insert records)

This attack tries to insert records (users) in database that will allow future access

The hacker enters:
	x'; INSERT INTO members('userId','firstname','lastname','preferences') 
		VALUES ('2345','John','Smith','some preferences');--

On the server side the SQL becomes:

"SELECT userID, firstname, lastname, preferences
	from users 
	where userID = 'x'; INSERT INTO 
	VALUES ('2345','John','Smith','some preferences');--'"