SQL syntax error edit post

Question

getting :

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 's Creed III', description='The plot is set in a fictional history of real ' at line 2

when trying to edit posts on a database.

heres my display and edit php:

        $result = mysql_query("SELECT * FROM gallery");
        while ($row = mysql_fetch_array( $result )){
                // while looping thru each record…
            // output each field anyway you like

            $title =  $row['title'] ;
            $description = $row['description'];
            $year = $row['year'];
            $rating = $row['rating'];
            $genre = $row['genre'];
            $filename =  $row['filename'];
            $imageid = $row['imageid'];

            include '../modules/edit_display.html';
        }



        // STEP 2: IF Update button is pressed , THEN UPDATE DB with the changes posted
        if(isset($_POST['submit'])){ 
            $thisTitle = $_POST['title'];
            $thisDescription = $_POST['description'];
            $thisYear = $POST['year'];
            $thisRating = $POST['rating'];
            $thisGenre = $POST['genre'];
            $thisNewFilename =  basename($_FILES['file']['name']);
            $thisOneToEdit = $_POST['imageid'];
            $thisfilename = $_POST['filename'];



            if ($thisNewFilename == ""){
                $thisNewFilename = $thisfilename ;
            } else {
                uploadImage();
                createThumb($thisNewFilename , 120, "../uploads/thumbs120/");
            }



            $sql = "UPDATE gallery SET
                    title='$thisTitle',
                    description='$thisDescription',
                    year='$thisYear',
                    rating='$thisRating',
                    genre='$thisGenre',
                    filename='$thisNewFilename'

                 WHERE 

                    imageid= $thisOneToEdit";

                $result = mysql_query($sql) or die (mysql_error());



        }

What is the `title` that you try to insert? This issue may about `single quote` in title. — Bora, Sep 11 '13 at 08:12

score 3 · Answer 1 · edited May 23 '17 at 10:25

You're suffering from an imminent dose of SQL Injection due to using a dangerous user input model.

When you type "Assassin's Creed III" in the title field, that gets placed in single quotes in the UPDATE statement in your code (via the $_POST['title'] variable):

'Assassin's Creed III'

The problem there is that MySQL sees it as 'Assassin', followed by s Creed III'. It doesn't know what to do with the latter.

Of course, this becomes a HUGE problem if someone types in valid SQL at that point, but not what you expected. Have a look at How can I prevent SQL injection in PHP? or any of several other advices on avoiding SQL Injection.

score 2 · Answer 2 · edited Sep 11 '13 at 08:25

2

You have syntax error here. Use $_POST instead of $POST.

Replace

 $thisYear = $POST['year'];
 $thisRating = $POST['rating'];
 $thisGenre = $POST['genre'];

With

 $thisYear = $_POST['year'];
 $thisRating = $_POST['rating'];
 $thisGenre = $_POST['genre'];

edited Sep 11 '13 at 08:25

Bora

10,529
5
43
73

answered Sep 11 '13 at 08:14

Devang Rathod

6,650
2
23
32

score 2 · Answer 3 · answered Sep 11 '13 at 08:15

2

i have seen you are adding ' into database so you need to escape it using addslashes()

addslashes($thisTitle)

answered Sep 11 '13 at 08:15

Dipesh Parmar

27,090
8
61
90

I thing it must be typo so i am adding this into comment....replace `$POST` to `$_POST` – Dipesh Parmar Sep 11 '13 at 08:17
I think this issue happened around single quote +1 – Bora Sep 11 '13 at 08:19

score 1 · Answer 4 · answered Sep 11 '13 at 08:15

1

you need to escape your input like

$thisDescription = mysql_real_escape_string($_POST['description']);

do this for all input that contains quotation marks etc..

NOTE: mysql will soon be gone so its advised to write new code using mysqli instead

answered Sep 11 '13 at 08:15

Liam Allan

1,115
1
8
13

score 1 · Answer 5 · answered Sep 11 '13 at 08:18

You have alot of issues in your script.

You're trying to add ' character to database, you need to escape it properly with addslashes.
You're vulnerable to SQL Injection. Escape it properly with mysql_real_escape_string, or even better, use PDO.
Third, it is $_POST, not $POST. You're using it wrong in some areas.
Add quotes to $thisOneToEdit in query.

The error is causing because you're trying to add Assasin's Creed III string to database. The single quote breaks your query and creates a syntax error.

score 0 · Answer 6 · answered Sep 11 '13 at 08:16

0

Do a addslashes() on the values that might contain single or double quotes like below before using them in query

$thisTitle = addslashes($_POST['title']);

answered Sep 11 '13 at 08:16

rakeshjain

1,791
11
11

SQL syntax error edit post

6 Answers6