homepage Welcome to WebmasterWorld Guest from 54.81.170.186
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Apostrophe causing error in SQL syntax
magic_quotes_gpc On, using stripslashes
oceanwave




msg:1267916
 4:14 pm on Mar 17, 2005 (gmt 0)

I have a form that get submitted to a php page before the values are added to the database. A sample of the beginning of the php page looks like:

<?
include 'db.php';
// Define post fields into simple variables
$text = $_POST['text'];
$message = $_POST['message'];

/* Strip some slashes in case the user entered any escaped characters. */

$text = stripslashes($text);
$message = stripslashes($message);

You get the idea. Later on in the page error checking on the form posted fields are called (for empty fields), the information is sent to the database, an email with the info is sent, etc.

Here's the problem. If the user types an apostrophe, the information is not submitted and the user gets the error, "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 '....". After doing a little research, I read that I had to check my magic quotes. I found this:

magic_quotes_gpc On On
magic_quotes_runtime Off Off
magic_quotes_sybase Off Off

I am on a shared server and do not have access to server settings.

What do I need to do to allow a user to enter an apostrophe in a text box or textarea? The strange thing is I don't think I get the error when quotes are entered.

As the information is added to an email, I don't want slashes to be added before every apostrophe.

Thank you so much!

 

Paul in South Africa




msg:1267917
 4:28 pm on Mar 17, 2005 (gmt 0)

Some charaters need to be escaped. One solution is to stripslashes only for the email so $text and $message will remain with slashes added then do something like

$emailtext = stripslashes($text);
$emailmessage = stripslashes($message);

then use $emailtext and $emailmessage in the part of you script that sends the mail.

mincklerstraat




msg:1267918
 4:53 pm on Mar 17, 2005 (gmt 0)

What you're doing in stripping slashes from all input is, I think, pretty much standard coding practice.

The thing is, though, when you formulate a query that uses some of this user input, you need to put those slashes back in again. Use mysql_real_escape_string() if you have version 4.3 or more or mysql_escape_string on each part that contains user input, and put quotes around these strings as necessary.

oceanwave




msg:1267919
 11:18 pm on Mar 17, 2005 (gmt 0)

Thank you so much for answering.

I still am unsure of what I need to do. I did see mention of mysql_real_escape_string() in some readings, but I am not sure what that means, where and how it should be coded, etc.

I also saw some suggestions on how to set an htaccess file to turn magic quotes off, but I am not sure if this is desirable. Also the code I saw did not follow the format of an htaccess file.

I still need help!

dreamcatcher




msg:1267920
 8:58 am on Mar 18, 2005 (gmt 0)

Try converting the apostrophe to a character entity when you post your data to the database using str_replace.

$text = str_replace("'", "&#039;", $text);

When you display your data, if you need to, convert it back.

dc

oceanwave




msg:1267921
 9:50 pm on Mar 19, 2005 (gmt 0)

I just want to thank everyone for their help! I used Paul in South Africa's idea, which worked perfectly. The email is sent without slashes, and the apostrophes are uploaded to the database. The database does not show the slashes (I read something that said the database removes the slashes?) before the apostophes.

If the database does not show the slashes, is there any kind of problem when I query the database? I had such a problem getting it into the database, will I have a problem getting it out of the database?

Thanks again!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved