SQLSTATE[42000]: Syntax error while syntax is OK

Question

I'm really puzzled by error that comes from my simple insert. I've checked the syntax many times by different checkers and searched for similar troubles but haven't found solution.

The Error looks like this:

'SQLSTATE[42000]: Syntax error or access violation: 1064 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 1' in

And my code is basically this:

$yhteys = new PDO('mysql:host=localhost;dbname=XXXX', 'YYYY', 'ZZZZ');
$kysely = $yhteys->prepare("INSERT INTO hakija (Kutsumanimi, Etunimet, Sukunimi, SyntymAika, Syntymapaikka, Sahkoposti, Puhelinnumero, Postiosoite, Postinumero, Postitoimipaikka, Maa, Suosittelija, IPos, Lahetysaika, Vapaa_sana, Sosme) VALUES ($nimi, $etunimet, $sukunimi, $saika, $spaikka, $email, $puhelin, $osoite, $postinro, $postitmp, $maa, $suosittelija, $IPos, $lahetysaika, $vapaasana, $sosme)");
$kysely->execute();

If I use this INSERT directly via phpMyAdmin, it works, but from php.. Can anyone help me out?

PHP: native (5.4) MySQL 5.6

Are you sure your variables are not empty? it looks like this, but I didn't reproduce it.. And also consider the two answers: You have to encapsulate strings with single quots, and overall you should use prepared statements (for security and convenience). — Stefan Woehrer, Nov 03 '15 at 07:57
Not sure, some of the variables might be empty since they not required. — tlaxin, Nov 03 '15 at 08:08
ok. use the prepared statement as RafH suggested. this way it should be ok if they are empty, plus it's a must for security reasons (http://stackoverflow.com/questions/732561/why-is-using-a-mysql-prepared-statement-more-secure-than-using-the-common-escape) — Stefan Woehrer, Nov 03 '15 at 08:37

score 1 · Accepted Answer · answered Nov 03 '15 at 07:52

1

You should use prepared statements. It will prevent sql injections and you wont have to deal with variables types

$yhteys = $dbh->prepare("INSERT INTO hakija (Kutsumanimi, Etunimet,...) VALUES (:kutsumanimi, :ktunimet, ...)");
$yhteys ->bindParam(':kutsumanimi', $kutsumanimi);
$yhteys ->bindParam(':ktunimet', $ktunimet);
...
$yhteys ->execute();

Have a look here : http://php.net/manual/en/pdo.prepared-statements.php

answered Nov 03 '15 at 07:52

RafH

4,504
2
23
23

Thank you, this was the solution. – tlaxin Nov 03 '15 at 21:46

score 0 · Answer 2 · answered Nov 03 '15 at 07:02

If values you are inserting are Strings you need to enclose it in quotes

$kysely = $yhteys->prepare("INSERT INTO hakija (Kutsumanimi, Etunimet, Sukunimi, SyntymAika, Syntymapaikka, Sahkoposti, Puhelinnumero, Postiosoite, Postinumero, Postitoimipaikka, Maa, Suosittelija, IPos, Lahetysaika, Vapaa_sana, Sosme) VALUES ('$nimi', '$etunimet', '$sukunimi', '$saika', '$spaikka', '$email', '$puhelin', '$osoite', '$postinro', '$postitmp', '$maa', '$suosittelija', '$IPos', '$lahetysaika', '$vapaasana', '$sosme')");

if values are integer you can skip quotes

SQLSTATE[42000]: Syntax error while syntax is OK

2 Answers2