not allowing apostrophies in mysql fields

[mottwsc]

I have text with an apostrophe (Bill's car) that has been encrypted and decrypted, then inserted into a database.  After it is decrypted but before inserting it, I process it with

$field1 = mysqli_real_escape_string($cxnA, $field1)

 

When I insert the field and view it in MySQL Query Browser, it shows the converted apostrophe: Bill&#39s car

 

When I select on the field

select * from table1 where field1='Bill\'s car'

this returns no records

 

I have to use

select * from table1 where field1='Bill&#39s car'

to get the record, which is very inconvenient

 

If I display this field in a browser using

<input type='text' name=field1 value='".htmlentities($field1Value, ENT_QUOTES, 'UTF-8')."' maxlength='50'>

I get - Bill&#39s car - in the browser, which is not what I want.

 

I have to use

<input type='text' name=field1 value='".htmlspecialchars_decode(htmlentities($field1Value, ENT_QUOTES, 'UTF-8'), ENT_QUOTES)."' maxlength='50'>

to get Bill's car to display.

 

The strange thing is that when I echo field1 before it is inserted, it shows Bill's car.

 

I have even tried the following to get it to insert the proper value:

$field1 = htmlspecialchars_decode(mysqli_real_escape_string($cxnA, $field1), ENT_QUOTES);

but this doesn't work either.

 

How do you prevent this from happening?

 

Thanks.

 

 

 

[PFMaBiSmAd]

Define: "that has been encrypted and decrypted"

 

The only way you are getting &#39 stored in the database is if the data already contained html entities before being stored into the database (and the solution would be to use html_entity_decode with ENT_QUOTES as the second parameter on the data before inserting it.)

 

 

 

[mottwsc]

I've encrypted and decrypted the text "Bill's car" in other processes before I get to this task using

$field1Encrypted = rtrim(mcrypt_encrypt($cipher_alg, $key, $field1, MCRYPT_MODE_CBC, $iv));

and

$field1 = rtrim(mcrypt_decrypt($cipher_alg, $key, $field1Encrypted, MCRYPT_MODE_CBC, $iv));

 

I just tried your suggestion

$field1 = html_entity_decode(mysqli_real_escape_string($cxnA, $field1), ENT_QUOTES);

but I have the same result on the database insert, unfortunately

 

[PFMaBiSmAd]

It would probably help if you posted enough of your code that shows the form and the form processing code through to where the INSERT query is being executed, everything that someone would need to reproduce the symptom. What you state you are getting for results don't match the few lines of code you have posted.

[mottwsc]

The problem was an initial string replace that changed a quote to the safe characters for the purpose of security.

 

$field1 = str_replace("'", "&#39", $field1);

 

I'm surprised that later functions designed to change it back didn't work.

 

Thanks to all for your suggestions.

[PFMaBiSmAd]

&#39 is the html entity of a html entity, so of course you were not getting the results you expected.

 

You should store the actual data in the database (escaping it only.)

 

You should use htmlentities (with ENT_QUOTES as the 2nd parameter) only when you output data on a web page.