Nick Nick - 1 month ago 9
MySQL Question

Inserting an ISO8601 DATETIME throws errors

So I've been trying this for a whole day now and I feel like there's a super simple solution and i'm just missing it. Attempting to use this query:

INSERT INTO codes (`user_id`, `type`, `code`, `expires`) VALUES ($id, $type, $code, $expires);


Here is the "expires" value:

2016-11-13T00:14:43.000Z


The error that comes out of querying:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':14:43.000Z)' at line 1


Here's the database structure:

CREATE TABLE IF NOT EXISTS codes (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`user_id` varchar(255) NOT NULL,
`type` varchar(255) NOT NULL,
`code` varchar(255) NOT NULL,
`expires` DATETIME
)ENGINE=InnoDB;


I do use a real_escape_string on the $expires variable if that matters.

Anyone have any suggestions?

Answer

Your datetime values like 2016-11-13T00:14:43.000Z are text strings. You need to enclose them in single quotes in the VALUES clauses of your insert statements.

In fact, you need single quotes around all your values, because they are all text strings.

 INSERT INTO codes (user_id, type, code, expires) VALUES ('$id', '$type', '$code', '$expires');

To understand this, let's say you have a $code value of 143-101. If you do

 INSERT INTO junk (code) VALUES ($code)

from your php program, the MySQL server will see

 INSERT INTO junk (code) VALUES (143-101)

which will yield the column value 42. That will make Douglas Adams happy, but nobody else.

You want your MySQL server to see

 INSERT INTO junk (code) VALUES ('143-101')

which you can do with

 INSERT INTO junk (code) VALUES ('$code')

And, if this application is accessible to the global internet, you must study up on SQL injection and make your SQL injection-proof, or some cybercriminal will pwn you and steal your assets.