php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #13809 Openlink 3.2 and 4.0 odbc_do and single quotes
Submitted: 2001-10-24 05:19 UTC Modified: 2003-07-10 20:03 UTC
From: Grant dot Walters at walters dot co dot nz Assigned:
Status: Not a bug Package: ODBC related
PHP Version: 4.3.1 OS: SCO Openserver 5.0.5 & RH Lnux 7
Private report: No CVE-ID: None
 [2001-10-24 05:19 UTC] Grant dot Walters at walters dot co dot nz
Came across this issue when doing my data conversions.  If the fields have single quotes in them, odbc_do fails.

I have tested this against the Openlink 3.2 and 4.1 SDK's and found that using odbc_prepare works fine.

Basic Script
------------
<?
$conn = odbc_connect("$dsn","","","$cursor");
$sql="SELECT ID,Category,description FROM card_type WHERE description='IMPEYS'";
echo "<BR><b>SQL:</b> $sql<BR>";
$results = odbc_do($conn,$sql);
if ($results) {
  while (odbc_fetch_into($results,$row)) {
    echo $row[0]." ".$row[1]." ".$row[2]."\n";
  }
}
$sql="SELECT ID,Category,description FROM card_type WHERE description LIKE '%PEP%'";
echo "<BR><b>SQL:</b> $sql<BR>";
$results = odbc_do($conn,$sql);
if ($results) {
  while (odbc_fetch_into($results,$row)) {
    echo $row[0]." ".$row[1]." ".$row[2]."\n";
  }
}
$sql='SELECT ID,Category,description FROM card_type WHERE description LIKE "%PEP%"';
echo "<BR><b>SQL:</b> $sql<BR>";
$results = odbc_do($conn,$sql);
if ($results) {
  while (odbc_fetch_into($results,$row)) {
    echo $row[0]." ".$row[1]." ".$row[2]."\n";
  }
}
$sql='SELECT ID,Category,description FROM card_type WHERE description="PEPPERELL\'S"';
echo "<BR><b>SQL:</b> $sql<BR>";
$results = odbc_do($conn,$sql);
if ($results) {
  while (odbc_fetch_into($results,$row)) {
    echo $row[0]." ".$row[1]." ".$row[2]."\n";
  }
}
$sql="SELECT ID,Category,description FROM card_type WHERE description=\"PEPPERELL'S\"";
echo "<BR><b>SQL:</b> $sql<BR>";
$results = odbc_do($conn,$sql);
if ($results) {
  while (odbc_fetch_into($results,$row)) {
    echo $row[0]." ".$row[1]." ".$row[2]."\n";
  }
}

/*
Output
------
SQL: SELECT ID,Category,description FROM card_type WHERE description='IMPEYS'
355 Other Item IMPEYS 

SQL: SELECT ID,Category,description FROM card_type WHERE description LIKE '%PEP%'
177 Other Item PEPPERELL'S 

SQL: SELECT ID,Category,description FROM card_type WHERE description LIKE "%PEP%"

Warning: SQL error: [OpenLink][ODBC][Driver]Syntax error or access, SQL state 37000 in SQLExecDirect in /usr/local/.WWW/WEBS/_odbc/test.php3 on line 42

SQL: SELECT ID,Category,description FROM card_type WHERE description="PEPPERELL'S"

Warning: SQL error: [OpenLink][ODBC][Driver]Syntax error or access, SQL state 37000 in SQLExecDirect in /usr/local/.WWW/WEBS/_odbc/test.php3 on line 50

SQL: SELECT ID,Category,description FROM card_type WHERE description="PEPPERELL'S"

Warning: SQL error: [OpenLink][ODBC][Driver]Syntax error or access, SQL state 37000 in SQLExecDirect in /usr/local/.WWW/WEBS/_odbc/test.php3 on line 58

*/
?>

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-05-13 18:18 UTC] kalowsky@php.net
did some examination on this, and I believe it lies in the OpenLink software... as I see the same problem here, but not on my Windows emulation.  Andrew any chance you can take a look into this further?
 [2002-05-22 15:22 UTC] kalowsky@php.net
Assigning to Andrew
 [2003-01-19 19:00 UTC] kalowsky@php.net
Please try using this CVS snapshot:

  http://snaps.php.net/php4-STABLE-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php4-win32-STABLE-latest.zip

Removing ahill assigned to status, and asking to try a newer version
 [2003-02-04 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over 2 weeks, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2003-05-03 16:57 UTC] Grant dot Walters at walters dot co dot nz
Sorry about the delay.
I have tested with the 4.1 openlink and have drawn the same basic conclusions as previously with odbc_do.
A straight query can not be built when the value being looked for contains a (') character.
No combination of character escaping or quote usage (" vs ') seems to alleviate the issue.
 [2003-07-10 18:52 UTC] sniper@php.net
After searching with google for this issue, it's pretty clear your code is broken, you should use single quotes in the WHERE statements. Anyway, this can't really be PHP bug..

 [2003-07-10 20:03 UTC] Grant dot Walters at walters dot co dot nz
Sniper.  Did you even read my basic code sample?  The problem is how to build a query that contains data that contains a single quote.  I agree it is/was probably an Openlink driver issue.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC