Non-Emulated Prepared Statement support from MS SQL Server through PHP on Linux

Question

Summary

I'm attempting to use prepared statements to stop SQL Injections, but am unable to find the support I need to guarantee it is working properly.

---

Scenario

I am hosting a site on Linux which is connecting to a Microsoft SQL Server with FreeTDS version 0.91, specifically using FreeTDS's dblib. I have set the tds version to 7.4 for the database connection, and am using PHP's PDO object.

According to the FreeTDS documentation, 4.2 does not support prepared statements:

TDS 4.2 has limitations

• ASCII only, of course.
• RPC is not supported.
• BCP is not supported.
• varchar fields are limited to 255 characters. If your table defines longer fields, they'll be truncated.
• dynamic queries (also called prepared statements) are not supported.

However there is nothing indicating that 7.4 doesn't support prepared statements, which gives me reasonable confidence they at least won't throw a driver error.

PHP's PDO supports connection specific attributes via PDO::setAttribute(). I am interested in PDO::ATTR_ERRMODE to set all errors as exceptions, and PDO::ATTR_EMULATE_PREPARES to force the database to do prepared statements if compatible.

---

Issue

When testing the connection, I receive the following error:

Database error: SQLSTATE[IM001]: Driver does not support this function: driver does not support setting attributes

Without being able to set PDO::ATTR_EMULATE_PREPARES, I am unable to guarantee the database is actually executing the prepared statements as intended.

Is there anyway to modify my approach, or is there an alternative approach, to guarantee that prepared statements are being executed securely on an MS SQL Server from Linux?

Answer 1

Solution

Use ODBC instead of dblib, which provides the full functionality of PDO. Note that there is two possible configurations of ODBC: standalone ODBC and FreeTDS with ODBC driver. From my experience, to set the character set for a connection, it must be done through FreeTDS using the ODBC driver, making the combined configuration preferable.

---

ODBC Setup

I searched through many different StackOverflow posts and various documentation sources on the web on how to properly install ODBC. I pulled my solution from a mixture of the following three references:

• unixODBC setup documentation
• FlipperPA's answer to setting up FreeTDS (incidentally done with ODBC)
• Benny Hill's answer to this setup issue with FreeTDS

Below is the list of steps I used to configure ODBC using FreeTDS on a Debian based system.

TDS 8.0 supports prepared statements.

NOTE: Will not support SET NAMES a or SET CHARSET a on a connection; character sets need to be defined using the combined config by setting a FreeTDS attribute. Using the standalone ODBC driver defaulted the charset to ASCII, which gave odd results. See my other post for examples of possible issues.

Install require packages:

sudo apt-get install freetds-bin freetds-common unixodbc tdsodbc php5-odbc

• freetds-bin provides FreeTDS, as well as tsql and isql (used for debugging later).
• freetds-common was already installed on the system, but does not include the two debugging tools. Installing freetds-bin at a later date after a config was defined cause no issue.
• unixodbc is the ODBC driver
• tdsodbc provides the TDS protocol for ODBC
• php5-odbc is the php module to used ODBC drivers. Note that your php version may differ from mine.

Configure Standalone unixODBC

ODBC driver settings in /etc/odbcinst.ini:

[odbc]
Description     = ODBC driver
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so 
UsageCount      = 1

Create a system wide datasource name configuration in /etc/odbc.ini:

[datasourcename]
 Driver         = odbc
 Description    = Standalone ODBC
 Server         = <IP or hostname>
 Port           = <port>
 TDS_Version    = 8.0

Configure unixODBC and FreeTDS:

ODBC driver settings in /etc/odbcinst.ini:

[odbc]
Description     = ODBC driver
Driver          = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup           = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so 
UsageCount      = 1

Create a system wide datasource name configuration in /etc/odbc.ini:

[datasourcename]
Driver          = FreeTDS_odbc
Description     = Uses FreeTDS configuration settings defined in /etc/freetds/freetds.conf
Servername      = datasourcename
TDS_Version     = 8.0

Add the ODBC datasource name config to FreeTDS in /etc/freetds/freetds.conf:

[datasourcename]
    host = <IP or hostname>
    port = <port>
    client charset = UTF-8
    tds version = 8.0
    text size = 20971520
    encryption = required

IMPORTANT: make sure that the odbc files are readable by the process that will be reading them. If you are running your webserver using a www-data user, they must have the proper permissions to read those files!

You can now set the connection character set in freetds.conf and connect to the database with PDO as

$pdo = new PDO('odbc:datasourcename');

Testing:

Use tsql to check that FreeTDS is configured and can connect to the database.

tsql -S datasourcename -U username -P password

Use isql to check that ODBC is connecting properly.

isql -v datasourcename username password

Link ODBC with PHP:

Add ODBC PHP module to php.ini by adding the following:

extension = odbc.so

Note that your php.ini location will depend on what webserver you are using. Use <?php phpinfo(); ?> and view it through the webserver to find its location.

Restart Apache

EDIT: Added information regarding character set capabilities of the driver, as I ran into issues with the standalone ODBC configuration where it would ignore any attempt to change the connection's character set.