' screwing up query

[jakebur01]

I am doing an odbc query. And one of the e-mails has a ' in it.

 

Example: woody'squote@example.net

 

Warning: odbc_exec() [function.odbc-exec]: SQL error: [ProvideX][ODBC Driver]Unexpected extra token: woody'squote@example.net, SQL state 37000 in SQLExecDirect in C:\Inetpub\wwwroot\smithsadvantage\failed_messages.php on line 40

Error in SQL

 

However, I still want to be able to select info from the database based on the email address above.  How can I still query the data using a value that has quotes and other weird characters?

 

if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT CUSTOMER_NUM, CUSTOMER_NAME, PHONE_1 FROM AR_CUST_MAST WHERE EMAIL_1 = '$line'";
//print $sql;
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}

while (odbc_fetch_row($rs))
  {
$acct=odbc_result($rs,"CUSTOMER_NUM");
$name=odbc_result($rs,"CUSTOMER_NAME");
$phone=odbc_result($rs,"PHONE_1");

  }

 

[fugix]

escape the special chars using mysql_real_escape_string()

[jakebur01]

But, this is an odbc query?  Can I still generate a MySQL connection, store mysql_real_escape_string() into a variable, and then use that value with my odbc query?

[fugix]

But, this is an odbc query?  Can I still generate a MySQL connection, store mysql_real_escape_string() into a variable, and then use that value with my odbc query?

i must have posted that without thinking, you cant call that function without a valid link established. maybe you can try using htmlentities() to insert data, and html_entity_decode()to draw them out of your db

[jakebur01]

Mmmnn.  There's gotta be a way to do this.

[jakebur01]

As an alternative, could I do something like this so it will skip the query all together? Like:

 

if($line contains ' OR $line contains " OR $line contains ,)

{

//do nothing

}

else

{

... process query

}

[fugix]

not sure what your logic behind doing that is, how would you grab the correct data for the user?

[kenrbnsn]

It looks like you need to use another single quote to escape a single quote when using odbc queries, so if you have the string

<?php
$str = "woody'squote@example.net"
?>

to use it in a odbc query, you could do

<?php
$str = str_replace("'","''",$str);
?>

before using it.

 

Ken

[jakebur01]

Cool! What about if it contained a double quote? How would you escape that?

 

It looks like you need to use another single quote to escape a single quote when using odbc queries, so if you have the string

<?php
$str = "woody'squote@example.net"
?>

to use it in a odbc query, you could do

<?php
$str = str_replace("'","''",$str);
?>

before using it.

 

Ken

[jakebur01]

It would not return data, but it would keep my script from hanging up.

 

not sure what your logic behind doing that is, how would you grab the correct data for the user?

[fugix]

i did some research on this topic because i was curious about it myself, i believe that the same rule applies for most special characters with obdc queries. doubling up the special char to escape it

 

 

 

[jakebur01]

How do you use a double quote in str_replace?

$queryline = str_replace(""","'""",$line);

 

i did some research on this topic because i was curious about it myself, i believe that the same rule applies for most special characters with obdc queries. doubling up the special char to escape it

 

 

 

[wildteen88]

How do you use a double quote in str_replace?

$queryline = str_replace(""","'""",$line);

Either use single quotes wrapped around the double quores

$queryline = str_replace('"','""',$line);

 

OR escape the double quotes

$queryline = str_replace("\"","\"\"",$line);