-3

Ignoring the fact that concatenating user input into SQL strings is possibly the worst thing you can do in terms of avoiding SQL injection (this is not for a production site), what is wrong with the following SQL?

"SELECT '_id', 'email', 'password', 'salt', 'banned', 'ban_reason' FROM 'tbl_users' WHERE 'email'='" . $email . "' LIMIT 1";

I'm getting the error message:

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 '\'_id\', \'email\', \'password\', \'salt\', \'banned\', \'ban_reason\' FROM \'tb' at line 1

I'm using mysqli_real_escape_string() on it right before running the query, hence the backslashes.

I also tried enclosing everything in backticks, but the error changed then to unknown column.

halfer
  • 19,824
  • 17
  • 99
  • 186
Adam
  • 311
  • 1
  • 3
  • 15
  • Remove all single quotes – Phantom Aug 27 '14 at 15:19
  • 5
    To escape field names in MySQL use the backtick, not the single quote. – Matteo Tassinari Aug 27 '14 at 15:20
  • 3
    Don't insert variables in to SQL by concatenating strings, use prepared statements with placeholders. – Quentin Aug 27 '14 at 15:21
  • Your other major problem seems to be the application of `mysqli_real_escape_string()` on the *whole* SQL query. That's not what it is or. – mario Aug 27 '14 at 15:52
  • (Don't worry too much about downvotes - they happen. However it's not ideal to put commentary about votes in questions - questions are intended for a wide future audience, and they don't want to read about voting!). – halfer Aug 27 '14 at 16:29
  • @Mario's right - run your parameters through `mysqli_real_escape_string()`, not the whole query. – halfer Aug 27 '14 at 16:32

3 Answers3

2

Exactly where it says. "near '\'_id\'"

You use `backticks` to surround column and table names. Not 'single quotes'.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
2

You should be escaping your variables you're inserting, not your query, and using backticks around field names

$sql = "SELECT `_id`, `email`, `password`, `salt`, `banned`, `ban_reason` FROM `tbl_users` WHERE `email`='" . mysqli_real_escape_string($email) . "' LIMIT 1";
Mez
  • 24,430
  • 14
  • 71
  • 93
0
"SELECT `_id`, `email`, `password`, `salt`, `banned`, `ban_reason` FROM tbl_users WHERE `email`='" . $email . "' LIMIT 1"

You should use ` instead of ' when selecting fields.

mariobgr
  • 2,143
  • 2
  • 16
  • 31
  • Tried this, but now the error says "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 '\'user@example.com\' LIMIT 1' at line 1" – Adam Aug 27 '14 at 15:24
  • Did you also put the backticks in the **WHERE 'email' = ...** ? – mariobgr Aug 27 '14 at 15:26
  • As your example I put them around the column name, but not the criteria. I've tried plenty of combinations but the same error message :/ – Adam Aug 27 '14 at 15:28
  • @Adam Notice the backslashes. Are you `mysql_real_escape_string`ing your entire query??? – Niet the Dark Absol Aug 27 '14 at 15:56