Protecting Input: Don’t Allow SQL Injection, PHP and SQL Server Style!

My last post talked about using parameterized queries to guard against SQL Injection for a specific environment: MySQL databases within the context of a PHP application. Those applications ran under Apache, which was running under Linux. The MySQL database also ran under Linux. You probably already know that this collection of technology is named Linux Apache MySQL PHP — LAMP for short. This is a common configuration for cloud providers, and it’s very common in corporate environments, too.

But it’s not the only game in town. Microsoft makes a powerful database that many companies have adopted. This post talks about how to replace MySQL with SQL Server — everything else (Linux, Apache, and PHP) will stay the same.

Proprietary Database in the House!

A quick personal side note. I “grew up” programming dBASE III (not plus; just III) applications before SQL Server was a thing. When I finally found Microsoft SQL Server 6.0, I was elated that it solved a lot of problems that dBASE’s *.DBF files (or dBXL’s or FoxPro’s DBF files) caused. I remained a Microsoft-centric developer until the Great Rift (when Microsoft decided, “Hey! All you Visual Basic 6.0 developers? Yeah, no future for you! .Net or bust!”).* After that, I threw myself into the open source software movement, where databases like MySQL, PostgreSQL, and MariaDB rule the roost.

But, I’ve never lost my affection for Microsoft SQL Server, so it was with something approaching nostalgia that I wrote the examples for this post.

But you know what?

I’d forgotten just how many ways Windows Server and SQL Server can bite you. I’d gotten so used to the light-weight and predictable characteristics of the open source world that I’d forgotten how frustrating it can be to work with Windows. No doubt, folks who spend all of their professional lives working with Windows will have a different experience, and that’s fine. But if you’re a developer who, like me, prefers open source, I thought I should share how to make this as painless as possible.

To Microsoft’s credit, they tried to think like an open source developer when they built their PHP drivers for SQL Server. They even put it on GitHub! But still, from time to time, you can see the old Microsoft creep in.

To get started, point your browser to this URL:

• https://github.com/Microsoft/msphpsql

My target environment was Ubuntu Server 16.10 with PHP 7.08 running under Apache 2.4.18. My target database was Microsoft SQL Server 2014 running on Microsoft Windows Server 2012R2. Everything patched to the most current versions.** Given that my PHP environment is running under a 64-bit version of Ubuntu, I selected this branch from Microsoft’s GitHub site:

• Linux-GA-4.0.8

They also have a PHP5 version, as well as another PHP7 version. I wasn’t able to get the latter to work under my installation. This was one of my first reminders that I was using a Microsoft-provided product. But it wasn’t a serious issue; it just cost some time. But I was able to get Linux-GA-4.0.8 running the first time.

The README.md file contained in the ZIP download for Linux-GA-4.0.8 contained clear and easy to use instructions. After installing Ubuntu Server 16.10 and selecting its LAMP installation, I expanded the Microsoft zip file and performed these steps:

sudo su

apt-get -y install php7.0 mcrypt php7.0-mcrypt php-mbstring php-pear php7.0-dev php7.0-xml

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/15.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit

sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install msodbcsql mssql-tools

sudo apt-get install unixodbc-dev-utf16

sudo pecl install sqlsrv

sudo pecl install pdo_sqlsrv

sudo su -

echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/apache2/php.ini

echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/apache2/php.ini

echo "extension=/usr/lib/php/20151012/sqlsrv.so" >> /etc/php/7.0/cli/php.ini

echo "extension=/usr/lib/php/20151012/pdo_sqlsrv.so" >> /etc/php/7.0/cli/php.ini

exit

sudo systemctl restart apache2

A couple of the steps, like number 2, were redundant; Ubuntu’s install included most of what Microsoft’s drivers needed. However, it wasn’t everything, and including components already installed in the “apt-get” statement didn’t hurt anything.

Notice the step to install “unixodbc-dev-utf16.” The Microsoft SQL Server driver for PHP doesn’t talk directly to SQL Server. Instead, it talks to the unixodbc layer, which in turn talks to Microsoft SQL Server. I haven’t performed any load testing with this solution, but I suspect that this driver might perform less well under heavy load than the MySQL Driver. If you have any experience with this setup under load, would you please leave a comment describing your experience?

At this point, I was ready to start coding!

Subtle PDO Code Differences

A design aspiration of PHP Data Objects (PDO) is to make it easy to use techniques like parameterized queries with a variety of databases. They’ve been largely successful; in this case, the only major difference between PDO with MySQL and PDO with SQL Server is the connection string. To illustrate:

For MySQL, consider these PHP variables:

$strDatabaseHost = "localhost";
$strDatabaseName = "asfg";
$strDatabaseID = "asfgreader";
$strDatabasePW = "password";
$strDatabaseType = "mysql";

With these variables, a MySQL PDO connection string can look like this:

$conn = new PDO($strDatabaseType . ":host=" . $strDatabaseHost . ";dbname=" . $strDatabaseName, $strDatabaseID, $strDatabasePW);

To see how SQL Server’s a little different, first consider these PHP variables:

$strSQLServerDatabaseHost = "ohcmhsrv007.interstell.home";
$strSQLServerDatabaseName = "asfg";
$strSQLServerDatabaseID = "asfgreader";
$strSQLServerDatabasePW = "password";

