Connection failed: SQLSTATE[42000]: Syntax error or access violation

Question

Do you have any idea whats wrong with my code? I can't really figure it out.

Connection failed: SQLSTATE[42000]: Syntax error or access violation: 1064 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 'VARCHAR(50) NOT NULL, url TEXT(65535) NOT NULL, ip VARCHAR(150) NOT NULL)' at line 3

<?php
$host = "localhost";
$dbname = "nope";
$username = "nope";
$password = "nope";

if(isset($_GET["s"])){
    try{
        $pdo = new PDO("mysql:host=".$host.";dbname=".$dbname,$username,$password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $table = "links";
        $sql ="CREATE TABLE IF NOT EXISTS $table(
        ID INT(11) AUTO_INCREMENT PRIMARY KEY,
        key VARCHAR(50) NOT NULL, 
        url TEXT(65535) NOT NULL,
        ip VARCHAR(150) NOT NULL)";
        $pdo->exec($sql);
    } catch (PDOException $e) {
        echo 'Connection failed: ' . $e->getMessage();
    }


    /*$statement = $pdo->prepare("select url from $table where key = :key");
    $statement->execute(array(':key' => $_GET["s"]));
    $result = $statement->fetch();
    echo $result;*/
}
else{
    echo "error";
}
?>

`key` is a reserved word (surprise ...), so you can not use it unquoted as a column name. https://dev.mysql.com/doc/refman/5.7/en/keywords.html — CBroe, Oct 18 '17 at 08:16
If any of the provided solution work for you, then please accept it. — mega6382, Oct 18 '17 at 17:56

score 1 · Answer 1 · answered Oct 18 '17 at 08:16

Your column name key is a reserved keyword in mysql

The solution is to quote the column name in backticks

    `key` VARCHAR(50) NOT NULL,

or just change the name of the column to whatever you like which is not a reserved word.

     my_key VARCHAR(50) NOT NULL,

So your statement should be

    $sql ="CREATE TABLE IF NOT EXISTS $table(
    ID INT(11) AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(50) NOT NULL, 
    url TEXT(65535) NOT NULL,
    ip VARCHAR(150) NOT NULL)";

or

    $sql ="CREATE TABLE IF NOT EXISTS $table(
    ID INT(11) AUTO_INCREMENT PRIMARY KEY,
    table_key VARCHAR(50) NOT NULL, 
    url TEXT(65535) NOT NULL,
    ip VARCHAR(150) NOT NULL)";

score 1 · Answer 2 · answered Oct 18 '17 at 08:21

Try the following query instead:

CREATE TABLE IF NOT EXISTS $table(
        `ID` INT(11) AUTO_INCREMENT PRIMARY KEY,
        `key` VARCHAR(50) NOT NULL, 
        `url` TEXT NOT NULL,
        `ip` VARCHAR(150) NOT NULL)

Key is a Reserved Word in MariaDB. And thus needs to be enclosed in ``. And there is no need to define length for type TEXT

Connection failed: SQLSTATE[42000]: Syntax error or access violation

2 Answers2