php check if email exists in mysql database

Question

For some reason it always returns 0 even when there is an identical email in the database. Is there something wrong with my syntax? The code worked when I did it without a prepare/execute statement. Is it necessary here for security reasons?

$email= $conn->real_escape_string($_POST['email']);

function emailExists($conn, $email) {
    $stmt = $conn->prepare("SELECT 1 FROM accountInfo WHERE email=(?)");
    $stmt->bind_param($email);
    $stmt->execute();
    return $stmt->num_rows;
}

echo emailExists($conn, $email);

Don't use real_escape_string when you're parameterising the query. it doesn't add any protection, and can also easily over-encode your data. Remove the call to real_escape_string and try again. If you still have an issue then, please a) update your code to its newest version, b) give an example of what you are seeing in the $_POST array when you dump it, and c) show what's in the database that you are expecting to match. — ADyson, Jan 20 '21 at 14:57
Also you might want to look more carefully at examples of the usage of bind_param .... https://www.php.net/manual/en/mysqli-stmt.bind-param.php and also examine exactly how to use num_rows from a statement, and what you need to do before it will return anything useful: https://www.php.net/manual/en/mysqli-stmt.num-rows.php . The official docs cover it ok, and you can also find other examples elsewhere too. or you could just make the query do the counting with a SELECT COUNT(email) query, and then read the result instead of faffing with num_rows. — ADyson, Jan 20 '21 at 15:06
`bind_param($email);` - there is no `type` identifier... if this is `mysqli` then that should be `bind_param('s',$email);` — Professor Abronsius, Jan 20 '21 at 15:11
A word about security. If you are not using `utf8_bin` or `BINARY` fields instead of simple `VARCHAR` with for example `utf8_general_ci`, someone with a similiar character will match. `tést@example.com` will match `test@example.com`. In username and password fields, always use binary compared fields. — Daniel W., Jan 20 '21 at 15:21

score 2 · Answer 1 · answered Jan 20 '21 at 15:12

2

Don't use real_escape_string(). When you use parameter binding there is no need to escape anything.

Parameter binding in mysqli is rather difficult as you have to remember the strange syntax of bind_param(). At least 2 arguments are needed, the first is a string of characters representing the types of values. You don't have that.

SQL doesn't need brackets around parameters. You can just do email=?.

When you want to check the existence of something using SQL, then you don't need to use $stmt->num_rows. You can use COUNT(1) in SQL, which should be simpler. In fact, forget about the existence of this function/property as it leads to many mistakes like yours and is generally not useful.

When we fix all the small problems the code should look something like this:

$email = $_POST['email'];

function emailExists(mysqli $conn, string $email):bool {
    $stmt = $conn->prepare("SELECT COUNT(1) FROM accountInfo WHERE email=? LIMIT 1");
    $stmt->bind_param('s', $email);
    $stmt->execute();
    return (bool) $stmt->get_result()->fetch_row[0];
}

echo emailExists($conn, $email);

answered Jan 20 '21 at 15:12

Dharman

30,962
25
85
135

@DanielW. I added it, but does it make any difference with `COUNT()`? – Dharman Jan 20 '21 at 15:15
Dharman It makes a difference how many rows mysql is looking at, because it will not stop after one row without limit. – Daniel W. Jan 20 '21 at 15:19
@DanielW. Is this what you mean? I can't see any difference. http://sqlfiddle.com/#!9/a6c585/183991 – Dharman Jan 20 '21 at 15:22
It's hard to tell from this little dataset. Try it on 100k rows without an index. It would be nice to see. Also see [this QA](https://stackoverflow.com/questions/455476/does-adding-limit-1-to-mysql-queries-make-them-faster-when-you-know-there-will#:~:text=If%20you%20limit%20your%20result,record%20that%20matches%20your%20query.). – Daniel W. Jan 20 '21 at 15:26
@DanielW. I tried it on a local machine but I can't see any difference in EXPLAIN output. Are you sure you haven't confused it with something else? I am a little surprised that a simple aggregate query needs a LIMIT, given that it will always return only 1 row. – Dharman Jan 20 '21 at 15:26
3

@DanielW. The `LIMIT` limts the count of rows the query produces as a result set, not how many rows the query processes. Since the `COUNT` will only produce one row for the result set, the `LIMIT` does not make any difference here. – slaakso Jan 20 '21 at 15:27
@DanielW. Should I ask another Stack Overflow question? I think this is tangential to this topic anyway. I would love to have a definite answer though. The link you shared is a little ambiguous as it doesn't talk about aggregate queries in particular. – Dharman Jan 20 '21 at 15:30
I checked it with 500k rows on a local docker mysql and I couldnt see any difference in the profiler. I think the query optimizer know's whats best and does a good job there. – Daniel W. Jan 20 '21 at 15:56

score 2 · Accepted Answer · answered Jan 20 '21 at 16:50

2

You just need to add $stmt->store_result();

function emailExists($conn, $email) {
    $stmt = $conn->prepare("SELECT 1 FROM accountInfo WHERE email=? LIMIT 1");
    $stmt->bind_param('s', $email);
    $stmt->execute();
    $stmt->store_result();
    return $stmt->num_rows;
}

Check this PHP code here

answered Jan 20 '21 at 16:50

Slava Rozhnev

9,510
6
23
39

Added `LiMIT 1` to query for speed up. – Slava Rozhnev Jan 20 '21 at 16:55

php check if email exists in mysql database

2 Answers2