Given these variables, the PDO connection string looks like this:

$strSQLServerURL = "tcp:" . $strSQLServerDatabaseHost . ",1433";

$conn = new PDO( "sqlsrv:server=$strSQLServerURL ; Database = $strSQLServerDatabaseName", $strSQLServerDatabaseID, $strSQLServerDatabasePW);

The database type is obviously different: mysql versus sqlsrv. The next part of the connection string is different, too. Whereas MySQL uses “host=”, SQL Server requires “server=”. Not only that, but SQL Server requires the protocol designator (tcp:) and the port (1433) to be added to the server’s name. For MySQL, those are implied. The last difference is in how the two drivers require the database name be specified. For MySQL, the string is “dbname=”. For SQL Server, it’s “Database=”.

After that difference, the two strings are largely the same.

Common Code for the Sake of Completeness

To finish out our example, we’ll use the same example of a Blu Ray inventory database that the last post used. To search the database, we’ll ask the user to select the genre identifier and title from an HTML form. We’ll capture the variable like this:

$strGenre = $_POST['search_genre'];
$strTitle = $_POST['search_title'];

The search PHP screen will then call the routine that’ll return an array of arrays with search matches:

$arrRows = returnSearchResultsSQLServer($strSQLServerDatabaseHost, $strSQLServerDatabaseName, $strSQLServerDatabaseID, $strSQLServerDatabasePW, $strTitle, $strGenre);

That routine contains the code to build the connection string and make an attempt to connect:

$strSQLServerURL = "tcp:" . $strSQLServerDatabaseHost . ",1433";
$conn = new PDO( "sqlsrv:server=$strSQLServerURL ; Database = $strSQLServerDatabaseName", $strSQLServerDatabaseID, $strSQLServerDatabasePW);
if (!$conn)
    die(error_log("Could not connect: " . sqlsrv_errors()));

The next step is to build the query string. The only thing even slightly unusual in these steps is that we only include the genre identifier in the SQL string if the user included a value for $strGenre:

$strSQL = "SELECT a.*, b.* FROM asfg_pi_input02_works a, asfg_pi_input02_genre b WHERE api02_name_of_work LIKE ? ";
if ($strGenre != "0")
{
    $strSQL .= " AND a.api02_genre = ? ";
}
$strSQL .= " AND b.api02g_identifier = a.api02_genre ORDER BY a.api02_name_of_work ";

Notice the question marks. Just as in the previous article, the question marks represent the parameters. And as before, even if an evil user tries to use something like ‘ OR 1 = 1 ‘ to attack your site, parameters will protect you.

The code next tells SQL Server’s query engine that this query is coming before binding the parameters to the question marks:

$stmt = $conn->prepare($strSQL);
$stmt->bindParam(1, $strNameOfWork);
if ($strGenre != "0")
{
$stmt->bindParam(2, $strGenre);
}

All that’s left now is to execute the query, walk through the results, and build the array of arrays to return to the calling program:

$stmt->execute();
if ($stmt == FALSE)
die(error_log(sqlsrv_errors()));

while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) )
{
$arrRow = array();
$arrRow['api02_identifier'] = $row['api02_identifier'];
$arrRow['api02_name_of_work'] = $row['api02_name_of_work'];
$arrRow['api02_ordering_url'] = $row['api02_ordering_url'];
$arrRow['api02_price'] = $row['api02_price'];
$arrRow['api02_date_ordered'] = $row['api02_date_ordered'];
$arrRow['api02_date_received'] = $row['api02_date_received'];
$arrRow['api02_genre'] = $row['api02_genre'];
$arrRow['api02_rating'] = $row['api02_rating'];
$arrRow['api02_date_released'] = $row['api02_date_released'];
$arrRow['api02_vendor_identifier'] = $row['api02_vendor_identifier'];
$arrRow['api02g_identifier'] = $row['api02g_identifier'];
$arrRow['api02g_genre'] = $row['api02g_genre'];

$arrReturn[] = $arrRow;
}

Summary

As you can see, once you get past the connection string and unusual number of steps to install the Microsoft driver, using PDO/parameterized queries against Microsoft SQL Server isn’t substantially different than using them under MySQL. That’s a good thing if you’re a PHP programmer. It means you can add this layer of protection against SQL Injection attacks to your PHP applications regardless of whether you use SQL Server or the more traditional MySQL (or MariaDB, in more recent Red Hat Enterprise Linux or CentOS distributions). Parameterized queries are an important part of any web application developer’s arsenal. It’s nice to see that it’s not too expensive to implement!

* While researching this article, I came across a quote from me about dBASE in a 1994 edition of ComputerWorld. Wow. That’s a long time ago.

** Of course it’s all patched to current! How could any self-respecting security professional do otherwise?

by Terrance A. Crow

Terrance has been writing professionally since the late 1990s — yes, he’s been writing since the last century! Though he started writing about programming techniques and security for Lotus Notes Domino, he went on to write about Microsoft technologies like SQL Server, ActiveX Data Objects, and C#. He now focuses on application security for professional developers because… Well, you’ve watched the news. You know why!