PHP MySQL query error when passing a string

Question

Hi I am having an issue with a MySQL query not passing a string variable.

If $pass contains only numbers it works fine. When it contains letters I get the Cannot execute the query error.

Example:

$pass=123456     //works fine
$pass=z23456     //cannot execute the query
$_SESSION['id']=$pass;           //start session

if (isset($_SESSION['id'])) {

// Query database for user information.
$query = "SELECT RepName FROM RepTable WHERE RepNumber = 
".$_SESSION['id']."";
$result = mysql_query ($query) OR die ('Cannot execute the query.');
$rinfo = mysql_fetch_array ($result);
$RepInfo = $rinfo[0];

A debugging tip: try OR die ("Cannot execute the query $query"); - use in development only - NEVER use it in production. This will show you that, if for example $pass="foo" your SQL statement reads SELECT RepName FROM RepTable WHERE RepNumber = foo - as you can see, foo is not a valid identifier, function or number, so will need to be quoted. Assuming that RepNumber is a string type, mgraph's answer is your solution. If RepNumber is a numeric type, then you will need to validate $pass first. — daiscog
– daiscog, Commented Jun 19, 2012 at 14:34
Incidentally, use of the mysql extension is discouraged; you really ought to consider using the mysqli or pdo extensions instead. See here for more information — daiscog
– daiscog, Commented Jun 19, 2012 at 14:39

mgraph · Accepted Answer · 2012-06-19 14:32:43Z

5

Try :

'".$_SESSION['id']."'"

instead of :

".$_SESSION['id'].""

you can also add:

mysql_escape_string($_SESSION['id'])

edited Jun 19, 2012 at 14:32

answered Jun 19, 2012 at 14:26

mgraph

15.3k4 gold badges43 silver badges76 bronze badges

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

Comments

Emmanuel Tabard · Accepted Answer · 2012-06-19 14:30:18Z

1

You should use prepared statements with mysql, it's safer (SQL injections for example) :

http://php.net/manual/en/pdo.prepared-statements.php

Eg:

$stmt = $dbh->prepare("SELECT RepName FROM RepTable WHERE RepNumber = ?");
$stmt->execute($_SESSION['id'])

answered Jun 19, 2012 at 14:30

Emmanuel Tabard

6965 silver badges14 bronze badges

Collectives™ on Stack Overflow

PHP MySQL query error when passing a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related