Why is this giving me a syntax error? PHP PDO

Question

I'm usually good at this, and while I was finishing my project; I ran into an error. PDO was giving me a syntax error, that I couldn't wrap my head around. Maybe I'm just tired, but this is driving me crazy.

This is what I'm using for my PHP code:

$records = $conn->prepare('INSERT INTO users (username, email, password, limit) VALUES (:username, :email, :password, :theLimit)');
$records->bindParam(':username', $_POST['username']);
$records->bindParam(':password', password_hash($_POST['password'], PASSWORD_BCRYPT));
$records->bindParam(':email', $_POST['email']);
$records->bindParam(':theLimit', $_GET['amt']);

My database is as follows:

CREATE TABLE `users` (
  `id` int(11) UNSIGNED NOT NULL,
  `username` varchar(250) NOT NULL DEFAULT '',
  `email` varchar(250) NOT NULL DEFAULT '',
  `password` varchar(200) NOT NULL DEFAULT '',
  `limit` varchar(200) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The error I get is this:

Array ( [0] => 42000 [1] => 1064 [2] => 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 'limit) VALUES ('F', 'some_person@email.com', '$2y$10$gacC/a8zA3jCbPpKNTddtu4dBG' at line 1 )

And finally, the URL looks like this:

https://website.com/reg.php?serial=306019525D31&amt=10

I have even tried:

$records->bindParam(':theLimit', $_POST['email']);

thinking it might've been the URL. Same error.

I redid the database, same error.

Any ideas?

Thank you!

score 1 · Accepted Answer · answered Sep 23 '19 at 02:11

LIMIT is a reserved MySQL keyword, so you should escape it:

$sql = "INSERT INTO users (username, email, password, `limit`) ";
$sql .= "VALUES (:username, :email, :password, :theLimit)";
$records = $conn->prepare($sql);

Actually, you should avoid naming your objects using LIMIT or any other reserved keyword. Otherwise, you will always have to escape them as done above.

Why is this giving me a syntax error? PHP PDO

1 Answers1