0

I'm trying to do a simple select on a table with a simple where clause. Basically:

$query = "Select * from devices where device_id = 'abcdefghijklmnopqrstuvwxyz000000'";

When I try to execute the query, I get the 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 '' at line 128

If I shorten $id by 1 character, it works, or if I try to query a different field, it's fine. Obviously this is something to do with the datatype being stored in the table. The field device_id is char(32), so I understand if the query wouldn't take values greater in length than that, but $id has a length of 32.

Even copying the value from the device_id column in phpMyAdmin and pasting it as the value for $id in my php doesn't work. Something seems fishy... What's going on, and how can I fix it?

I've gotten some comments about changing the datatype, and stating that 'abcdefghijklmnopqrstuvwxyz000000' is too large to store in the table. However, it isn't.

Let me clarify my question:

The value stored in this column in the db is 'abcdefghijklmnopqrstuvwxyz000000'. The value in my query is the exact same value: 'abcdefghijklmnopqrstuvwxyz000000';

Both have a strlen of 32, and the datatype of that column is char(32).

Why will the table store the value 'abcdefghijklmnopqrstuvwxyz000000', but not let me query against that value? This doesn't seem correct.

JEJoll
  • 547
  • 1
  • 6
  • 20
  • 2
    `$id = 'abcdefghijklmnopqrstuvwxyz000005'` <<< you do have that semi-colon for it, *right?* – Funk Forty Niner Apr 19 '16 at 12:25
  • 2
    [Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Apr 19 '16 at 12:27
  • http://dev.mysql.com/doc/refman/5.7/en/char.html *"The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled."* – Funk Forty Niner Apr 19 '16 at 12:28
  • SQL syntax errors are caused by SQL. The first obvious debugging step is to look at the generated SQL. – Álvaro González Apr 19 '16 at 12:29
  • is the `$id` contains semi-colons or not? if yes, remove semi-colons from your query. – mitkosoft Apr 19 '16 at 12:33
  • @fred-ii- Yes, I do. I actually just put that there to show was was stored in $id. – JEJoll Apr 19 '16 at 12:50
  • Is this your exact code? – chris85 Apr 19 '16 at 12:52
  • This wasn't my exact code, and it wasn't a semi-colon. I think I had some single and double quotes mixed up somewhere, or frigged up my concatenation. Thanks for the help though. And I've found another strange behaviour surrounding this... See the link in my answer. – JEJoll Apr 19 '16 at 13:04

2 Answers2

0

The length of Id Column is small to have $id = 'abcdefghijklmnopqrstuvwxyz000005' in it, you just have to change the column ID data type, maybe varchar(64) will be enough.

Hachachin
  • 89
  • 8
  • use semicolon at the end of $id = 'abcdefghijklmnopqrstuvwxyz000005' next change the datatype in database – Pardeep Pathania Apr 19 '16 at 12:40
  • The value stored in the database is exactly the one I'm querying for, so it's not too small to store that value. – JEJoll Apr 19 '16 at 12:51
  • i think u need to use this special \`, `"Select * from devices where device_id = \`abcdefghijklmnopqrstuvwxyz000000\` "` – Hachachin Apr 19 '16 at 13:56
0

You may mistyped the value to your database?

Can you check this with same values in your database?

cmlonder
  • 2,370
  • 23
  • 35