CODEWITHSUNDEEP
phpwindowsms-accessodbc
Álvaro González
Álvaro González

Reputation: 146460

Escape input data in SQL queries when using ODBC + Access

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());
    }
}

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?

Upvotes: 2

Views: 1680

Answers (2)

&#193;lvaro Gonz&#225;lez
&#193;lvaro Gonz&#225;lez

Reputation: 146460

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])

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.

Upvotes: 1

BIBD
BIBD

Reputation: 15384

Typically in MS Access, you identify the parameters by placing them in brackets 

$sql = 'UPDATE street
        SET name=[myname]
        WHERE street_id=[mystreet]';

How that will mesh with php, I don't know.

Upvotes: 1

Related Questions