problem with quotes in database field

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mottwsc
Forum Commoner
Posts: 55
Joined: Sun Dec 23, 2007 8:01 pm

problem with quotes in database field

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: problem with quotes in database field

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
mottwsc
Forum Commoner
Posts: 55
Joined: Sun Dec 23, 2007 8:01 pm

Re: problem with quotes in database field

Post 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]
mottwsc
Forum Commoner
Posts: 55
Joined: Sun Dec 23, 2007 8:01 pm

Re: problem with quotes in database field

Post 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]
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: problem with quotes in database field

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
mottwsc
Forum Commoner
Posts: 55
Joined: Sun Dec 23, 2007 8:01 pm

Re: problem with quotes in database field

Post 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.
jaceinla
Forum Commoner
Posts: 25
Joined: Thu Oct 14, 2010 12:57 pm

Re: problem with quotes in database field

Post 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.
mottwsc
Forum Commoner
Posts: 55
Joined: Sun Dec 23, 2007 8:01 pm

RESOLVED Re: problem with quotes in database field

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