SQL Injection in PHP Web Applications

What is PHP SQL Injection?

When an attacker exploits a PHP application via an SQL Injection, they can gain access to the application’s database and make the application execute unauthorized injected SQL commands to control the behavior of the application. 

In this article you will learn:

• What is PHP SQL Injection?
• How does PHP SQL Injection work?
• Code Examples of SQL Injection in PHP
  • Making Superusers (PostgreSQL)
  • Listing Passwords
  • Attacking the database hosts operating system (MSSQL Server)
• PHP SQL Injection Prevention

How does PHP SQL Injection work?

To exploit an SQL injection vulnerability in a PHP application, an attacker can use several types of SQL injections. For example, they could enter data in the form fields of the application, which is then passed to the database for processing. If the application can’t properly validate the form input data, and accepts the input without sanitizing it, the attacker can inject SQL statements via these form fields and delete, copy, or modify the contents of the database.

In this blog post we will focus on a few select SQL injection types. More are covered in depth in this blog post – [link].

Common variants of SQL injection include:

• SQL injection based on user input
• SQL injection based on cookies
• SQL injection based on HTTP headers
• Second-order SQL injection

A successful SQL injection attack on a PHP application can enable an attacker to:

• Steal credentials
• Access databases
• Alter data
• Access network(s)  

Code Examples of SQL Injection in PHP

Let’s have a look at these examples of SQL Injection:

Making Superusers (PostgreSQL)

<?php
$offset = $argv[0]; // beware, no input validation!
$query  = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
$result = pg_query($conn, $query);
?>

The above example is used to split a result set into pages. When someone clicks on the ‘next’ or ‘prev’ link, the $offset is encoded into the URL. While the script is expecting a decimal number, an attacker exploit this by appending a urlencode()’d form of the following code to the URL:

0;
insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passed)
    select 'crack', usesysid, 't','t','crack'
    from pg_shadow where usename='postgres';
--

When executed, this script would provide the attacker a superuser access. The 0; in the code example above is there to supply a valid offset to the original query in order to terminate it.

Listing Passwords

An easy way to gain passwords is to bypass your search result pages. An attacker only needs to check if there are any submitted variables that are not properly handled, used in SQL statements.

Customisation of WHERE, ORDER BY, LIMIT and OFFSET clauses in SELECT statements can be achieved by setting filters commonly in a prior form.

Databases that support the UNION construct are particularly at risk, where an attacker could attach an entire query to the original in order to list passwords from an arbitrary table. As a result, it is highly recommended that encrypted password fields are used.

<?php
$query  = "SELECT id, name, inserted, size FROM products
           WHERE size = '$size'";
$result = odbc_exec($conn, $query);
?>

The attacker can combine the static part of the query with another SELECT statement that will reveal all passwords:

'
union select '1', concat(uname||'-'||passwd) as name, '1971-01-01', '0' from usertable;
--

An attractive target for an SQL Injection attack can also be the SQL UPDATE’s. As with SELECT, an attacker can chop and append a new query to the UPDATE statement, however the attacker might change the SET clause. To successfully manipulate the query, the attacker would require some schema information, which they could get by examining the form variable names, or simply via brute force.

Attacking the database host operating system (MSSQL Server)

<?php
$query  = "SELECT * FROM products WHERE id LIKE '%$prod%'";
$result = mssql_query($query);
?>

If an attacker submits the value a%’ exec master..xp_cmdshell ‘net user test testpass /ADD’ — to $prod, then the $query will be:

<?php
$query  = "SELECT * FROM products
           WHERE id LIKE '%a%'
           exec master..xp_cmdshell 'net user test testpass /ADD' --%'";
$result = mssql_query($query);
?>

The SQL statements in the batch will be executed by the MSSQL server, which includes a command to add a new user to the local accounts database. Should the application be running as sa and the MSSQLSERVER service is running with adequate privileges, the attacker would gain an account he can use to access this machine.

PHP SQL Injection Prevention

Ensure you never trust any kind of input, especially input coming from the client side. To prevent SQL Injections in your PHP applications:

1. Always use customized users with very limited privileges. 
2. Use prepared statements with bounds variables. Prepared statements are precompiled SQL commands, which can be used with a specific database access library (such as mysqli) or with the more generic library PDO. 
3. Check if the input has the expected data type. PHP has a broadrange of input validation functions
4. If the application waits for numerical input, ensure you verify data with ctype_digit(). Alternatively, you cansilently change its type using settype(), or use its numeric representation by sprintf().
5. If binding variables is not supported by the database layer, each non-numeric, user supplied value can be quoted with the database-specific string escape function (e.g. mysql_real_escape_string(), sqlite_escape_string(), etc.) – this removes all special characters in a string so they lose their meaning when used by the database. The whole string now gets passed as a harmless string to the database, instead of being able to participate in query manipulation
6. Using stored procedures and previously defined cursors to abstract data access ensures users cannot  directly access tables or views

PHP SQL Injection Prevention with Bright

Bright’s cloud-based application security testing scanner Bright tests for SQL Injection, delivering developer friendly remediation guidelines to fix issues.

By shifting DAST scans left and integrating them into the SDLC, developers can detect vulnerabilities early, and remediate them before they hit production. 

Start integrating security testing automation into your development pipelines today with Bright’s FREE scanning account. Sign up here: https://app.brightsecurdev.wpenginepowered.com/signup

Simply follow the 3-step wizard to start scanning your applications and APIs 

If you want to learn more about different types of SQL Injections this is a must read blog post too!