single quotes in SQL Query

Question

Hi Guys,

Im writing a php script that is used to update a database but it is giving errors when i tries to run the query it returns an error along the lines of

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 'id=15"' at line 1

Where it says "To use near" seems to display part of the query after there is a space in the data. Im assuming i need to put single quotes around where the data to the query from the php variables but when i try to put them in (even escaping the quotes) i get parse errors from the script

The SQL Query is

    mysql_query("UPDATE Videos SET Title=".$_POST['Title'].", Preacher=".$_POST['Preacher'].", Date=".$_POST['Date'].", Service=".$_POST['Service'].", File=".$_POST['File'].", Description=".$_POST['Description']."WHERE id=".$_GET['vid_id']."\"") or die(mysql_error());

Thank in advance for any help

Answer 1

As you are using DB API directly (no DB abstraction level) the best solution is to use DB escape function.

Just use mysql_real_escape_string().

<?php
// Your query
$query = sprintf("UPDATE Videos SET Title='%s', preacher='%s', Date='%s', "
                     ."Service='%s', File='%s', Description='%s' WHERE id='%s'",
                 mysql_real_escape_string($_POST['Title']),
                 mysql_real_escape_string($_POST['Preacher']),
                 mysql_real_escape_string($_POST['Date']),
                 mysql_real_escape_string($_POST['Service']),
                 mysql_real_escape_string($_POST['File']),
                 mysql_real_escape_string($_POST['Description']),
                 mysql_real_escape_string(($_GET['vid_id']));
?>

As a bonus you'll get a really improved security against SQL INJECTION attacs your previous code was prone.

In the case you would simply escape slashes you have, again, to use php/mysql functions addslashes() will do the job in this case.

Answer 2

Why are you putting a \" right at the end, this puts a " on to the end of your SQL but you don't have one at the start?

Try this:

mysql_query("UPDATE Videos SET Title=".$_POST['Title'].", Preacher=".$_POST['Preacher'].", Date=".$_POST['Date'].", Service=".$_POST['Service'].", File=".$_POST['File'].", Description=".$_POST['Description']."WHERE id=".$_GET['vid_id']) or die(mysql_error());

Answer 3

You need to escape the variables properly and surround them by single quotes:

mysql_query("UPDATE
                Videos
            SET
                Title = '".mysql_real_escape_string($_POST['Title'])."',
                Preacher = '".mysql_real_escape_string($_POST['Preacher'])."', 
                Date = '".mysql_real_escape_string($_POST['Date'])."',
                Service = '".mysql_real_escape_string($_POST['Service'])."',
                File = '".mysql_real_escape_string($_POST['File'])."',
                Description = '".mysql_real_escape_string($_POST['Description'])."'
            WHERE
                id = '".mysql_real_escape_string($_GET['vid_id'])."'")
or die(mysql_error());

Without escaping your variables properly, you are making yourself vulnerable to SQL injection attacks.

EDIT

To simplify the above, you can do a few tricks:

// Apply mysql_escape_string to every item in $_POST
array_map('mysql_real_escape_string', $_POST);
// Get rid of $_POST, $_POST['Title'] becomes $p_Title
extract($_POST, EXTR_PREFIX_ALL, 'p_');

// Use sprintf to build your query
$query = sprintf("UPDATE
                Videos
            SET
                Title = '%s',
                Preacher = '%s', 
                Date = '%s',
                Service = '%s',
                File = '%s',
                Description = '%s'
            WHERE
                id = '%s'",
            $p_Title,
            $p_Preacher,
            $p_Service,
            $p_File,
            $p_Description,
            mysql_real_escape_string($_GET['vid_id']));

mysql_query($query) or die(mysql_error());

Note that mixing $_POST and $_GET variables is not encouraged. You should supply the update ID through an hidden input field in the form.

Answer 4

REMOVE \", from:

id=".$_GET['vid_id']."\""

Answer 5

mysql_real_escape_string() and sql injections have already been mentioned.
But right now your script (painstakingly) has to mix the sql statement with the data/parameters and in the next step the MySQL server has to separate the data from the statement.
Using (server-side) prepared statements both "parts" of your query are sent separately and the sql parser (of your MySQL server) can never get "confused" about where the statement ends and the data begins.

The php-mysql module doesn't know prepared statements but php-mysqli and PDO do.

$pdo = new PDO('mysql:host=localhost;dbname=test', '...', '...'); 
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$stmt = $pdo->prepare('
  UPDATE
    Videos
  SET
    Title=:title ,
    Preacher=:preacher ,
    Date=:date ,
    Service=:service ,
    File=:file ,
    Description=:description
  WHERE
    id=:id
');
$stmt->bindParam(':title', $_POST['title']);
$stmt->bindParam(':preacher', $_POST['preacher']);
$stmt->bindParam(':date', $_POST['date']);
$stmt->bindParam(':service', $_POST['service']);
$stmt->bindParam(':file', $_POST['file']);
$stmt->bindParam(':description', $_POST['description']);
$stmt->bindParam(':id', $_GET['id']); // really _GET?
$stmt->execute();

May seem a lot of bloat if you use $stmt for only one operation. But consider that otherwise you have to call mysql_real_escape_string() for each parameter.