Welcome to WebmasterWorld Guest from 23.20.137.66

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Apostrophe causing error in SQL syntax

magic_quotes_gpc On, using stripslashes

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

10+ Year Member



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!

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

10+ Year Member



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.

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

WebmasterWorld Senior Member 10+ Year Member



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.

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

10+ Year Member



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!

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

WebmasterWorld Senior Member dreamcatcher is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

10+ Year Member



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!

 

Featured Threads

Hot Threads This Week

Hot Threads This Month