I have a pretty large PHP/MySQL app, and I am in the process of upgrading the scripts from very late PHP3/very early PHP4 to a more modern release.
In this app, we store a lot of user name information. So question for everyone, person's name is John D'Angelo.
What do you store in MySQL? D'Angelo or D/'Angelo?
I started one way (not storing the slashes) and it made some of my queries go crazy. So then I started storing the slashes, and that made the other queries go crazy!
So what's best practice? Store the slashes in MySQL or don't store them?
Best Way For Stripslashes
Moderator: General Moderators
Re: Best Way For Stripslashes
The best practice is to use mysql_real_escape_string() or it's mysqli/PDO equivalent. It escapes the string for much more than quotes and doesn't change the data stored in the database.
Re: Best Way For Stripslashes
So for instance, $fname =John $lname= D'Angelo (from a POST on web page)
$fname2=mysql_real_escape_string($fname);
$lanme2=mysql_real_escape_string($lname);
And we say INSERT into PEOPLES (fname,lname) VALUES ($fname2, $lname2);
(this is a VERY basic INSERT query, most of mine are dynamically built with a lot of concatenation, etc)
Example:
//No errors on validation, insert new record
if ($msgFormErr==""){
//Insert Student Bio
$sSQL = "insert into studentbio (" .
"studentbio_internalid," .
"studentbio_active," .
"studentbio_lname," .
"studentbio_fname," .
"studentbio_mi," .
---snip----
Don't I now store D\'Angelo in my database? Which when I then go to display, I must stripslash it so it displays correctly?
$fname2=mysql_real_escape_string($fname);
$lanme2=mysql_real_escape_string($lname);
And we say INSERT into PEOPLES (fname,lname) VALUES ($fname2, $lname2);
(this is a VERY basic INSERT query, most of mine are dynamically built with a lot of concatenation, etc)
Example:
//No errors on validation, insert new record
if ($msgFormErr==""){
//Insert Student Bio
$sSQL = "insert into studentbio (" .
"studentbio_internalid," .
"studentbio_active," .
"studentbio_lname," .
"studentbio_fname," .
"studentbio_mi," .
---snip----
Don't I now store D\'Angelo in my database? Which when I then go to display, I must stripslash it so it displays correctly?
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Best Way For Stripslashes
No. Not unless you have something automatic turned on like magic_quotes_gpc (which escapes post, get data) and then mysql_real_escape_string() a post, get var. Build your own function, especially if the app is portable or in case settings change later etc...dougp23 wrote:So for instance, $fname =John $lname= D'Angelo (from a POST on web page)
$fname2=mysql_real_escape_string($fname);
$lanme2=mysql_real_escape_string($lname);
And we say INSERT into PEOPLES (fname,lname) VALUES ($fname2, $lname2);
(this is a VERY basic INSERT query, most of mine are dynamically built with a lot of concatenation, etc)
Example:
//No errors on validation, insert new record
if ($msgFormErr==""){
//Insert Student Bio
$sSQL = "insert into studentbio (" .
"studentbio_internalid," .
"studentbio_active," .
"studentbio_lname," .
"studentbio_fname," .
"studentbio_mi," .
---snip----
Don't I now store D\'Angelo in my database? Which when I then go to display, I must stripslash it so it displays correctly?
Code: Select all
function prep4db($var, $user_data=true) {
if($user_data && magic_quotes_gpc()) {
$var = stripslashes($var);
}
return mysql_real_escape_string($var);
}
Last edited by AbraCadaver on Wed Jan 13, 2010 9:31 pm, edited 2 times in total.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Best Way For Stripslashes
I am trying to follow along, but getting lost, lol!
It seems if $lname= D'Angelo
and I use the mysql_real_escape_string($lname),
I now have D'\Angelo
And if I build a SQL insert statement , aren;t I then INSERTING D'\Angelo??
Or does PHP or MySQL know enough to drop the \
It seems if $lname= D'Angelo
and I use the mysql_real_escape_string($lname),
I now have D'\Angelo
And if I build a SQL insert statement , aren;t I then INSERTING D'\Angelo??
Or does PHP or MySQL know enough to drop the \
Re: Best Way For Stripslashes
It is used for escaping only (ie, as to not break the query). It will not be stored in the database, and you do not to remove it when retrieving said data.
Re: Best Way For Stripslashes
With the value "D'Angelo", there are a number of things you need to do.
- Check if magic_quotes is turned on. If it is, then $_POST['lname'] will be "D\'Angelo". I usually fix this with:
If you're not familiar with ternary operators, that statement essentially says "If magic_quotes are on, remove any slashes from what was in POST, otherwise just give me what was in POST.
Code: Select all
$lname = (get_magic_quotes_gpc()) ? stripslashes($_POST['lname']) : $_POST['lname'];
After this, $lname will always be "D'Angelo" - You then need to escape it for the database using mysql_real_escape_string() like has already been suggested.$lname will now = "D\'Angelo" & is ready to be inserted into the database.
Code: Select all
$lname = mysql_real_escape_string($lname); - In your query, quote all values with single quotes. If you use double-quotes, I don't think MySQL will recognize the fact that you're escaping the single quote in the name - because it's not necessary. Anyway - MySQL will strip the escaping slash when the value is inserted, so the last name in your database will be "D'Angelo"
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.