SQL error syntax on query with variables

Question

I am trying to create an import script that looks as below:

foreach ( $app_files as $file ) {
            if ( $row["old_id_import"] == $file['idx'] ) {

                $ids   = $row["id"];
                $files = $file['file'];
                $date  = date( 'Y-m-d H:i:s' );
                $sql2  = "INSERT INTO palace_files ('id', 'module', 'item_id', 'name', 'status', 'date', 'modified') VALUES (NULL, 5, "'.$ids.'", "'.$files.'",1 ,"'.$date.'" , "'.$date.'")";
                if ($conn->query($sql2) === TRUE) {
                    echo "New record imported successfully";
                } else {
                    echo "Error: " . $sql2 . "<br>" . $conn->error;
                }
            }
        }

But it seems that i have syntax error, dont know where. Maybe i am tired.

I have found similar threads but none worked.

example INSERT INTO palace_files ('id', 'module', 'item_id', 'name', 'status', 'date', 'modified') VALUES (NULL, 5, 1357, 143099797075.830,1 ,2017-07-26 22:41:10 , 2017-07-26 22:41:10 ) — JamesDooh, Jul 26 '17 at 22:41
Backticks (`\``), not quotes (`'`) for table/column names.... quotes are used for string literals — Mark Baker, Jul 26 '17 at 22:44
Now learn to use prepared statements with bind variables, and enter the 21st century — Mark Baker, Jul 26 '17 at 22:45
You're using the wrong type of identifier qualifiers https://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html and is the main reason why your code failed. Then your quoting's inverted. — Funk Forty Niner, Jul 26 '17 at 23:21

score 3 · Accepted Answer · edited Jul 27 '17 at 00:05

3

You query has some mistakes/could be improved.

status and date are keywords in sql. In order for them to be future proof (in case they become reserved, or new ones are added), or you want to always safely use any column name you want and not worry about if they could cause errors, better just wrap them in backticks ``
you should not wrap the columns in ' '
you are not wrapping them with ' ' in your values

Your query should read as follows:

$sql2 = "INSERT INTO palace_files (id, module, item_id, name, status, date, modified) 
         VALUES (NULL, 5, '{$ids}', '{$files}',1 ,'{$date}' , '{$date}')";

edited Jul 27 '17 at 00:05

Funk Forty Niner

74,450
15
68
141

answered Jul 26 '17 at 22:44

Yolo

1,569
1
11
16

Why is the code snippet in my answer not showing as code?? And why the down vote? – Yolo Jul 26 '17 at 22:46
I have use date,status, name as entity name in my database and that has no problem. so that is not the reason for his error – Lekens Jul 26 '17 at 22:47
But it could happen and it is bad practice not to do so, so i am advising to wrap them in case anyone else comes across this post... 2. and 3. should solve your problem – Yolo Jul 26 '17 at 22:48
if you wrap in ' ' without the { } or . . sign php will see it a just string @'$ids', '$files',1 ,'$date' , '$date') it has to be in '{$ids}' – Lekens Jul 26 '17 at 22:54
1

You don't _need_ to do that. See this [post](https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php) for example. But i have added it in my answer. – Yolo Jul 26 '17 at 23:00
*"module, status and date are reserved words in sql"* - Where did you get that information from? @Yolo – Funk Forty Niner Jul 26 '17 at 23:20
@Fred-ii- See [reference](https://dev.mysql.com/doc/refman/5.5/en/keywords.html). Although module is not listed, my IDE was marking it as such. I changed that in my answer. – Yolo Jul 26 '17 at 23:22
@Yolo I know it quite well and none of those are listed in there or in any RDBMS. – Funk Forty Niner Jul 26 '17 at 23:24
@Yolo `DATE` and `STATUS` are keywords, not reserved. These are two different animals altogether. Notice there are no `(R)`'s next to them. So there's no need to tick them. – Funk Forty Niner Jul 26 '17 at 23:25
@Fred-ii- I have improved my answer based on your last comment. – Yolo Jul 26 '17 at 23:45
Thank you @Yolo that's more true to the question. – Funk Forty Niner Jul 27 '17 at 01:11

Omi · Answer 2 · 2017-07-26T23:33:20.513

1

Your are doing wrong concatination. See somehere you missed . operator & quote ". Inside "" you can use php variable it will interpret its value.

 $sql2  = "INSERT INTO palace_files ('id', 'module', 'item_id', 'name', 'status', 'date', 'modified') VALUES (NULL, 5, "'.$ids.'", "'.$files.'",1 ,"'.$date.'" , "'.$date.'")";

Try this:

foreach ($app_files as $file) {
 if ($row["old_id_import"] == $file['idx']) {

    $ids = $row["id"];
    $files = $file['file'];
    $date = date('Y-m-d H:i:s');
    $sql2 = "INSERT INTO palace_files (id, module, item_id, name, status, date, modified) VALUES (NULL, 5, '$ids', '$files',1 ,'$date' , '$date')";
    if ($conn->query($sql2) === TRUE) {
        echo "New record imported successfully";
    } else {
        echo "Error: " . $sql2 . "<br>" . $conn->error;
    }
 }
}

edited Jul 26 '17 at 23:33

answered Jul 26 '17 at 22:40

Omi

3,954
5
21
41

This answer is completely incorrect and it needs to be fixed. – Funk Forty Niner Jul 26 '17 at 23:22
@Fred-ii- thanks i have fixed now – Omi Jul 26 '17 at 23:24
`('id', 'module', 'item_id', 'name', 'status', 'date', 'modified')` there shouldn't be quotes for these `'` - they either need to be removed, or using what are called ticks `\`` which resemble quotes, but are different. – Funk Forty Niner Jul 26 '17 at 23:30

Lekens · Answer 3 · 2017-07-26T22:50:24.933

foreach ($app_files as $file) {
 if ($row["old_id_import"] === $file['idx']) {

    $ids = $row["id"];
    $files = $file['file'];
    $date = date('Y-m-d H:i:s');
    $sql2 = "INSERT INTO palace_files (module, item_id, name, status, date, modified) VALUES (5, '{$ids}', '{$files}',1 ,'{$date}' , '{$date}')";
    if ($conn->query($sql2) === TRUE) {
        echo "New record imported successfully";
    } else {
        echo "Error: " . $sql2 . "<br>" . $conn->error;
    }
 }
}

Instead of inserting ID why not set ID as auto-increment and allow it take care of itself

SQL error syntax on query with variables

3 Answers3