MySQL Treats Update Parameters a columns

Question

I have a PHP Script with the following code:

$sql = "UPDATE tbl_p2e_place

            SET 
            `listingName` = $listing_name, `listingDescription` = $listing_description, 
            `listingSpecialOffers` = $listing_special_offers, `listingFoodTypes` = $listing_food_types,
            `listingAddress` = $listing_address, `CityID` = $listing_city, 
            `listingGPSSouth` = $gps_coordinate_south, `listingGPSEast` = $gps_coordinate_east,
            `listingLatitude` = $map_latitude, `listingLongitude` = $map_longitude, 
            `listingTime` = $listing_timing, `listingNumber` = $booking_number, 
            `listingEmail` = $email_address, `listingWebsite` = $lisiting_website, 
            `listingTwitter` = $lisiting_twitter, `listingFacebook` = $lisiting_facebook, 
            `listingIsFeatured` = $is_featured, `listingDisplay` = $display

            WHERE listingID = $listing_id"; 


    if(!mysql_query($sql))
    {
        die('Update Error: ' . mysql_error());
    }

    mysql_close($con);

The problem arises when I want to run the query I get the following error:

Update Error: Unknown column 'Shop' in 'field list'

Shop was entered as a input from the form.

What could cause this and how can I stop it?

Regards

-1 for sql-injectable code, see: http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain — Johan, Jun 25 '11 at 14:52

score 1 · Answer 1 · answered Jun 25 '11 at 13:14

1

You need to put quotes around the variables in your query

Another important issue: you say this information comes from a form. Look up SQL Injection and query sanitation!

answered Jun 25 '11 at 13:14

Explosion Pills

188,624
52
326
405

I do use mysql_real_escape_string() on each value that comes through a POST and a GET. – RaVen Jun 25 '11 at 13:18
There is no point, since I could just write an sql injection without quotes and it will work, because you're missing quotes. – Alex Jun 25 '11 at 13:26

score 1 · Answer 2 · answered Jun 25 '11 at 13:16

Use PDO, parametrized queries, or some kind of ADO abstraction layer.

What you are doing is prone to holes like SQL Injection, and to the errors which you just got. Of course, the easiest way to solve this exact problem, is to put apostrophes around the values, because, logically, the SQL string now looks like this:

....
`listingDisplay` = some value without quotes

and of course, MySQL Server will tell you that there is no column named 'some', and that you are missing commas, when in fact you are missing apostrophes around the value.

`listingIsFeatured` = '$is_featured', `listingDisplay` = '$display'

score -1 · Accepted Answer · answered Jun 25 '11 at 13:16

-1

use '' for the variables like

"UPDATE tbl_p2e_place

        SET 
        `listingName` = '".$listing_name."'................and so on

answered Jun 25 '11 at 13:16

WordsWorth

872
1
11
23

MySQL Treats Update Parameters a columns

3 Answers3