Page 1 of 1

problem with quotes in database field

Posted: Thu Dec 09, 2010 10:22 am
by mottwsc
I have text with an apostrophe (Bill's car) that I need to insert into a database. Before inserting it, I process it with

Code: Select all

$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 & # 39 s car (I'm spacing out the 'ampersand pound 39' so you see that it displays that way - not as a single quote)

When I select on the field

Code: Select all

select * from table1 where field1='Bill\'s car'
this returns no records

I have to use

Code: Select all

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

Code: Select all

<input type='text' name=field1 value='".htmlentities($field1Value, ENT_QUOTES, 'UTF-8')."' maxlength='50'>
I get - Bill & # 39 s car - in the browser, which is not what I want.

I have to use

Code: Select all

<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.

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

Code: Select all

$field1 = html_entity_decode(mysqli_real_escape_string($cxnA, $field1), ENT_QUOTES);
but this doesn't work either.

Any help would be appreciated.

Re: problem with quotes in database field

Posted: Thu Dec 09, 2010 10:27 am
by AbraCadaver
What is $field1 before you run it through mysqli_real_escape_string()? Make sure to view source on the page to see what it really is.

Re: problem with quotes in database field

Posted: Thu Dec 09, 2010 11:05 am
by mottwsc
Here is some debug and an interim step I added before the database insert...

Code: Select all

echo "field1 (1):".$field1."<br/>";
$field1 = html_entity_decode($field1,ENT_QUOTES);
echo "field1 (2):".$field1."<br/>";
$field1 = html_entity_decode(mysqli_real_escape_string($cxnA, $field1), ENT_QUOTES);
echo "field1 (3):".$field1."<br/>";
The output is
[text]field1 (1):Bill&#39s car
field1 (2):Bill&#39s car
field1 (3):Bill&#39s car[/text]

Re: problem with quotes in database field

Posted: Thu Dec 09, 2010 11:29 am
by mottwsc
For clarification, the output when you look at View Source is:

[text]field1 (1):Bill&#39s car
field1 (2):Bill&#39s car
field1 (3):Bill&#39s car[/text]

When you see it in the browser, it is:

[text]field1 (1):Bill's car
field1 (2):Bill's car
field1 (3):Bill's car[/text]

Re: problem with quotes in database field

Posted: Thu Dec 09, 2010 11:44 am
by AbraCadaver
mottwsc wrote:For clarification, the output when you look at View Source is:

[text]field1 (1):Bill&#39s car
field1 (2):Bill&#39s car
field1 (3):Bill&#39s car[/text]

When you see it in the browser, it is:

[text]field1 (1):Bill's car
field1 (2):Bill's car
field1 (3):Bill's car[/text]
So it's being encoded somewhere before you insert it in the database. Trace through your code and see where that is happening.

Re: problem with quotes in database field

Posted: Thu Dec 09, 2010 11:56 am
by mottwsc
The original data comes from an input field that is encrypted before it is inserted into the database table in a BLOB field...

Code: Select all

$field1Encrypted = mcrypt_encrypt($cipher_alg, $key, $field1, MCRYPT_MODE_CBC, $iv));
Then, it is pulled out of the BLOB field and decrypted...

Code: Select all

$field1 = mcrypt_decrypt($cipher_alg, $key, $field1, MCRYPT_MODE_CBC, $iv));
After this, it hits the code I've been discussing, where the decrypted visible data is escaped so that it can be inserted into another (VARCHAR) field in the table.

Re: problem with quotes in database field

Posted: Thu Dec 09, 2010 1:32 pm
by jaceinla

Code: Select all

<?php

$dbc = mysqli_connect('localhost','root','root','test');

$bill = "Bill's Car";
$bill = mysqli_real_escape_string($dbc, $bill);
$query = 'INSERT INTO simple (id,data) VALUES (NULL, "'.$bill.'")';

mysqli_query($dbc,$query);
?>
Perhaps I am not answering your question, but that code there inserts Bill's Car into my DB without the 39.

RESOLVED Re: problem with quotes in database field

Posted: Thu Dec 09, 2010 6:13 pm
by mottwsc
The problem was an initial string replace that changed a quote to the safe characters for the purpose of security.

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

Thanks to all for your suggestions.