phpsql-serverlinuxpdofreetds

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


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?


Solution

  • 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:

    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

    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.