1

I'm Updating with mysql over ODBC Filemaker Table.
When a field contains o'reilly or example'two I get this error message:

Warning: odbc_exec(): SQL error: [FileMaker][FileMaker] FQL0001/(1:80):
There is an error in the syntax of the query., SQL state 42000 in SQLExecDirect in C:\fm_1.php on line 49

and using addslashes() does not work.

thank you!

this is my code: 

<?php
$conn = odbc_connect("DSN=Server;Database=TEST;UID=odbc;PWD=1234", "odbc", "1234");
if ($conn)
    echo "\nConnection established.";
else
    die("\nConnection could not be established.");

$result = odbc_exec($conn, "SELECT ID_MH, MH_Name FROM myTable WHERE MH_Name LIKE '%EXAMPLE'");
while ($row = odbc_fetch_array($result)) {

    $ID_MH = $row["ID_MH"];
    $MH_Name = $row["MH_Name"]; 

    // do something

    $MH_Name = addslashes($MH_Name);
    $update = "UPDATE myTable SET MH_Name='$MH_Name' WHERE ID_MH=" . $ID_MH;    
    $data_update = odbc_exec($conn, $update);

} 
odbc_close($conn);
?>
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Patrik777
  • 11
  • 3
  • 1
    Is MySQL actually relevant here? The posted code only seems to deal with the FMP database via ODBC. – Michael Berkowski Dec 22 '16 at 15:27
  • The correct solution to this problem is to use `odbc_prepare()/odbc_execute()` instead of `odbc_exec()` with variables in your SQL statement. But you should verify if FMP supports prepared statements. https://stackoverflow.com/questions/5713837/correct-way-to-escape-input-data-before-passing-to-odbc – Michael Berkowski Dec 22 '16 at 15:46

2 Answers2

0

Try to escape instead of using addslashes:

"UPDATE myTable SET MH_Name=\"$MH_Name\" WHERE ID_MH=" . $ID_MH;

Nicolai Kant
  • 1,391
  • 1
  • 9
  • 23
0

here is the solution: 

$query = 'UPDATE myTable SET MH_Name=? WHERE ID_MH=?';
$stmt = odbc_prepare ($conn, $query);
$success = odbc_execute($stmt, array($MH_Name, $ID_MH));

source:https://www.skeletonkey.com/FileMaker_11_ODBC_Drivers/

thanks!

Patrik777
  • 11
  • 3