从MS SQL Server通过Linux上的PHP的非模拟预备语句支持 [英] Non-Emulated Prepared Statement support from MS SQL Server through PHP on Linux

问题描述

摘要

我正在尝试使用准备好的语句来停止SQL注入，但找不到所需的支持以确保其正常运行.

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.

场景

我正在Linux上托管一个站点，该站点正在连接到带有FreeTDS 0.91版的Microsoft SQL Server，特别是使用FreeTDS的dblib.我已将数据库连接的tds版本设置为7.4，并且正在使用PHP的 PDO 对象.

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.

根据 FreeTDS文档，4.2不支持准备好的语句:

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

TDS 4.2有局限性

TDS 4.2 has limitations

• 当然只有ASCII了.
• 不支持RPC.
• 不支持BCP.
• varchar字段限制为255个字符.如果您的表定义 较长的字段将被截断.
• 不支持动态查询(也称为预处理语句).

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

但是，没有任何迹象表明7.4不支持预备语句，这使我有足够的把握相信它们至少不会引发驱动程序错误.

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的PDO通过 PDO::setAttribute() 支持特定于连接的属性. 我对PDO::ATTR_ERRMODE将所有错误设置为异常感兴趣，并对PDO::ATTR_EMULATE_PREPARES强制数据库在兼容时执行准备好的语句感兴趣.

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.

问题

测试连接时，出现以下错误:

When testing the connection, I receive the following error:

数据库错误:SQLSTATE [IM001]:驱动程序不支持此功能:驱动程序不支持设置属性

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

无法设置PDO::ATTR_EMULATE_PREPARES，我无法保证数据库实际上正在按预期执行准备好的语句.

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

是否有修改我的方法，或者有其他方法来 保证准备好的语句可以在Linux的MS SQL Server上安全地执行?

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?

推荐答案

解决方案

使用ODBC代替dblib，它提供了PDO的全部功能. 请注意，ODBC有两种可能的配置:独立ODBC 和

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设置

我在网上搜索了许多不同的StackOverflow帖子和各种文档资源，以了解如何正确安装ODBC.我从以下三个参考文献的混合物中提取了解决方案:

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设置文档
• FlipperPA对设置FreeTDS (偶然用ODBC完成)
• Benny Hill对此的答案FreeTDS的安装问题

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

以下是我在基于Debian的系统上使用FreeTDS配置ODBC的步骤列表.

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

TDS 8.0支持准备好的语句.

TDS 8.0 supports prepared statements.

注意:在连接上将不支持SET NAMES a或SET CHARSET a；需要通过设置FreeTDS属性使用组合配置来定义字符集.使用独立的ODBC驱动程序默认将字符集设置为ASCII，这将产生奇怪的结果.有关可能的示例，请参见我的其他帖子.问题.

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.

安装必需软件包:

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

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

• freetds-bin提供FreeTDS以及tsql和isql(用于以后进行调试).
• freetds-common已经安装在系统上，但不包括两个调试工具.在定义配置后的以后再安装freetds-bin不会造成任何问题.
• unixodbc是ODBC驱动程序
• tdsodbc提供ODBC的TDS协议
• php5-odbc是用于ODBC驱动程序的php模块.请注意，您的php版本可能与我的不同.
• 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.

配置独立的unixODBC

/etc/odbcinst.ini中的ODBC驱动程序设置:

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

在/etc/odbc.ini中创建系统范围的数据源名称配置:

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

配置unixODBC和FreeTDS:

/etc/odbcinst.ini中的ODBC驱动程序设置:

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

在/etc/odbc.ini中创建系统范围的数据源名称配置:

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

将ODBC数据源名称配置添加到/etc/freetds/freetds.conf中的FreeTDS:

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

重要提示:请确保该进程可读取odbc文件 那将读他们.如果您使用 www-data用户，他们必须具有适当的权限才能阅读这些内容 文件！

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!

您现在可以在freetds.conf中设置连接字符集，并使用PDO作为连接到数据库

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

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

测试:

使用tsql检查FreeTDS是否已配置并可以连接到数据库.

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

tsql -S数据源名称-U用户名-P密码

tsql -S datasourcename -U username -P password

使用isql检查ODBC是否正确连接.

Use isql to check that ODBC is connecting properly.

isql -v数据源名称用户名密码

isql -v datasourcename username password

使用PHP链接ODBC:

通过添加以下内容，将ODBC PHP模块添加到php.ini:

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

extension = odbc.so

extension = odbc.so

请注意，您的php.ini位置将取决于您所使用的Web服务器. 使用<?php phpinfo(); ?>并通过网络服务器查看它的位置.

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.

重新启动Apache

添加了有关驱动程序字符集功能的信息，因为我遇到了独立ODBC配置的问题，在该配置中它将忽略任何更改连接字符集的尝试.

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.

这篇关于从MS SQL Server通过Linux上的PHP的非模拟预备语句支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！