使用ODBC + Access时在SQL查询中转义输入数据 [英] Escape input data in SQL queries when using ODBC + Access

问题描述

我已经尝试过odbc_prepare() + odbc_execute()来更新Access文件中的记录，但是我总是收到关于列数不正确的SQL state 07001错误消息(实际上，该消息使用的是Spanglish，并且收效不大感):

I've tried odbc_prepare() + odbc_execute() to update a record in an Access file but I always get an SQL state 07001 error message about incorrect column count (actually, the message is in Spanglish and doesn't make much sense):

<?php
$items = array();
$items[100] = 'Foo';
$items[200] = 'Bar';
$sql = 'UPDATE street
    SET name=?
    WHERE street_id=?';
$stmt = odbc_prepare($conection, $sql);
if( $stmt===FALSE ){
    die(odbc_errormsg());
}
foreach($items as $cod => $name){
    if( !odbc_execute($stmt, array($name, $cod)) ){
        die(odbc_errormsg());
    }
}

odbc_execute手册页上的用户评论表明Microsoft Access ODBC驱动程序不支持参数化查询.但是，我还没有找到odbc_ *函数来转义数据.

User comments at odbc_execute manual page suggest that Microsoft Access ODBC drivers do not support parameterized queries. However, I haven't found an odbc_* function to escape data.

那么...我该如何转义输入数据?

So... How can I escape input data?

推荐答案

我一直在尝试随机的东西.如果您使用以下一种语法(甚至混合使用)，似乎odbc_prepare()会检测到参数:

I've been trying out random stuff. It seems that odbc_prepare() detects parameters if you use one of these syntaxes (or you even mix them):

• INSERT INTO foo (bar) VALUES (:param)
• INSERT INTO foo (bar) VALUES ([param])

• INSERT INTO foo (bar) VALUES (:param)
• INSERT INTO foo (bar) VALUES ([param])

但是，odbc_execute()会抱怨缺少参数，无论您用什么来喂(数值数组，关联数组...).并且它将知道无法找到的确切参数数量.这使得整个机制完全没有意义.

However, odbc_execute() will complain about missing parameters no matter what you feed it with (numeric array, associative array...). And it'll know the exact number of parameters that cannot be found. That makes the whole mechanism completely pointless.

不幸的是，我的最好的解决办法，到目前为止是这样的:

Sad to say, my best solution so far is this:

/**
 * Escape a string to be inserted into Access via ODBC
 */
function odbc_escape_string_access($value){
    $replacements= array(
        "'" => "''",
    );
    return strtr($value, $replacements);
}

这太可怕了，但我找不到更好的东西.

It's horrible but I couldn't find anything better.

这篇关于使用ODBC + Access时在SQL查询中转义输入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！