sOnt sOnt -4 years ago 68
MySQL Question

How to insert into database (syntax error)

Helo. I am having trouble executing the following INSERT INTO query. The SELECT works fine. Where is the mistake?

The php gives back this error message:

Error: 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 ''id', 'reg_date', 'szoveg') VALUES (NULL, CURDATE(), 'x')' at line 1

$servername = "localhost";
$username = "root";
$password = "pass";
$dbname = "db";

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$stmt = $conn->prepare("SELECT * FROM Notes");

$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

$query = "INSERT INTO Notes ('id', 'reg_date', 'szoveg') VALUES (NULL, CURDATE(), '".$_POST["szoveg"]."')";

$stmt = $conn->prepare($query);


Answer Source

Single quotes (') denote sting literals in SQL. TO refer to identifiers, such as columns names, you should use bare words:

$query = "INSERT INTO Notes (id, reg_date, szoveg) VALUES (NULL, CURDATE(), '".$_POST["szoveg"]."')";

Mandatory comment:
Using string concatination to generate SQL statements leaves your code vulnerable to SQL injection attacks. You should consider using a prepareds statement instead.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download