在PHP PostgreSQL中转义SQL查询

I have a website with lots of PHP files (really a lot...), which use the pg_query and pg_exec functions which do not escape the apostrophe in Postgre SQL queries.

我有一个网站，上面有很多PHP文件(真的很多…)，它们使用pg_query和pg_exec函数，在Postgre SQL查询中不会转义撇号。

However, for security reasons and the ability to store names with apostrophe in my database I want to add an escaping mechanism for my database input. A possible solution is to go through every PHP file and change the pg_query and pg_exec to use pg_query_params but it is both time consuming and error prone. A good idea would be to somehow override the pg_query and pg_exec to wrapper functions that would do the escaping without having to change any PHP file but in this case I guess I will have to change PHP function definitions and recompile it which is not very ideal.

但是，出于安全原因和在数据库中使用撇号存储名称的能力，我想为数据库输入添加一个转义机制。一种可能的解决方案是遍历每个PHP文件并更改pg_query和pg_exec以使用pg_query_params，但这既费时又容易出错。一个好主意是，以某种方式重写pg_query和pg_exec到包装器函数，这些函数可以在不需要修改任何PHP文件的情况下进行转义，但是在这种情况下，我想我必须修改PHP函数定义并重新编译它，这不是很理想。

So, the question is open and any ideas that would allow to do what I want with minimum time consumption are very welcome.

所以，这个问题是开放的，任何能让我用最少的时间做我想做的事的想法都是非常受欢迎的。

2 个解决方案

#1

This is a perfect example of when a database layer and associated API will save you loads of time. A good solution would be to make a DB class as a singleton, which you can instantiate from anywhere in your app. A simple set of wrapper functions will allow you to make all queries to the DB go through one point, so you can then alter the way they work very easily. You can also change from one DB to another, or from one DB vendor to another without touching the rest of the app.

这是一个很好的例子，说明数据库层和相关的API什么时候可以节省大量的时间。一个好的解决方案是做一个数据库作为一个单例类,你可以从任何地方在你实例化应用程序。一组简单的包装器函数允许您将所有查询DB经过一个点,所以你可以很容易改变他们的工作方式。您还可以从一个DB切换到另一个DB，或者从一个DB供应商切换到另一个DB供应商，而无需触摸应用程序的其他部分。

The problem you are having with escaping is properly solved by using the PDO interface, instead of functions like pg_query(), which makes escaping unnecessary. Seeing as you'll have to alter everywhere in your app that uses the DB, you may as well refactor to use this pattern at the same time as it'll be the same amount of work.

使用PDO接口可以正确地解决您正在使用的转义问题，而不是像pg_query()这样的函数，它使转义变得不必要。由于在使用DB的应用程序中，您必须对所有地方进行修改，因此您最好在使用这个模式的同时重构它，因为它的工作量是相同的。

class db_wrapper {

    // Singleton stuff
    private $instance;

    private function __construct() {
        // Connect to DB and store connection somewhere
    }

    public static function get_db() {
        if (isset($instance)) {
            return $instance;
        }
        return $instance = new db_wrapper();
    }

    // Public API

    public function query($sql, array $vars) {
        // Use PDO to connect to database and execute query
    }

}

// Other parts of your app look like this:

function do_something() {
    $db = db_wrapper::get_db();
    $sql = "SELECT * FROM table1 WHERE column = :name";
    $params = array('name' => 'valuename');
    $result = $db->query($sql, $params);

    // Use $result for something. 
}

#2

You post no code but I guess you have this:

你没有发布代码，但我猜你有:

$name = "O'Brian";
$result = pg_query($conn, "SELECT id FROM customer WHERE name='{$name}'");

... and you'd need to have this:

…你需要这样:

$name = "O'Brian";
$result = pg_query_params($conn, 'SELECT id FROM customer WHERE name=$1', array($name));

... but you think the task will consume an unreasonable amount of time.

…但是你认为这个任务会花费不合理的时间。

While it's certainly complex, what alternatives do you have? You cannot override pg_query() but it'd be extremely simple to search and replace it for my_pg_query(). And now what? Your custom function will just see strings:

虽然它确实很复杂，但你有什么选择呢?您不能覆盖pg_query()，但是为my_pg_query()搜索和替换它会非常简单。现在什么?您的定制函数将只看到字符串:

SELECT id FROM customer WHERE name='O'Brian'
SELECT id FROM customer WHERE name='foo' OR '1'='1'

Even if you manage to implement a bug-free SQL parser:

即使您设法实现了一个无bug的SQL解析器:

1. It won't work reliably with invalid SQL.
2. 对于无效的SQL，它不能可靠地工作。
3. It won't be able to determine whether the query is the product of intentional SQL injection.
4. 它无法确定查询是否是有意SQL注入的产物。

Just take it easy and fix queries one by one. It'll take time but possibly not as much as you think. Your app will be increasingly better as you progress.

只要轻松一点，逐个修复查询。这需要时间，但可能没有你想的那么多。随着你的进步，你的应用会越来越好。