PHP / SQL, simple select where get the "value" as column

Question

$account = "proot"; 

        $sql_check_account = mysqli_query($connect, "SELECT username FROM proot_accounts WHERE username = $account");

    if(!$sql_check_account)
            die(mysqli_error($connect));

Return :

Unknown column 'proot' in 'where clause'

Same with :

 $sql_check_account = mysqli_query($connect, 'SELECT username FROM proot_accounts WHERE username = "'.$account.'"');

Or

 $sql_check_account = mysqli_query($connect, "SELECT username FROM proot_accounts WHERE username =".$account);

What can make the variable $account perform as a column ? I don't get the problem here...

Thanks !

Hi thanks for your time, same error with the backticks and second example won't work :( — Anyone_ph, Apr 23 '15 at 20:16
Backticks are for mysql column/table names. You are trying to compare a `VARCHAR` value. See my answer below. — Aaron Kent, Apr 23 '15 at 20:18
Maybe this thread would be useful, http://stackoverflow.com/questions/153598/unknown-column-in-where-clause. I've never seen this before, is there an error code with this error? — chris85, Apr 23 '15 at 20:26
Thanks chris, already read this topic before to post this one :( — Anyone_ph, Apr 23 '15 at 20:32
What about `"SELECT username FROM proot_accounts WHERE username = (select '$account';)"` — chris85, Apr 23 '15 at 20:42

score 3 · Accepted Answer · answered Apr 23 '15 at 20:16

3

You need to put quotes around it.

mysqli_query($connect, "SELECT username FROM proot_accounts WHERE username = '$account'");

You should also explore prepared SQL statements in PHP. They will save a lot of these formatting headaches.

answered Apr 23 '15 at 20:16

Aaron Kent

609
1
5
11

1

how is this different than the OPs 2nd example? – Sean Apr 23 '15 at 20:18
This is different because it generates a SQL statement with quotation marks in it. You want `username = "value"` and not `username = value` – Aaron Kent Apr 23 '15 at 20:20
Are you still getting `Unknown column 'proot' in 'where clause'`? Echo the query you are generating and share the result. – Aaron Kent Apr 23 '15 at 20:21
there : Unknown column 'proot' in 'where clause' – Anyone_ph Apr 23 '15 at 20:22
this is does not create a statement different than the OPs `'SELECT ... WHERE username = "'.$account.'"'` – Sean Apr 23 '15 at 20:22
It looks like it does make the SQL string have single quotes around `$account` instead of double quotes. – Don't Panic Apr 23 '15 at 20:24
@Don'tPanic although MySQL runs the same with single or double quotes – Sean Apr 23 '15 at 20:25
Single or double quotes shouldn't matter. But if MySQL is thinking 'proot' is a column that means the quotes must not be going through. @Anyone_ph, can you alter your program to print the query instead of running it? – Aaron Kent Apr 23 '15 at 20:27
@Sean, Doesn't that depend on the server mode? http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_ansi_quotes – Don't Panic Apr 23 '15 at 20:27
Can you run this? `echo 'SELECT username FROM proot_accounts WHERE username = "'.$account.'"'` – Aaron Kent Apr 23 '15 at 20:31
php register.php (CLI)-> SELECT username FROM proot_accounts WHERE username = "proot" With my on my example SELECT username FROM proot_accounts WHERE username = proot – Anyone_ph Apr 23 '15 at 20:36
So you see how in the first one you get the quotes and the second you don't? Try sending the quoted one to MySQL. – Aaron Kent Apr 23 '15 at 20:39
Yep I rewrite the script with your quote example, works thanks for your time ! – Anyone_ph Apr 23 '15 at 20:51

score 2 · Answer 2 · answered Apr 23 '15 at 20:48

I think this is a quoting issue. This:

"SELECT username FROM proot_accounts WHERE username = $account"

will get you this string:

SELECT username FROM proot_accounts WHERE username = proot

In this case MySQL is thinking proot is a column name, because proot is not in quotes at all.

This:

'SELECT username FROM proot_accounts WHERE username = "'.$account.'"'

will get you this string:

SELECT username FROM proot_accounts WHERE username = "proot"

In this case, MySQL may still think "proot" is a column name, depending on the SQL mode. Since you are still getting the same error when you use this code, it looks like your database is set to ANSI_QUOTES mode. In this mode, text inside quotation marks will be interpreted as an column identifier, not a literal value.

Using this:

"SELECT username FROM proot_accounts WHERE username = '$account'"

will get you this string:

SELECT username FROM proot_accounts WHERE username = 'proot'

Using ' instead of " should ensure that MySQL will treat proot like a literal value instead of a column identifier regardless of the SQL mode.

PHP / SQL, simple select where get the "value" as column

2 Answers2

Linked