simple mysql select error limit

Question

This is the error message and my code. I just don't see the error.

Description:

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 'key='cbd1f3bb822e8617b624301774287490d3fcd97e' LIMIT 1' at line 1

Query:

SELECT * 
FROM wp_wpsc_api_keys 
WHERE name='MichelleAllen17' 
AND key='cbd1f3bb822e8617b624301774287490d3fcd97e' 
LIMIT 1

Any ideas of what can be the issue in my sql are welcome

One thing that would help this question would be the output of `describe wp_wpsc_api_keys`, but as it happens the smart guys know the answer without it. — pjmorse, Jan 12 '13 at 00:55

score 8 · Answer 1 · answered Jan 12 '13 at 00:54

8

KEY is a reserved keyword, it must be escaped with backtick.

SELECT  * 
FROM    wp_wpsc_api_keys 
WHERE   name = 'MichelleAllen17' AND 
        `key` = 'cbd1f3bb822e8617b624301774287490d3fcd97e' 
LIMIT   1

MySQL Reserved Keywords

answered Jan 12 '13 at 00:54

John Woo

258,903
69
498
492

1

@misulicus - You're better off renaming the field to `api_key` and avoiding using reserved words: `ALTER TABLE wp_wpsc_api_keys CHANGE COLUMN key api_key varchar(255);` (put key in backticks) – Anthony Jan 12 '13 at 01:01
It's not quite accurate to say that "it MUST be escaped with backtick". Using backticks is NOT required in this case. A simple, convenient way of avoiding MySQL seeing that column name as a reserved word is to qualify the column_name with the table_name or with a table alias. e.g. `FROM mytable t WHERE t.key = ` ... no backticks required. – spencer7593 Jan 12 '13 at 01:06
@spencer7593 `Using backticks is NOT required in this case.` -- so you're saying that at all case, we should be using column aliases just to avoid using backticks? we have different views. for me, in this case, i'll be using backticks than supplying an alias to that table. – John Woo Jan 12 '13 at 03:27
@JW... what I said was that your statement that the backticks are REQUIRED is INNACURATE. Yes, the use of backticks is ONE possible solution but no, that is not the only solution. (If sql_mode is set to ANSI, for example, then double quotes can be used to enclose identifiers. I was pointing out that an identifier can ALSO be qualified with a table name or alias. So, NO, I am not saying that in "`all case`" (or in any case) that you (or anyone else) "`should be using column aliases to avoid backticks`". There are other, much better, reasons for qualifying column references with table aliases. – spencer7593 Jan 12 '13 at 22:12
@JW said: "`i'll be using backticks [rather] than [qualifying the column reference with] an alias `". Well, that pretty much stands to reason, JW, given your requirement that the column name "`MUST`" be enclosed in backticks. Given your statement that backticks are REQUIRED, it's pretty much a given that your usage of backticks would satisfy that requirement. – spencer7593 Jan 14 '13 at 05:29

score 0 · Answer 2 · answered Jan 12 '13 at 00:55

0

put backtiks around the field names

...where `name`=... `key`

answered Jan 12 '13 at 00:55

Itay Moav -Malimovka

52,579
61
190
278

1

Only required when the word is reserved. so ugly otherwise. – Anthony Jan 12 '13 at 00:56
ugly, yes, but this is his problem (key, or name are reserved words) and if I put backtiks, I rather be consistant in that query – Itay Moav -Malimovka Jan 12 '13 at 00:57
qualifying the column names with a table alias prevents MySQL from seeing the column name as reserved word... `WHERE t.name= AND t.key= `. I concur with Anthony: unnecessary backticks are annoyingly ugly. – spencer7593 Jan 12 '13 at 01:03
for sure, that's why I suggested in the other comment to rename the column. – Anthony Jan 12 '13 at 01:03

spencer7593 · Answer 3 · 2013-01-12T01:11:18.660

As an alternative to backticks, another "best practice" pattern is to QUALIFY all column names with the table_name, or a convenient table alias, e.g.

SELECT t.*
  FROM wp_wpsc_api_keys t 
 WHERE t.name='MichelleAllen17'
   AND t.key='cbd1f3bb822e8617b624301774287490d3fcd97e'
 LIMIT 1

This prevents MySQL from seeing the column name "key" as a reserved word.

Let's be clear: the problem in your query isn't a lack of backticks... the problem is that MySQL is seeing a token in your query text (in this case "key") as a reserved word, rather than as the name of the column. The solution is to prevent MySQL from seeing that token as a keyword. Using backticks is one way to accomplish that, but they aren't required.

Using backticks is entirely valid, and can be done along with qualifying the column names. The backicks are required when the column name contains spaces or special characters. Here is the same query, with the table and column names enclosed in backticks:

SELECT t.*
  FROM `wp_wpsc_api_keys` t 
 WHERE t.`name`='MichelleAllen17'
   AND t.`key`='cbd1f3bb822e8617b624301774287490d3fcd97e'
 LIMIT 1

I just happen to find it annoying to have to look at, or type, backticks that are unnecessary. It is MUCH MORE useful use of keystrokes (for me) to have the column names qualified ("t."), even if that isn't required, just because I am SO used to seeing column names qualified whenever there is more than one table in a query (which happens a LOT for a lot of really useful queries.)

`the problem in your query isn't a lack of backticks` -- backticks were introduced to escaped reserved keywords that were used in the query. different people views differently. — John Woo, Jan 12 '13 at 03:31
JW said "`it MUST be escaped in backticks`". But, no, backticks are NOT required; yes, they are one possible solution to the problem... the problem is that MySQL is seeing a token in the SQL text as a reserved word. So, contrary to your statement (and apparent belief) that backticks are REQUIRED, they are in fact, NOT required. — spencer7593, Jan 12 '13 at 21:49

simple mysql select error limit

3 Answers3