Syntax error at update query where clause mysql

Question

if(isset($_POST['Update'])) {
$placename = $_POST['placename'];
$description = trim(addslashes($_POST['description']));
$hotel = $_POST['hotel'];
$transport = $_POST['transport'];
$map = $_POST['map'];
$sqlp = "UPDATE places SET placename = $placename, description = $description, hotel = $hotel, transport = $transport, map = $map WHERE place_id = ". $sPlace['place_id'];
connection();
if(mysql_query($sqlp)) {
    echo "Successfully Updated";
} else {
    echo mysql_error();
}
}

Error Message is following-

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 '

map = map WHERE place_id = 54' at line 1

**Your code is vulnerable to SQL injection.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain). — eggyal, Aug 26 '12 at 01:42
Also as stated in [the introduction](http://www.php.net/manual/en/intro.mysql.php) to the PHP manual chapter on the `mysql_*` functions: *This extension is not recommended for writing new code. Instead, either the [mysqli](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extension should be used. See also the [MySQL API Overview](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API.* — eggyal, Aug 26 '12 at 01:42
Please show the content of `$sqlp` prior to the query being executed: e.g. include `echo $sqlp;` then paste here what is output. — eggyal, Aug 26 '12 at 01:43
`$sPlace['place_id']` apparently ends in a `'`. Anyway, do what @eggyal said :) — Ry-, Aug 26 '12 at 01:46
UPDATE places SET `placename` = SAMRAT, `description` = Rajoooooooooooooooni, `hotel` = NAI, `transport` = BUS, CNG, `map` = map WHERE place_id = 54 @eggyal — , Aug 26 '12 at 01:48

Mihai Iorga · Answer 1 · 2012-08-26T19:58:33.650

You error in that code is that you don't add quotes around variables, it should be like this:

$query = "UPDATE `table` SET `name`='".mysqli_real_escape_string($_POST['name'])."' WHERE `id`=1";

But please try to use PDO with transaction as you will be able to debug any errors and you don't have to worry about SQL Injection.

Try this: (you will see errors, and if it's not ok, it will rollback)

$db = new PDO('mysql:host=localhost;dbname=databaseName', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false));

$placename = $_POST['placename'];
$description = trim(addslashes($_POST['description']));
$hotel = $_POST['hotel'];
$transport = $_POST['transport'];
$map = $_POST['map'];

try {

    $db->beginTransaction();

    $stmt = $db->prepare("UPDATE `places` SET `placename`=:placename, `description`=:description, `hotel`=:hotel, `transport`=:transport, `map`=:map WHERE `place_id`=:place_id");
    $stmt->execute(array(':placename' => $placename, ':description' => $description, ':hotel' => $hotel, ':transport' => $transport, ':map' => $map, ':place_id' => $sPlace['place_id']));

    $db->commit();

} catch(PDOException $ex) {
    $db->rollBack();
    echo $ex->getMessage();
}

score 0 · Answer 2 · answered Aug 26 '12 at 01:44

0

You have an error in your SQL syntax ... 'map = map WHERE place_id = 54' at line 1

map = map <-- is invalid. the right-side should be an sql value (quoted string, number, etc). Perhaps map = 'map' (quote the value) is the intended result?

answered Aug 26 '12 at 01:44

jspcal

50,847
7
72
76

score 0 · Answer 3 · edited May 23 '17 at 11:51

The problem you are seeing has come about because none of your string literals have been quoted, so the comma in the value of $transport is being evaluated as a separator between SQL SET clauses and so gives rise to the syntax error that you witness.

You should quote your string literals—or better yet, use parameterised statements so that your variables do not get evaluated for SQL at all (which avoids all forms of SQL injection attack).

Syntax error at update query where clause mysql

3 Answers3