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's 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'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:29 am
by mottwsc
For clarification, the output when you look at View Source is:
[text]field1 (1):Bill's car
field1 (2):Bill's car
field1 (3):Bill's 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's car
field1 (2):Bill's car
field1 (3):Bill's 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.