[SOLVED] Update SQL record containing punctuation....

[cweepy]

Hello, I'm working on a backend for a website.  I am trying to update a book's information using an html form.  Some of the items that need to be updated will contain punctuation such as double or single quotes and semicolons.

 

	$sql = 'UPDATE books SET img = $id, Title = "'.$title.'", Author = "'.$author.'", desc = "'.$desc.'", isbn-10 = "'.$isbn10.'", isbn-13 = "'.$isbn13.'", Publisher = "'.$publisher.'", Cover = "'.$cover.'", pagecnt = $pagecnt, edition = "'.$edition.'", dimensions = "'.$dim.'", Language = "'.$lang.'", WHERE id = $id';

        mysql_query($sql) or die ("Query failed: " . mysql_error());

 

Error:

Query failed: 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 'desc = "", isbn-10 = "316143472", isbn-13 = "978-0316143479", Publisher = "Littl' at line 1

 

 

I believe I get this error because, as the query is executed, the quotes are acting as delimeters in the update line.  I tried adding "addslashes" to the query but that did not help. 

 

How do I avoid this conflict whilst keeping all puncuation open for use?  I'm using MySql 5.0.51a-community.

[PFMaBiSmAd]

http://www.phpfreaks.com/forums/index.php/topic,214979.0.html

 

[cweepy]

I tried to implement what was suggested in that thread to no avail...

 

        $id = $_POST['id'];
        $title = mysql_real_escape_string($_POST['title']);
        $author = mysql_real_escape_string($_POST['author']);
        $desc = mysql_real_escape_string($_POST['desc']);
$isbn10 = mysql_real_escape_string($_POST['isbn10']);
$isbn13 = mysql_real_escape_string($_POST['isbn13']);
        $publisher = mysql_real_escape_string($_POST['publisher']);
$cover = $_POST['cover'];
$pagecnt = $_POST['pagecnt'];
$edition = $_POST['edition'];
$dim = $_POST['dim'];
$lang = $_POST['lang'];

$sql = "UPDATE books SET img = $id, Title = $title, Author = $author, desc = $desc, isbn-10 = $isbn10, isbn-13 = $isbn13, Publisher = $publisher, Cover = $cover, pagecnt = $pagecnt, edition = $edition, dimensions = $dim, Language = $lang WHERE id = $id";

        mysql_query($sql) or die ("Query failed: " . mysql_error());

 

 

Error produced:

Query failed: 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 'When You Are Engulfed in, Author = David Sedaris, desc = , isbn-10 = 316143472, ' at line 1

[PFMaBiSmAd]

String values need single-quotes around them to make them strings instead of keywords or identifiers. Your original code has single-quotes around the string values, why are they not in the query you just posted?

[cweepy]

When I did not succeed the first time I attempted to remove the quotes to explore the effect.  Currently:

 

        $id = $_POST['id'];
        $title = mysql_real_escape_string($_POST['title']);
        $author = mysql_real_escape_string($_POST['author']);
        $desc = mysql_real_escape_string($_POST['desc']);
$isbn10 = mysql_real_escape_string($_POST['isbn10']);
$isbn13 = mysql_real_escape_string($_POST['isbn13']);
        $publisher = mysql_real_escape_string($_POST['publisher']);
$cover = mysql_real_escape_string($_POST['cover']);
$pagecnt = $_POST['pagecnt'];
$edition = mysql_real_escape_string($_POST['edition']);
$dim = mysql_real_escape_string($_POST['dim']);
$lang = mysql_real_escape_string($_POST['lang']);

$sql = "UPDATE books SET img = $id, Title = '$title', Author = '$author', desc = '$desc', isbn-10 = '$isbn10', isbn-13 = '$isbn13', Publisher = '$publisher', Cover = '$cover', pagecnt = $pagecnt, edition = '$edition', dimensions = '$dim', Language = '$lang' WHERE id = $id";

        mysql_query($sql) or die ("Query failed: " . mysql_error());

 

Error:

Query failed: 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 'desc = '', isbn-10 = '316143472', isbn-13 = '978-0316143479', Publisher = 'Littl' at line 1

[PFMaBiSmAd]

Now you are making progress. desc is a reserved keyword. I recommend changing the column name to something else.

[cweepy]

Really appreciate the help, it worked famously after I changed "desc" column in the db to "description" and after eliminating the "-" hyphens from isbn column names as well.

 

Final Code:

        $id = $_POST['id'];
        $title = mysql_real_escape_string($_POST['title']);
        $author = mysql_real_escape_string($_POST['author']);
        $desc = mysql_real_escape_string($_POST['summary']);
$isbn10 = mysql_real_escape_string($_POST['isbn10']);
$isbn13 = mysql_real_escape_string($_POST['isbn13']);
        $publisher = mysql_real_escape_string($_POST['publisher']);
$cover = mysql_real_escape_string($_POST['cover']);
$pagecnt = $_POST['pagecnt'];
$edition = mysql_real_escape_string($_POST['edition']);
$dim = mysql_real_escape_string($_POST['dim']);
$lang = mysql_real_escape_string($_POST['lang']);

$sql = "UPDATE books SET img = $id, Title = '$title', Author = '$author', description = '$desc', isbn10 = '$isbn10', isbn13 = '$isbn13', Publisher = '$publisher', Cover = '$cover', pagecnt = $pagecnt, edition = '$edition', dimensions = '$dim', Language = '$lang' WHERE id = $id";

        mysql_query($sql) or die ("Query failed: " . mysql_error());

 

 

Thanks again!

 

P.S. I'm amazed and pleased at your response times to my posts!  Your help is invaluable.