Prepared Statement + mysqli_real_escape_string?

PHP
1

Is there a way to use mysqli_real_escape_string so that I can effectively escape single and double quotes from a data entry form before being inserted into MySQL, PLUS get the added benefits of a Prepared Statement?

Debbie

2

There’s no need for the mysql escape function when you’re using prepared statements.

3

yeah… if your using that function with prepared statements where there should be a separation between SQL and user controlled data your not doing things correctly.

4

If I wanted to insert this into MySQL…

<p>Debbie’s ride to work was late today, so she grabbed the bus.</p>
<p>It turns out the problem was that Mike’s car had a flat tire.</p>

How would a Prepared Statement handle the single quotes?

Without escape characters it would break.

With mysqli_real_escape_string it would be fine.

Debbie

5

Prepared Statements does not need to handle the single quotes. The SQL query and the data never mix. They are handled independently of one another. Understand, escape_string escapes those things that have meaning in an SQL query, however, if the data is never part of an SQL query it doesn’t need to escape those.

You do not use escape_string with prepared statements.

6

So for my needs, I don’t need to use Prepared Statements, but I will need mysqli_real_escape_string to escape ’ and " in my HTML markup, right?

Debbie

7

mysqli_real_escape_string gives a hint about it’s purpose with the first word of the function name (it for MySQL, not HTML).

It’ll escape ’ into \’ which is still going to look like \’ in your HTML.
It should become & #039; Use [fphp]htmlentities[/fphp]

8

A prepared statement will automatically do a normal SQL comment of single quotes (if you tell the engine you use double quotes instead of single quotes for strings, it will do the same with those etc).

If you take a look on the example below, this is how your string would look “internally” after you bind it to the prepared statement. It will add enclosed single quotes since you tell its a string, and any single quotes inside the string will get one more appended to it.

However when the insert is completed, it would look exactly the same as the string you originally had.

Example:
‘<p>Debbie’‘s ride to work was late today, so she grabbed the bus.</p>’