[SOLVED] Dumb questions about escaping characters

[KevinM1]

I know that one should escape input, especially if it's going to be put in a database, but I've never really understood why.  Could someone please explain it to me?

 

Also, if I escape input (say, by using mysql_real_escape_string()) and insert into a database, will it still be escaped if I read that information from the database and output it to the screen?

 

Thanks!

[per1os]

I would use this function instead of mysql_real_escape_string as it does the same thing wiithout the database connection

 

<?php
function myEscape($string) {
       return  get_magic_quotes_gpc()?addcslashes(stripslashes ($string), "\x00\n\are\\'\"\x1a" ):addcslashes($string, "\x00\n\are\\'\"\x1a" );
}
?>

 

The reason to escape the data is for one, to avoid mysql errors. When inserting data into my sql, lets say i want to insert "This is a fine's string"  that will cause an error because mysql uses ' to enclose string data in. When you run the "mysql_escape" it adds a slashes to "escape" the character.

 

The major reason is that if it is not done you are prone to SQL Injection in which someone can run something like ' OR 1  and retrieve all data or even worse run code to delete your tables etc.

 

Now if escaped properly like with the code I posted above (which will not allow the code to be double escaped) than you should NEVER stripslashes of data coming out of the database and it should display properly straight out.

 

Hope that helps.

[obsidian]

I would use this function instead of mysql_real_escape_string as it does the same thing wiithout the database connection

 

<?php
function myEscape($string) {
       return  get_magic_quotes_gpc()?addcslashes(stripslashes ($string), "\x00\n\are\\'\"\x1a" ):addcslashes($string, "\x00\n\are\\'\"\x1a" );
}
?>

 

No offense, but that's really not very good advice on a couple levels. First, it does not do exactly the same thing as *_escape_string (especially if you're using postgresql or another database). I don't have time to go into the whole debate right now since it's been discussed many times before, but here are a few pointers to keep in mind:

 

1) Always escape user input that will be used within a query (yes, even in a SELECT statement) with the corresponding *_escape_string function:

<?php
// MySQL Connection
$username = mysql_real_escape_string($_POST['username']);
$sql = mysql_query("SELECT * FROM users WHERE username = '$username'");

// PostgreSQL Connection
$username = pg_escape_string($_POST['username']);
$sql = pg_query("SELECT * FROM users WHERE username = '$username'");
?>

 

2) There is no reason to escape strings when it's not going to be used along with a SQL connection, so there's no reason to avoid using the built in functions. The purpose of escaping user input is to avoid SQL injection, which without a database connection is impossible.

 

3) To output user information to the screen, 99% of the time, the database will clean it automatically as you extract it. Typically, simply using something like htmlentities or the like to protect against XSS or errant data is all that you would need.

 

Hope this helps some. If you need further help on understanding the ramifications of SQL injection, do a Google search for "SQL injection white papers," and I'm sure you'll get plenty

[per1os]

I would use this function instead of mysql_real_escape_string as it does the same thing wiithout the database connection

 

<?php
function myEscape($string) {
       return  get_magic_quotes_gpc()?addcslashes(stripslashes ($string), "\x00\n\are\\'\"\x1a" ):addcslashes($string, "\x00\n\are\\'\"\x1a" );
}
?>

 

No offense, but that's really not very good advice on a couple levels. First, it does not do exactly the same thing as *_escape_string (especially if you're using postgresql or another database). I don't have time to go into the whole debate right now since it's been discussed many times before, but here are a few pointers to keep in mind:

 

1) Always escape user input that will be used within a query (yes, even in a SELECT statement) with the corresponding *_escape_string function:

<?php
// MySQL Connection
$username = mysql_real_escape_string($_POST['username']);
$sql = mysql_query("SELECT * FROM users WHERE username = '$username'");

// PostgreSQL Connection
$username = pg_escape_string($_POST['username']);
$sql = pg_query("SELECT * FROM users WHERE username = '$username'");
?>

 

2) There is no reason to escape strings when it's not going to be used along with a SQL connection, so there's no reason to avoid using the built in functions. The purpose of escaping user input is to avoid SQL injection, which without a database connection is impossible.

 

3) To output user information to the screen, 99% of the time, the database will clean it automatically as you extract it. Typically, simply using something like htmlentities or the like to protect against XSS or errant data is all that you would need.

 

Hope this helps some. If you need further help on understanding the ramifications of SQL injection, do a Google search for "SQL injection white papers," and I'm sure you'll get plenty

 

Agreed, I usually only use MySQL, I should probably change the name of that function to reflect that.

 

Also with your code you showed there, what if the magic_quotes_gpc is on? You are essentially double escaping the string correct? Which is why I did add the stripslashes in my code, perhaps this might suit you better:

 

<?php
function myEscape($string, $db='mysql') {
       if (strtolower($db) == 'mysql') {
             return  get_magic_quotes_gpc()?mysql_real_escape_string(stripslashes ($string)):mysql_real_escape_string($string);
       }elseif (strtolower($db) == 'pg') {
             return  get_magic_quotes_gpc()?pg_escape_string(stripslashes ($string)):pg_escape_string($string);
       }
}
?>

 

Which allows for db selection and to properly escape it even with the magic quotes on to avoid the double escaping.

[obsidian]

Also with your code you showed there, what if the magic_quotes_gpc is on? You are essentially double escaping the string correct?

 

In short, yes, you are correct; however, you would hopefully know your system and whether or not you need to account for magic_quotes. My preference is to have it off all the time and account for things manually. On the other hand, I can see a use for checking it if you are writing for an open source project or the like.

 

In addition, if you read a few resources about the setting, you will soon see the value of turning it off altogether. Check out this quote from Wikipedia:

The PHP documentation points out several pitfalls and recommends that although they are enabled by default, they should ideally be disabled.

[per1os]

I can see a use for checking it if you are writing for an open source project or the like.

 

Agreed. Thats what I am usually coding for, so yea it is just a habit. Fun stuff.

[KevinM1]

Thanks for the responses, guys!