Securing Your Inline SQL Statements From SQL Injection

At the beginning of any coder's career, they build queries in code using strings. When you build strings in your code instead of on the database, it's referred to as "inline SQL." Whether you use PHP, C#, or any other language, you've probably used string objects to build a query based on user input. The problem with taking this approach is that you open your application up to SQL injection (SQLi). It's common with new developers, but even the best ones sometimes introduce vulnerabilities from poor code design.

Understanding Basic SQLi

Before you understand where inline SQL introduces vulnerabilities, you should first understand how an attacker formulates strategies to find them. Many SQLi attacks aren't even your stereotypical attacker banging away at your website looking for vulnerabilities. Instead, an attacker can download numerous scripts available on the web and run them on his favorite websites. This is one reason SQLi is such a popular method for hacking websites.

Take a look at a simple string written in PHP.

$query = 'select username, password from User where user_id='. $_POST['user_id']; 

In the above query, the string $query is built using textbox input from the user labeled user_id. It retrieves the username and password for the user, but it leaves the door wide open to attackers. Using this query with SQLi, an attacker could potentially gain access to the administrator's credentials. Let's see how it can be done.

Instead of typing a username in the user_id textbox, an attacker can enter something similar to the following:

-1 or 1=1; --

Put the string together using dynamically built PHP strings, and the following query is what you send to the database:

select username, password from User where userid= -1 or 1=1; --

The result is that the entire contents of the User database are given to the attacker. The '--' comments out any other commands that you might have in the statement. It's a protective measure the attacker uses to avoid any syntax errors.

This is a simple attack, but numerous other scripted SQL statements are available to test your input. You must be able to secure your website from these attacks using logic (understanding how a SQL statement could be built) and some built-in tools available with most common web languages.

Protecting Code From SQLi

The methods that you use to defend against SQLi depend on the language and platform you're programming on. One common way to defend against SQLi is to move inline SQL string statements from code to database stored procedures. Stored procedures will translate the SQLi statements as literals, which means it won't transfer the SQL statements as executable code but rather as literal string values the database will use as input parameters. The result is that the statements will return no values.

If you're using Microsoft .NET, you have easy access to ORM (object-relational mapping) frameworks such as Entity Framework or NHibernate. You can then use LINQ statements to query databases, and these will render the injected SQL statements unusable.

In the previous section's examples, PHP was used to build strings. Inline SQL in PHP is common due to developer limitations when working with WordPress. WordPress is often targeted because developers can't get access to a site's database to use stored procedures when they upload to the WordPress repository for public download. WordPress developers use inline SQL often, and that's why even some common, popular plugins have been compromised.

Since PHP was used as the example in the previous section, let's use a PHP example to protect inline SQL code from injection. PHP developers suggest using prepared statements. Prepared statements parameterize your queries, which makes them similar to stored procedures. Instead of building strings, prepared statements use symbols to indicate where you want to insert values, and these values are taken as literals.

As of PHP 5.x, you can use the PDO class to create prepared statements. We'll use PDO to build a query that's safe from SQLi. First, we need to create the database connection and instantiate the PDO object.

$conn = mysqli_connect($servername, $username, $password);

$pdo = new PDO('mysql:;dbname='.$database_name, $username, $password);

Next, let's set up a prepared statement with the SELECT query used in the previous section.

$sql = $pdo->prepare("select username, password from User where userid = :id;");

Notice that the prepared statement uses :id to indicate that this is where the variable is located. With the PDO object, you then assign a value to the variable using input from the user.

$sql->bindParam(':id', trim($_POST['user_id']));

We've used the "trim" function to remove any leading or trailing spaces from input.

Even though the prepared statement is set up, it still hasn't executed. The following statement executes it on the database using the connection we first set up.

$result = $sql->execute();

If you want to print results to the screen, you can fetch all returned rows from the prepared statement and use a loop. The following code is an example.

while ($row = $result->fetch_assoc())
        {
            echo $row['username'];
        }

This is just one example of protecting your code. If you must use inline SQL in WordPress or any other PHP script, use prepared statements instead of dynamically building a string. You protect your applications and user data and stop many of the scripts used to hack website data.