Quotation Marks in Forms into SQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Nunners
Forum Commoner
Posts: 89
Joined: Tue Jan 28, 2003 7:52 am
Location: Worcester, UK
Contact:

Quotation Marks in Forms into SQL

Post by Nunners »

I'm probably missing something really easy here, but I've had a problem with ' for a number of years, and it really is starting to be a problem as I develop larger more public sites.

Example:

A user would like to add a description of themselves into a text box on a form. On pressing submit, the form variables are passed, using Post to a PHP script that does the following:

Code: Select all

<?php
$sql_statement="insert into people (username,description) values ('$_Post&#1111;"username"]','$_Post&#1111;"description"]')"
&#1111;i]execution of mysql statement &#1111;i]
Now if the description were:
Hi my name is Bob
Then the sql would be:

Code: Select all

insert into people (username,description) values ('Bob','Hi my name is Bob')
So no problem there...

However, if the description was:
Hi, I'm Bob
sql would be:

Code: Select all

insert into people (username,description) values ('Bob','Hi I'm Bob')
And you'd get an error.....

What's the easiest way around this?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

The easiest way is addslashes() or mysql_escape_string() (either one will do). These functions will escape characters like ' within the string, so if you did:

Code: Select all

echo addslashes("Hi I'm Bob");
you'd get

Code: Select all

Hi I''m Bob
Then when you want to take the info out of the database and display it, you'd use stripslashes() to remove the escape characters so:

Code: Select all

echo stripslashes('Hi I''m Bob');
would produce:

Code: Select all

Hi I'm Bob
Some people will advise turning on magic_quotes_gpc in the php.ini but don't do this as it will cause all information posted from forms to be escaped and you won't always want this. It's best just to use addslashes() as and when you need it.

Mac
Nunners
Forum Commoner
Posts: 89
Joined: Tue Jan 28, 2003 7:52 am
Location: Worcester, UK
Contact:

Post by Nunners »

I can't believe it's that simple...... thanx ;)
Post Reply