SQL UPDATE with ' in content

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

SQL UPDATE with ' in content

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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'";
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

thanks feyd.
this seemed to work for me..

Code: Select all

linerFullDescription = "\".$linerFullDescription."\"
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

...and what if the submission has double quotes? or double quotes and single quotes?
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post 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?)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.. ;)
facets
Forum Contributor
Posts: 273
Joined: Wed Apr 13, 2005 1:53 am
Location: Detroit

Post by facets »

thank you!
insightful as always :)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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);
}

?>
Post Reply