CODEWITHSUNDEEP
phpmysql
pencilshree
pencilshree

Reputation: 289

PHP mySQL Error with select query

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.

Upvotes: 0

Views: 3504

Answers (4)

Astrid
Astrid

Reputation: 1312

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 :)

Upvotes: 2

Havenard
Havenard

Reputation: 27854

$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.

Upvotes: 1

Amal Murali
Amal Murali

Reputation: 76666

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.

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324610

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.

Upvotes: 6

Related Questions