can't sumbit text field containing an apostrophe

dpoincelot

Hi, I have a text field on the page, when someone enters text that contains an apostrophe I get a SQL error. Any ideas??
Thanks

Warning: MS SQL message: Line 1: Incorrect syntax near 'r'. (severity 15) in F:\web\functions2.php on line 29

Warning: MS SQL message: Unclosed quotation mark before the character string ',',',')'. (severity 15) in F:\web\functions2.php on line 29

Warning: MS SQL: Query failed in F:\web\functions2.php on line 29
Unable to query db

notionlogic

This is a pretty common error until you get the hang of it... 🙂

You need to use something like addslashes($stringvar) to escape the apostrophes (and other odd characters)

dpoincelot

Tried this:

$title = addslashes($_POST["title"]);

$rowset = $dbclass->database_sql("INSERT INTO lit_library (title) VALUES('$title');

Still having problems...

stolzyboy

try using # signs around the variable

$rowset = $dbclass->database_sql("INSERT INTO lit_library (title) VALUES('#$title#');d

dpoincelot

The pound sign didn't work.

This worked, but it seems like it would be a real pain to implement throughout the site. I would then have to turn all the quotes back to normal when they are displayed...Yikes! Plus, what about other wierd characters?? Is there a better way??

$title = htmlspecialchars($_POST[title], ENT_QUOTES);
$rowset = $dbclass->database_sql("INSERT INTO lit_library (title) VALUES('$title');

ereptur

If the title field is of type varchar or char then you need to use double quotes around the variable while inserting it.

you can try this:
$rowset = $dbclass->database_sql("INSERT INTO lit_library (title) VALUES(\"$title\");

stolzyboy

is that a MSSQL specific thing, cuz that isn't true in MySQL

dpoincelot

I am using MS SQL and VarChar, so adding the double qoutes seems to do the same thing as htmlentities, it escapes all the quotes. So either way I have to convert all variables in my application back to text? Seems like a lot of work...

\"$_POST[title]\"

cat's becomes cat\'s

Then decode back? html_entity_decode()?

Actually I just tried html_entity_decode() and it didn't work, it said that it was an undefined function.

altexis

from your code above.. you missed the closing sequence ")
try this one:

$title = addslashes($_POST["title"]); 

$rowset = $dbclass->database_sql("INSERT INTO lit_library (title) VALUES('$title')");

ahundiak

Review this thread.
(Well I was going to add a link here but I can't figure out how to do it and time is short. But search for mssql and you will find many related thread)

The whole notion of escaping (with slashes) is a non-standard extension of sql that mysql and postgresql support.

"Real" sql (like MSsql) requires that you "double up" single quotes when inserting. Something like

$data = str_replace("'","''",$data);
$sql = "Insert into table values ('$data')";

Read the comments in manual on stripslashes and addslashes. This topic is pretty common.

Many sites have a global "majic_quotes" variable set which actually adds unwanted escape characters to data received from html forms. When using MSsql you have to strip these slashes out as well.

It should be pointed out that both mysql and postgresql also support the "real" way of dealing with quotes. So what works on MSsql will also work on mysql.

(Actually, you need to escape the escape character on mysql to store it, not sure about MSsql).

I guess it's just unfortunate that escaping the data in a non-standard way has become common.

Archbob

This may just sound stupid but can't you just addlashes and then stripslashes and then insert into the database as normal?

dpoincelot

Thank you everyone,

It turns ereptur's method seems to be the most efficient, I add the double quotes in the SQL statement and then stripslashes on the display. Just have to go back and modifiy everything!

Thanks