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 = '[email protected]')

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
– Elliot Lings, Commented Sep 15, 2013 at 16:20

Community · Accepted Answer · 2017-02-08 14:07:40Z

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 8, 2017 at 14:07

CommunityBot

11 silver badge

answered Sep 15, 2013 at 16:23

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

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

Comments

Astrid · Accepted Answer · 2013-09-15 17:44:08Z

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, 2013 at 17:44

answered Sep 15, 2013 at 16:22

Astrid

1,3124 gold badges24 silver badges49 bronze badges

1 Comment

Niet the Dark Absol Over a year ago

5) Never use such a simple password anyway XD

Havenard · Accepted Answer · 2013-09-15 16:26:47Z

1

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

If $email is [email protected] it would be like:

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

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.

answered Sep 15, 2013 at 16:26

Havenard

28.1k5 gold badges38 silver badges66 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:08:51Z

0

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.

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Sep 15, 2013 at 16:24

Amal

76.8k18 gold badges133 silver badges154 bronze badges

Collectives™ on Stack Overflow

PHP mySQL Error with select query

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related