PHP / MySQL SELECT query using email address mystery

Question

I have a particularly puzzling problem.

I am using PHP to loop through a recordset and then identify if an email address exists in another table.

The code all works fine until it gets to one particular email address and I can't for the life of me see what is wrong.

The email address is [email protected]. I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ambro'' at line 1

All other email address are fine.

I echo the query

SELECT * FROM user_details WHERE email='[email protected]'

and run it in Navicat and it works

PHP Code as follows:

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

/*Get source data*/
mysql_select_db($database, $link);
$query_clients = "SELECT email FROM clients ORDER BY client_id DESC";
$clients = mysql_query($query_clients, $link) or die(mysql_error());
$row_clients = mysql_fetch_assoc($clients);
$totalRows_clients = mysql_num_rows($clients);

do {
    /*Check table to see if email already exists*/
 $query_check = sprintf("SELECT * FROM user_details WHERE email=%s",GetSQLValueString($row_clients['email'],"text"));
 echo "<br>".$query_check."<br>";
 $check = mysql_query($query_check, $link) or die(mysql_error());
 if (mysql_num_rows($check)==0) {
  $query_insertUsers = sprintf("INSERT INTO users (username, password, userlevel) VALUES (%s, %s, 1)", $username, $password);
  echo $query_insertUsers."<br>";
  //$insertUsers = mysql_query($query_insertUsers, $link) or die(mysql_error());
 }
} while ($row_clients = mysql_fetch_assoc($clients));

mysql_free_result($clients);

As I said - this code WORKS, it is only when trying to query with this one email address that it fails.

don't use echo, try var_dump and view source (if in web environment) — ajreal
– ajreal, Commented Dec 1, 2010 at 14:19
@Borealid - Interesting. I will certainly have to look into that. — Pandy
– Pandy, Commented Dec 1, 2010 at 14:31
Not to mention that your GetSQLValueString function is wrong — Your Common Sense
– Your Common Sense, Commented Dec 1, 2010 at 15:16

Piskvor left the building · Accepted Answer · 2010-12-01 13:58:14Z

3

This looks like the escaping is going wrong somehow: right syntax to use near 'ambro'' seems to indicate that the e-mail might be actually marcod'[email protected]. If you do

echo "<br>".$query_check."<br>";

and run that in Navicat, does that have the same error?

answered Dec 1, 2010 at 13:58

Piskvor left the building

93.1k46 gold badges182 silver badges226 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Levi Hackwith Over a year ago

I agree. There's a good chance there's some UTF-8 character that isn't being rendered when the query is printed, but is being interpreted by MySQL. +1

Brad Over a year ago

Maybe he's got a left or right single quote in there rather than a regular one, and Navicat is fixing this for him.

Piskvor left the building Over a year ago

@Brad: From my experience, NC won't fix your errors for you, precisely for debugging reasons. Some sort of zero-width character maybe...

ajreal · Accepted Answer · 2010-12-01 14:10:53Z

1

Run the following query:

SELECT * FROM user_details WHERE email LIKE 'marco%'

I'm willing to bet that what you actually have in the database is marcod'[email protected] (note the ' included). This probably happened during some kind of auto-generation of the email addresses.

edited Dec 1, 2010 at 14:10

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

answered Dec 1, 2010 at 14:00

Patrick Mozola

113 bronze badges

1 Comment

Pandy Over a year ago

No - definitely no apostrophes or quotes!

user229044 · Accepted Answer · 2010-12-01 14:50:00Z

0

Are you sure that email is inside quotes?

edited Dec 1, 2010 at 14:50

user229044♦

241k41 gold badges346 silver badges350 bronze badges

answered Dec 1, 2010 at 14:18

Padmanabha Vn

6241 gold badge14 silver badges33 bronze badges

1 Comment

Pandy Over a year ago

Yes. I am using the GetSQLValueString function (nicked from Dreamweaver) which adds the quotes and escapes characters.

Collectives™ on Stack Overflow

PHP / MySQL SELECT query using email address mystery

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related