[SQL Server]Incorrect syntax near 'neal'

Question

I'm trying to update my a row. But it automatically creates an error because the data in a column contains this Shaquille O'neal Or are there any problems ? Here's my code

  <?php
if(isset($_POST['editSubmit'])){
    $buildingID       = $_POST['editBuilding'];
    $buildingName     = $_POST['editBuildingName'];   
    $buildingProject  = $_POST['editBuildingProject'];
    $buildingFloors   = $_POST['editBuildingFloors'];
    $q = "update tblBuilding SET buildingName= '$buildingName' building_projectID='$buildingProject'
          floorNumber = '$buildingFloors'         
          where buildingID = '$buildingID'"; 
    $query = $db-> prepare($q);
    $results = $query->execute();
    echo" <meta http-equiv='refresh' content='0;url=project.php'>"; 
}
?>

EDITED: Prepared:

<?php
if(isset($_POST['editSubmit'])){
    $buildingID       = $_POST['editBuilding'];
    $buildingName     = $_POST['editBuildingName'];   
    $buildingProject  = $_POST['editBuildingProject'];
    $buildingFloors   = $_POST['editBuildingFloors'];

$stmt = $db->prepare("update tblBuilding set buildingName=?, building_projectID=?,floorNumber=?  where buildingID = $buildingID");

$stmt->bindParam(1, $buildingName );
$stmt->bindParam(2, $buildingProject);
$stmt->bindParam(3, $buildingFloors );
$stmt->execute();



    echo" <meta http-equiv='refresh' content='0;url=project.php'>"; 
}
?>

You need a comma between the columns used for `set`. – Gordon Linoff Mar 12 '16 at 00:07 — Gordon Linoff, Mar 12 '16 at 00:07

score 1 · Accepted Answer · answered Mar 12 '16 at 00:19

1

The real problem is that you're concatenating input data into SQL. This is a no-go: it opens the door wide for SQL injection problems.

Use parametrized queries and your problems should vanish.

answered Mar 12 '16 at 00:19

Lucero

59,176
9
122
152

Could you provide an example for parametrized update query? – juju17 Mar 12 '16 at 00:19
1

Have a look at the PHP docs: https://secure.php.net/manual/en/pdo.prepared-statements.php – Lucero Mar 12 '16 at 00:21
`Incorrect syntax near '('.` Please take a look at my prepared statement. Look in the main post. – juju17 Mar 12 '16 at 00:36
1

@user4932301 You broke the syntax of the update statement... you really just need to substitute your `$something` with `?` and bind them. That is, `... set column1=?, column2=?, ...` Do it for ALL - including the `buildingID`! – Lucero Mar 12 '16 at 00:38
Sir Lucero, you're a legend! Thank you for the learnings! God bless :D – juju17 Mar 12 '16 at 00:43
Thanks and good luck to you! – Lucero Mar 12 '16 at 00:44

score 0 · Answer 2 · edited Mar 13 '16 at 02:41

0

I suggest you could print out the query. Most likely it's because you wrap the value $buildingName using single quote which paired with the one in Shaquille O'neal then causes the rest of the query syntax error.

edited Mar 13 '16 at 02:41

davejal

6,009
10
39
82

answered Mar 12 '16 at 00:12

Hooyah

1

[SQL Server]Incorrect syntax near 'neal'

2 Answers2