Hi,

I'm using php to connect to an access database picking up data entered on a form and inserting it into the database. It works fine when I don't have apostrophes in the data string but I get an error when there is an apostrophe.

if ($what2Do == "insertevent"){
$conn = odbc_connect('W_events','','');
$sql = "SELECT * FROM events";
$rs = odbc_exec($conn, $sql);
$tableFields = "";
$fields = "";
foreach ($_POST as $key => $value){
echo $value."<br />";
$value = stripslashes($value);
echo $value."<br />";
if ($key != "what2Do"){
$fieldNumber = odbc_field_num($rs, $key);
$fieldType = odbc_field_type($rs, $fieldNumber);
echo $fieldType."<br />";
$tableFields = $tableFields.$key.",";
if ($fieldType == "VARCHAR" || $fieldType == "LONGCHAR"){
$fields = $fields."'".addslashes($value)."',";} else {$fields = $fields.$value.",";
}
}
}
$tableFields = substr($tableFields, 0, -1);
$fields = substr($fields, 0, -1);
$sql = "INSERT into events ($tableFields) values ($fields)";
echo $sql."<br />";
$rs = odbc_exec($conn, $sql);
if ($rs != FALSE){echo "Data inserted sucessfully";}
}

With the addslashes command I get the following:

INSERT into events (event_date,event_name,event_description,event_type,event_Location,event_price) values (01/01/01,'Birthday Party','Paul\'s birthday party',2,'28 College close','28p')

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Paul\'s birthday party',2,'28 College close','28p')'., SQL state 37000 in SQLExecDirect in e:\sites.web\wcom\htdocs\admin\index.php on line 83

Without the addslashes command I get the following:

INSERT into events (event_date,event_name,event_description,event_type,event_Location,event_price) values (01/01/01,'Birthday Party','Paul's birthday party',2,'28 College close','28p')

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Paul's birthday party',2,'28 College close','28p')'., SQL state 37000 in SQLExecDirect in e:\sites.web\wcom\htdocs\admin\index.php on line 80

If I omit the stripslashes command I get the following:

INSERT into events (event_date,event_name,event_description,event_type,event_Location,event_price) values (01/01/01,'Birthday Party','Paul\'s birthday party',2,'28 College close','28p')

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Paul\'s birthday party',2,'28 College close','28p')'., SQL state 37000 in SQLExecDirect in e:\sites.web\wcom\htdocs\admin\index.php on line 80

Thanks

Paul

    addslashes() is almost definitely the wrong function to use (and stripslashes likewise, unless you have magic_quotes_gpc on and wish to reverse its effects). You have two choices here:
    1. Figure out what is the correct way of escaping, or
    2. Use a prepared statement.

    A brief look through the PHP manual on the ODBC extension shows that there is no specific function available, so perhaps the correct way is to double your quotes using [man]str_replace/man. Using a prepared statement with say, odbc_prepare() could be even better.

      Resolved:

      I was converting the page from asp to php, so looked at the asp constructed query string at it used another apostrophe before the apostrophe in Paul's, so I changed the addslashes command to str_replace and replaced the single apostrophe with a pair of apostrophies and it worked fine.

        Thanks Laserlight - didn't see your reply whilst I was posting mine but nice to see that we came to the same conclusion.

        Thanks again

        Paul

          Write a Reply...