PHP mySQL Error with select query

Question

Few others have got this same problem at the outset, however, I'm repeating this question because mine seems to be specific to EMail IDs.

I have a query that retrieves the password given a specific email id as input. THis is my query

Select Password from userauth where user_name in (select id from users where email = 's.sriram@example.com')

This query executes without any problem when done from phpMyAdmin.

However, it doesn't work when I do it through a php script. That php script is as follows:

<?php

// Grab User submitted information
$email = $_POST["users_email"];
$pass = $_POST["users_pass"];

// Connect to the database
$con= mysql_connect("localhost","root","sriram123");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
// Select the database to use
mysql_select_db("mydb",$con);

echo "Select Password from userauth where user_name in (select id from users where email = '$email')";

$result = mysql_query("Select Password from userauth where user_name in (select id from users where email = $email)");

if($result === FALSE) {
    echo "Error Occured. ";
    die(mysql_error()); // TODO: better error handling
}

while($row = mysql_fetch_array($result))
{
    echo $row['Password'];
}


mysqli_close($con);
?>

Now, I get an error message like this when I execute it:

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 '@example.com)' at line 1

I am new to PHP, and i'm just not sure why this thing works in phpMyAdmin but fails to work in my PHP script.

Have you tried putting quotes around the variable? $email => '$email' — Elliot Lings, Sep 15 '13 at 16:20

score 6 · Answer 1 · edited Feb 08 '17 at 14:07

6

xkcd

It looks like you didn't put the proper quotes around the variable, but you should use mysql_real_escape_string to make sure it's actually safe to use in a query.

edited Feb 08 '17 at 14:07

Community

1
1

answered Sep 15 '13 at 16:23

Niet the Dark Absol

320,036
81
464
592

Laurent · Answer 2 · 2013-09-15T17:44:08.870

2

1) Use PDO

2) Escape MYSQL names (Select Password from -> Select `Password` from)

3) Quote variables ($email -> '$email')

4) Never expose your password ($con= mysql_connect("localhost","root","PASSWORD");)

Start with this :)

edited Sep 15 '13 at 17:44

answered Sep 15 '13 at 16:22

Laurent

1,292
4
23
47

1

5) Never use such a simple password anyway XD – Niet the Dark Absol Sep 15 '13 at 16:24

score 1 · Answer 3 · answered Sep 15 '13 at 16:26

$result = mysql_query("Select Password from userauth where user_name in
    (select id from users where email = $email)");

If $email is a@b.com it would be like:

$result = mysql_query("Select Password from userauth where user_name in
    (select id from users where email = a@b.com)");

You are missing the '' around the e-mail address, as its a string.

Also, always use mysql_real_escape_string to escape every external string put into your query, otherwise your site will be vulnerable to SQL Injection attacks.

score 0 · Answer 4 · edited May 23 '17 at 12:08

You don't have the correct quotes around the $email variable. Try wrapping them in single quotes.

Change your code to:

$email = mysql_real_escape_string($email); //escaping
$query = "
SELECT Password
FROM userauth
WHERE user_name IN
    (SELECT id
     FROM users
     WHERE email = '$email')
     ";

$result = mysql_query($query);

Also, your MySQL query, as it currently stands, is vulnerable to SQL injection, and you should look into escaping user input. Better yet, stop using the deprecated mysql_* functions and switch to MySQLi or PDO.

PHP mySQL Error with select query

4 Answers4