SQL Injection and String Parameters
How to perform SQL injection in text fields
The only difference between numeric parameters and string parameters is that the latter is enclosed between quotes. From an attacker perspective it simply means that the injected SQL segment must be crafted in a way to handle those quotes and generate a valid query.
Considered Data Types
This technique can be applied to all data types that must be surrounded by quotes to be represented. Which means it can be used against all data types except numeric parameters. In some cases, even numeric values can sometimes be represented between quotes (see explanation in numeric parameter attacks article).
String Parameter Injection Example
Let’s suppose the page we are testing has GET parameter named username. When loaded, it displays the full name and email of the specified member. Here is what the URL looks like when a regular request is made.
Real username parameter in URL.
http://www.victim.com/viewMember.php?username=admin
And now an overview of what happens in the page’s script.
Building the query without sanitizing input.
$sql = "SELECT id, username, first_name, last_name, email FROM members WHERE username='".$_GET['username']."'";
Query qenerated (this query is executed).
SELECT id, username, first_name, last_name, email FROM members WHERE username='admin'
The user input is integrated as is. Therefore, the attacker can insert SQL segments and manipulate the WHERE clause. However, before gaining control over the query, he must simulate the end of the parameter. He must also handle the original closing quote to make a valid SQL query. Let's see how it is done.
Closing Quote
Since no input sanitizing is made, the first quote appearing in the input will be considered as the closing quote.
Parameter submitted by the attacker (adding a closing quote).
admin'
Query generated (invalid SQL syntax).
SELECT id, username, first_name, last_name, email FROM members WHERE username='admin''
Trailing Quote
The last query is invalid because of the remaining single quote. When the tester adds an always true condition with a missing quote, the sql injection is successful.
Parameter submitted by the attacker (notice the missing last quote).
admin' OR 'a'='a
Query generated (valid query).
SELECT id, username, first_name, last_name, email FROM members WHERE username='admin' OR 'a'='a'
By making the WHERE clause always true, the attacker will view information about all users at once. This is not a security problem since information could have been retrieved manually. However here is what he could have done.
Malicious parameter.
invalid-username' UNION SELECT 1, username, passwords FROM members WHERE 'x'='x
Query generated.
SELECT id, username, first_name, last_name, email FROM members WHERE username='invalid-username' UNION SELECT 1, username, passwords FROM members WHERE 'x'='x'
As you can guess, this would list all username and passwords in the database. This technique is detailed in the SQL injection UNION attacks article.
