Problem with '

stephen

I have been using a script on my site to update the websites database but have run into a small problem.

The current code looks like this:

$sql = "INSERT INTO insider (article,date,subject,content,author) VALUES ('$article','$date','$subject','$content','$author')";

Because the ' is used to close the fields if I use that in my entry form, the content is not posted.

How can I re-code this to allow me to use the ' symbol.

thanks

junebug

addslashes()

http://www.php.net/addslashes

Anon

you might want to check out ..

stripslashes()

too

stephen

I am looking at this addslashes and Im not sure exactly how to use it or if it is what Im looking for.

Is it as simple as adding addslashes into the insert statement?

Im not sure why, but I was thinking there was another way to run the insert that would not stop if it hit a ' in the entry form but then again I maybe dreaming on this one.

if ($submit) {
$sql = "INSERT INTO insider (article,date,subject,content,author) VALUES ('$article','$date','$subject','$content','$author')";

} else{
// display form
echo '

<form method="post" action="control.php?page=insider">
<TD>Date: </TD><TD><input type="Text" name="date" MAXLENGTH=25 > ex: August 1st, 2001</TD>
<TD>Subject: </TD><TD><input type="Text" name="subject" MAXLENGTH=20 > Max of 20 Characters</TD>

Anon

I've actually found that PHP's method of escaping single quotes with a slash is incompatible with Oracle, the database I'm using. In fact all the database's I've used require that single quotes are escaped with double single quotes, not a prefixed backslash.

So, the statement INSERT 'It's a nice day' should be INSERT 'It''s a nice day'. Most databases will choke on the statement 'It\'s a nice day'.

In fact, I've gone so far as to disable PHP's MAGIC_QUOTES_GPC global setting, which basically runs all GET, POST, and COOKIE vars through the AddSlashes() function automatically, later requiring proper output of these string to be ran through the StripSlashes() function. What a pain. It only gets in the way for me and makes handling database input and output a major chore, in addition to handling user input and output. I never know when PHP has modified the variable's content behind my back, and I have to basically guess when I need to use StripSlashes(). It's too confusing.

In a nutshell, for queries, you can use the Str_Replace() function to replace single quotes with double quotes:

Str_Replace("'", "''", $string)

I like to have a function that does this, so I can re-use the code and even modify it for all queries in one place. Hint: a function called "QueryEncode()" might be handy to encode all client-based variables in SQL statements.

As for form output, you sort of run into the same problem if the value has a double quote in it, so a similar function that converts double quotes into HTML entities (such as the HTMLSpecialChars() function) is a good idea too. Again, creating your own function to do this, such as "FormEncode()" might be handy.

Maybe I'm missing the advantage of the MAGIC_QUOTES settings in PHP, and maybe MySQL and/or Postgress prefer dealing with the slash-escaped single quotes. And maybe there's a setting in Oracle to handle PHP-espaced strings better. However, for PHP to assume that the database wants slash-escaped characters is quite wrong, and for it to manipute client variables is ever worse, espeically to users who don't know what's going on behind the scenes.

Anon

Create an intermediate code that replaces the \' with \\'.

ereg_replace(\"\'\", \"\\'\", $string);

Mind you, I\'m no PHP expert.

Anon

And maybe there's a setting in Oracle

There is: in the configuration file, set 'magic_quotes_sybase' to true.

See:

http://www.php.net/manual/en/configuration.php#configuration.file

Anon

for example if your text field in the form was named "message"

here is how it would look:

$message = addslashes($message);

$sql = "INSERT INTO field (message) VALUES ('$message')";
$return = mysql_query($sql) or die ("couldnt complete inscert");