Can not figure out why mysqli_num_rows is not working on my query

Question

function userexists($username){
    $query=$mysqli->query("SELECT username FROM accounts WHERE username='$username' AND exists=1");
    return mysqli_num_rows($query);
}

This function should be returning 1 or 0 but I can not figure out why the query is not being interpreted. What's wrong?

Edit: the error being returned by php is that $query is a boolean being fed into mysqli_num_rows, not an object.

Is `mysqli_query()` returning an error? Look at `mysqli_error` after you've sent your query. — , Jul 04 '13 at 00:03
`$mysqli->error:` 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 '=1' at line 1 — user1755043, Jul 04 '13 at 00:06
`exists` is a mySQL keyword. If you're using it as a column name then yes, you'll get a syntax error. Change the column name and your query and try again. — , Jul 04 '13 at 00:09
Quote `exists` in backticks like this: ``WHERE username='$username' AND `exists`=1"`` — jcsanyi, Jul 04 '13 at 00:10
Have you considered the SQL injection problem here? What happens if $username is `"'; DROP TABLE accounts; --"` ? — jcsanyi, Jul 04 '13 at 00:12
I just implemented `exists` so I will change the name to something else. @jcsanyi I simplified my question, but I do sanitize my input. — user1755043, Jul 04 '13 at 00:13

score 2 · Accepted Answer · 2013-07-04T00:20:02.817

2

In your query:

SELECT username FROM accounts WHERE username='$username' AND exists=1"

exists is a MySQL keyword. If you've used it as a column name then you'll get a syntax error. You could enclose the column name in back-ticks like this:

SELECT username FROM accounts WHERE username='$username' AND `exists`=1"

It's probably safer to change the column name and your query.

Be careful of possible SQL Injection attacks. At the very least you should escape any user-supplied data with mysqli_real_escape_string($username)

edited Jul 04 '13 at 00:20

answered Jul 04 '13 at 00:14

Please at least mention the SQL injection vulnerability in your answer as well. The solution doesn't have to be prepared statements - it can be as simple as `mysqli_real_escape_string()` – jcsanyi Jul 04 '13 at 00:16
@jcsanyi Done, although it's not covered by the question as the OP stated it, and he indicated that he does this anyway. – Jul 04 '13 at 00:21
Oops - I missed the second part of that comment from the OP. You're right - it looks like he's got it covered. It doesn't hurt to have it in the answer anyways though for anybody else that finds this question. Thanks. – jcsanyi Jul 04 '13 at 00:23

Can not figure out why mysqli_num_rows is not working on my query

1 Answers1