How to insert into database (syntax error)

Question

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);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("SELECT * FROM Notes");
    $stmt->execute();

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

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

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

}

**WARNING**: When using PDO you should be using [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) with placeholder values and supply any user data as separate arguments. In this code you have potentially severe [SQL injection bugs](http://bobby-tables.com/). Never use string interpolation or concatenation and instead use [prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) and never put `$_POST` or `$_GET` data directly in your query. Refer to [PHP The Right Way](http://www.phptherightway.com/) for guidance with this and other problems. — tadman, Mar 01 '17 at 18:23

score -1 · Answer 1 · answered Mar 01 '17 at 18:01

-1

Your table columns should not be quoted.

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

answered Mar 01 '17 at 18:01

TopCheese

220
1
8

Thanks, now it works as intended. – sOnt Mar 01 '17 at 18:18

score -1 · Accepted Answer · answered Mar 01 '17 at 18:02

-1

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.

answered Mar 01 '17 at 18:02

Mureinik

297,002
52
306
350

1

With your rep, you really should be closing Dups like this not answering them – RiggsFolly Mar 01 '17 at 18:06
Thanks, works well! – sOnt Mar 01 '17 at 18:17
This fix just weaponized that SQL injection hole. Mumbling about prepared statements isn't enough. – tadman Mar 01 '17 at 18:23

How to insert into database (syntax error)

2 Answers2