Page 1 of 1

SQL UPDATE with ' in content

Posted: Sat Oct 08, 2005 11:16 pm
by facets
Hello,

I'm having some troubles with content being updated in my DB.
If the textarea has an ' in it, it throws an error.

Here's the code :

Code: Select all

$query = "UPDATE auliner SET 'linerFullDescription = '$linerFullDescription' WHERE linerId = '$linerId'";

I've also tried linerDescription = "'linerFullDescription'"
Which it doens't like either.

Any ideas? Ta, Will.

Posted: Sat Oct 08, 2005 11:20 pm
by feyd

Code: Select all

$linerFullDescription = mysql_real_escape_string($linerFullDescription);
$linerId = mysql_real_escape_string($linerId);
$query = "UPDATE `auliner` SET `linerFullDescription` = '$linerFullDescription' WHERE `linerId` = '$linerId'";

Posted: Sat Oct 08, 2005 11:23 pm
by facets
thanks feyd.
this seemed to work for me..

Code: Select all

linerFullDescription = "\".$linerFullDescription."\"

Posted: Sat Oct 08, 2005 11:25 pm
by feyd
...and what if the submission has double quotes? or double quotes and single quotes?

Posted: Sat Oct 08, 2005 11:30 pm
by facets
good point. never thought of that.

my code now looks like :

Code: Select all

$linerId  = $_POST['linerId'];
$linerDescription  = $_POST['linerDescription'];
$linerFullDescription  = $_POST['linerFullDescription'];
$grammage = $_POST['grammage'];
$caliper = $_POST['caliper'];
$strengthCD = $_POST['strengthCD'];
$strengthMD = $_POST['strengthMD'];
$translucency = $_POST['translucency'];
$shear = $_POST['shear'];
$humidity = $_POST['humidity'];
$smoothnessWS = $_POST['smoothnessWS'];
$smoothnessFS = $_POST['smoothnessFS'];
$releaseForceLow = $_POST['releaseForceLow'];
$releaseForceHigh = $_POST['releaseForceHigh'];

$linerFullDescription = mysql_real_escape_string($linerFullDescription);
$linerId = mysql_real_escape_string($linerId);

$query = "UPDATE auliner SET linerDescription='$linerDescription', linerFullDescription = '$linerFullDescription', grammage='$grammage',caliper='$caliper',strengthCD='$strengthCD',strengthMD='$strengthMD',translucency='$translucency',shear='$shear',humidity='$humidity',smoothnessWS='$smoothnessWS',smoothnessFS='$smoothnessFS',releaseForceLow='$releaseForceLow',releaseForceHigh='$releaseForceHigh',linerDateEntered='$linerDateEntered',linerUserId='$linerUserId' WHERE linerId = '$linerId'";
Next question is should I use the mysql_real_escape_string for all the POST lines?
Anyway to merge the two ? ie (POST and mysql_real_escape_string into one line?)

Posted: Sat Oct 08, 2005 11:34 pm
by feyd
all data that comes from a tainted source (submission in this case) should be processed for safety. So yes, all of the posted data should pass through it.

Code: Select all

$foo = mysql_real_escape_string($_POST['foo']);
for example.. ;)

Posted: Sat Oct 08, 2005 11:47 pm
by facets
thank you!
insightful as always :)

Posted: Sun Oct 09, 2005 12:06 am
by Jenk
Don't forget to check for magic_quotes_gpc, and remove the escaping slashes before using mysql_real_escape_string() if magic_quotes_gpc is on, and to establish a connection with the database before using mysql_real_escape_string().

Code: Select all

<?php

function sqlClean ($string) {
    if (get_magic_quotes_gpc()) {
        $string = stripslashes($string);
    }
    return mysql_real_escape_string($string);
}

?>