PHP and Mysql error "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version"

Question

I am trying to code a little log thing for my Home automations script but then I got this error:

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 '' at line 2

My SQL is:

INSERT INTO
    logsa (timeb, msg, actionb)
VALUES 
    ('12-05-2018 02:29:38pm',
    'Succesfully send a trigger to https://maker.ifttt.com/trigger/test/with/key/xxxxxxxxxxxxxxxxxxxxxxxx With name test',
    'https://maker.ifttt.com/trigger/test/with/key/xxxxxxxxxxxxxxxxxxxxxxxx'

and my code is:

    $logmsg = ("Succesfully send a trigger to " . $row["actiona"] . " With name " . $row["namea"]);

    date_default_timezone_set("Europe/Stockholm");

    $date = date("d-m-Y");
    $time = date("h:i:sa");
    $fulldate = ($date . " " . $time);

    $actiona = $row["actiona"];
    $sql = "INSERT INTO logsa (timeb, msg, actionb)
    VALUES ('$fulldate', '$logmsg', '$actiona'";

    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }

You don't close your parenthesis in your query (could just be a copy paste error to the question here) — James, May 12 '18 at 12:54
Is this `'12-05-2018 02:29:38pm'` a valid datetime in mariaDB? — Jeff, May 12 '18 at 12:55
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, May 12 '18 at 13:02
What data type have you defined `timeb` as in your database schema? — RiggsFolly, May 12 '18 at 13:18

score 0 · Accepted Answer · edited May 12 '18 at 14:32

Change the timeb data value $fulldate to use a valid date time format like this

$fulldate = date('Y-m-d H:i:s');

MySQL and mariaDB expect DATETIME columns to be stored in a very specific format and yours was invalid.

Also you should be using prepared and parametrised queries [link] to avoid SQL Injection attacks like this

$logmsg = ("Succesfully send a trigger to " . 
            $row["actiona"] . 
            " With name " . 
            $row["namea"]);

date_default_timezone_set("Europe/Stockholm");

$fulldate = date('Y-m-d H:i:s');
$actiona = $row["actiona"];

$sql = "INSERT INTO logsa (timeb, msg, actionb) VALUES (?,?,?)";
$stmt = $conn->prepare($sql);

$stmt->bind_param('sss', $fulldate, $logmsg, $actiona );
$result = $stmt->execute();
if ($result) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

No problem RiggsFolly. But thanks guys for all your help. – SimonLovskog May 12 '18 at 14:26 — SimonLovskog, May 12 '18 at 14:26

PHP and Mysql error "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version"

1 Answers1