Despite growing awareness, SQL injection still remains a problem. Consequences of a successful SQL injection vary depending on the vulnerability. Some of the threats that can be introduced are these:
- Data disclosure
- Modification of existing data
- Insertion of new data
- Arbitrary file system access
- Arbitrary network access
- System compromise
Every query in CMA is vulnerable to SQL injection, so you'll work with several input vectors. By injecting a condition and commenting out the rest of the query through the username field, authentication can be bypassed. Listing 16 shows the query as it was intended.
Listing 16. The select statement when John and Password1 are submitted as user credentials
SELECT COUNT(*) FROM UserAccount WHERE Username = 'John' AND Password = md5('Password1');
Listing 17 shows what the statement looks like when a malicious string is used to inject code into the first condition.
Listing 17. The
'or 1=1;#and an empty password are submitted as credentials
SELECT COUNT(*) FROM UserAccount WHERE Username = ''or 1=1;#' AND Password = md5('');
Because 1 is always equal to 1 and the password checking condition is commented out by the number sign character (#), the query in Listing 17 returns the count of all the records in the UserAccount table. If the count is not zero, the return value of the Authenticate function evaluates to true, granting the attacker access.
The user search functionality is vulnerable in a way that can be exploited to extract arbitrary data, among other things. Listing 18 shows the search query as it was intended to work.
Listing 18. The user search query under normal conditions
SELECT FirstName, LastName FROM UserAccount WHERE FirstName LIKE '%John%' OR LastName LIKE '%John%';
By utilizing the UNION operator, attackers can append an entirely new query to retrieve data of their choosing:
'and 1=0 UNION SELECT Username, Password FROM UserAccount;#
Listing 19 shows the dynamically generated query after submission of the attack string.
Listing 19. The user search query after SQL injection
SELECT FirstName, LastName FROM UserAccount WHERE FirstName LIKE '%'and 1=0 UNION SELECT Username, Password FROM UserAccount;#%' OR LastName LIKE '%'and 1=0 UNION SELECT Username, Password FROM UserAccount;#'";
The attack yields the user name and password digest for every user in the database (see Figure 5).
Figure 5. The result of successful injection using the UNION operator
To prevent SQL injection, you must properly escape and validate all user-submitted
input. Most web development APIs come with functions to achieve this. With PHP and
MySQL, use parameterized queries along with
mysql_real_escape_string for string values to protect from many attacks (see Listing 20).
Listing 20. Updated authentication code utilizing preventive measures offered by the PHP API
$query = sprintf("SELECT COUNT(*) FROM useraccount " . "WHERE Username = '%s' AND " . "Password = md5('%s');", mysql_real_escape_string($Username), mysql_real_escape_string($Password));
As Listing 21 shows, the authentication bypass no longer works due to the escaping of the delimiter at the start of the attack string.
Listing 21. An injection attempt with the new fix in place
SELECT COUNT(*) FROM useraccount WHERE Username = '\'or 1=1;#' AND Password = md5('');
It is important to use the right type specifier in the format string. Casting to the expected type provides an additional layer of protection (see Listing 22).
Listing 22. Safely creating a query using an untrusted integer
$query = sprintf("SELECT * FROM useraccount WHERE Id = %d", (int)$_GET['id']);
The subsequent section goes into File Inclusion, a type of bug that is common in PHP web applications.