How to check if value exists in MySQL Database in PHP?

Question

I am new to PHP but I like to create a script that checks if an email is in my MySQL Database, the Database name is "Fily_Registrations", the table is "users" and the value is called "email". So basically if an the email "hi@all.com" exists in my database and I call the php script like "http://path/to/php.php?email=hi@all.com" it should echo out "YES" if it don't exists it should echo out "NO".

This is how fare I am now, but it always echoes out "NO":

<?php

$email = $_GET["email"];
$DataBase = "Fily_Registrations";
mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db($DataBase) or die(mysql_error());
$string = sprintf("SELECT '$DataBase' FROM users WHERE email = '$email'");
$query = msql_query($string);
if($query == false) {
     echo("No");
} else {
    echo("Yes");
}
?>

Does anyone know how to fix this?

May be mysql_num_rows($query) and check if its 0 then no data else there are ? — Abhik Chakraborty, Jan 11 '14 at 16:59
The code is very vulnerable. There are multiple attack vectors here: you're using `sprintf()` without arguments and interpolating user-defined values into the query without escaping them. — Linus Kleen, Jan 11 '14 at 17:00
There seems to be a problem in your query syntax. It should be SELECT [column_name] FROM [table_name] WHERE [condition] and not SELECT [db_name] FROM [table_name] WHERE [condition] — TranQ, Jan 11 '14 at 17:03

score 5 · Accepted Answer · edited Jan 11 '14 at 17:15

If database is Fily_Registrations then the query is wrong. Try this:

$email = mysql_real_escape($_GET["email"]);
$DataBase = "Fily_Registrations";
mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db($DataBase) or die(mysql_error());
$query = "SELECT * FROM users WHERE email = '{$email}'";
$result = mysql_query($query);
echo (mysql_num_rows($result) == 0) ? 'NO' : 'YES';

mysql_query returns false if query is not correct. Read first http://pl1.php.net/mysql_query and consider using PDO (http://pl1.php.net/pdo) instead of normal mysql_query.

Miguel Q. · Answer 2 · 2014-01-11T17:18:01.727

0

Your code should be something like this:

<?php

$email = $_GET["email"];
$DataBase = "Fily_Registrations";
mysql_connect("server", "username", "password") or die(mysql_error());
mysql_select_db($DataBase) or die(mysql_error());
$string = mysql_real_escape("SELECT * FROM users WHERE email = '$email'");
$query = msql_query($string);
if($query) {
     echo mysql_num_rows($query) > 0 ? "YES" : "NO";
} else {
    echo("====some error===");
}
?>

mysql_num_rows : http://pt1.php.net/manual/en/function.mysql-num-rows.php mysql_real_escape: http://pt2.php.net/mysql_real_escape_string

edited Jan 11 '14 at 17:18

answered Jan 11 '14 at 17:03

Miguel Q.

567
4
14

A good answer should address this issue. Please do so. And don't just copy-and-paste the OP's code and make corrections. Instead, *explicitly* hint at `mysql_num_rows()` and tell him/her its significance. – Linus Kleen Jan 11 '14 at 17:11

Kari Knuuttila · Answer 3 · 2014-01-11T17:49:18.587

Not yet super safe, but better.

Email checked for unsafe quotes with mysql_real_escape. Don't place too much trust on this thought. Some additional regex checking could be in order.

Prepared statements don't allow hidden mysql code execution from within parameters.

$email = mysql_real_escape($_GET["email"]);
try {
 $connect = new PDO("mysql:host=server;dbname=Fily_Registrations;port=3306", "user", "password");
}catch(PDOException $e) {
  print "Error!: " . $e->getMessage();
  die();
}

$q= "SELECT * FROM users WHERE email = :EMAIL";
$statement = $connect->prepare($q);
$status = $statement->execute(array(":EMAIL"=>$email));

if (($status) && ($statement->rowCount() > 0))
{
  echo "YES";
} else {
  echo "NO";
}

Br, Kari

How to check if value exists in MySQL Database in PHP?

3 Answers3