Question On How Apostrophe's Are Handled Inserting Into MYSql

[TecTao]

This is something I've been trying to figure out for some time.  I've read blogs and other forums and am still not clear.

 

Seems that when I pass a variable that has Apostrophe's in the variable, from a form page to the submit page and insert it into the MySql DB table, it inserts OK without any / before the apostrophe.

 

On the other hand on the same submit page, there is a select query from another table and there are variables with apostrophe's.  These queried variables keep the variables from the form page and the queried DB from inserting into a new table.

 

So I use mysql_real_escape_string () for the variables queried from the table to be inserted into the new table, don't use mysql_real_escape_string () on the variables passed frm the form page, and everything inserts into the new table just fine.  Displays with no forward slashes.

 

My confusion comes from when to use mysql_real_escape_string (), stripslashes () and htmlspecialchars().

 

Also in the reading I was doing, it looks like mysql_real_escape_string () is being replaced with mysqli_real_escape_string (), but when I tried to use it on a variable queried from the DB something like

$username = mysqli_real_escape_string ( $s['username'] )

( $s being 'foreach ( $result as $s )' from the select query.

 

Thanks in advance for shedding any light on this.

[requinix]

Jacques will be around shortly. He loves these threads. Until then...

 

- Always use mysql(i)_real_escape_string() on everything coming from outside your own PHP code, including anything in $_GET, $_POST, $_REQUEST, and $_COOKIE.

- Also use it when dealing with string values in SQL. Yes, you would use it on $s['username'] because it is a string and you don't know that it's safe to use. "Safeness" is about whether the value can mess up your SQL query because it has quotes or something that you're also using, not about where the data came from.

- Nowadays don't use stripslashes(). There are legitimate uses for it but none of them involve sanitizing form data or running SQL queries.

- Use htmlspecialchars() for outputting data into HTML and XML. You should use ENT_QUOTES in the second argument and you should always make sure it uses the correct encoding (such as by using the third argument).

 

Even better than wondering when to use mysql(i)_real_escape_string is to use prepared statements instead. They're a tiny bit slower but you don't have to think about SQL injection. mysqli and PDO only - not available with the mysql extension and mysql_* functions.

You make a query with placeholders for where the data should go, like INSERT INTO table (username) VALUES (?), prepare it, then when you execute it you say what that data should be.

Edited December 17, 2014 by requinix

[TecTao]

Thank you Requinix.  This clears up issues and much to think about.