ADDSLASHES and HTMLENTITIES don't seem to work

baffled_in_UK

Hi,

I have a problem with an insert into a MySQL D/b. Even though I am using addslashes and htmlentities the insert fails if I attempt to enter the following:

[INDENT]we have set up discounts and benefits to help 'subsidise' our membership fees[/INDENT]

before inserting into the Db I do this

$page_text = addslashes(trim($_POST['page_text']));
$page_text = htmlentities($page_text,ENT_QUOTES);

so I thought that would cover quotes and most other stuff?

Am I missing something obvious here ? As soon as I remove the single quotes the text is accepted and inserted ok.

Thanks

laserlight

Do not use addslashes() or htmlentities() at this stage. Instead, use the appropriate escaping mechanism, e.g., prepared statements with the PDO or MySQLi extensions, or mysql_real_escape_string().

Of course, magic_quotes_gpc should be set to Off in php.ini

baffled_in_UK

Just tried using mysql_real_escape_string() and still no joy. I have checked the magic_quotes_gpc value and it is 'Off'.

I've never used mysql_real_escape_string() before and had a quick look at the manual before trying it. I assume I just do this :

$page_text = mysql_real_escape_string($page_text)

before inserting the $page_text value?

Apologies if I used it incorrectly, advice is appreciated.

laserlight

Yes, that is correct. Next thing you can try is to print out the query.

baffled_in_UK

The printed result does not show any escape characters within the string? That doesn't sound right.

laserlight

That sounds wrong indeed. Try and test, e.g.,

<?php
$conn = mysql_connect($server, $username, $password);
mysql_select_db($database_name, $conn);
$str = "we have set up discounts and benefits to help 'subsidise' our membership fees";
echo htmlspecialchars($str) . "<br />\n";
$str = mysql_real_escape_string($str, $conn);
echo htmlspecialchars($str) . "<br />\n";
?>

baffled_in_UK

the result of that test was:

we have set up discounts and benefits to help 'subsidise' our membership fees
we have set up discounts and benefits to help \'subsidise\' our membership fees

now that looks good.

What I don't understand is why your example has the DB access when it doesn't update/insert? What is the relevance of that (even though it seems to create the escape characters) ?

Apologies if I'm being dim.

laserlight

Reading the PHP manual, you will see that [man]mysql_real_escape_string/man "escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query()" (emphasis mine).

Of course, if you leave out $conn it would use the active connection link, but I suspected that you might have used mysql_real_escape_string() before creating the connection, and perhaps this resulted in no escaping at all.

baffled_in_UK

Thank you for explaining that, I now understand.

I have just double-chcked my code and the connection is active when the mysql_real_escape_string() is performed.

This is very bizarre.

laserlight

As a last resort since we cannot seem to determine the root of the problem, does explicitly specifying the connection link resource work?

baffled_in_UK

Thanks for your patience laserlight.

I'm going to have to return to this tomorrow, I'm in the UK and have an early start so need to log off.

liquorvicar

Have you tried echoing out the created query and the error MySQL is throwing to see exactly what's going on?

baffled_in_UK

Yes, it doesn't like the single quotes.

liquorvicar

Can you post the echo-ed query that's failing and the accompanying MySQL error?

baffled_in_UK

Hi,
I've just sorted it. It's a bit bizzare.

This doesn't add the escape characters

$page_text = trim($_POST['page_text']);
$page_text = mysql_real_escape_string($page_text,$db);

but this does

$str = trim($_POST['page_text']);
$str = mysql_real_escape_string($str,$db);

whatever, it works great now.

Thanks laserlight for all your help and thanks liquorvicar for wanting to help 🙂

liquorvicar

There must be something else going on in your script.

I just ran this

$page_text = trim($_POST['page_text']);
$page_text = mysql_real_escape_string($page_text,$db);

on my dev box and it worked fine. I even turned on register_globals in case that was affecting it but it still worked.