Storing/Querying data containing apostrophes

[cmayo]

Hi all,

My client's database has been escaping character fields using PHP's addslashes(), resulting in several records with fields containing values like "O\'Henry"

Querying for that data seems problematic as those fields aren't returned by LIKE "O'Henry%" or escaped, "O\\\'Henry%"

Hand-munging the escaping, I find that escaping the embedded backslash instead of the apostrophe, i.e. LIKE "O\\\\'Henry%" will fetch the desired records, as will escaping both the backslash and the apostrophe with LIKE "O\\\\\\\'Henry%"

So my problem, I guess, is how to have PHP do that munging on user-entered search strings, i.e. when the user entere "O'Henry" into a form field. If I use addslashes() or mysql_real_escape_string() on the user text, i.e.

name like '".trim(addslashes($_POST['name']))."%'

the resulting query will read "LIKE O\\\'Henry%" and fail to find the records. If I double-escape the search string, i.e.

name like '".trim(addslashes(addslashes($_POST['name'])))."%'

I'll get a query containing

name like 'Bob\\\\\\\'s Burgers%'

but that's really ugly, and there's no telling how many other queries such double-escaping would break.

Could someone please help me sort this mess out?

Thanks much,
Chuck

 

[vacunita]

For starters you should not be having any issues with the slashes, as they are only to perform the insertion, but the slash \ characters themselves should not be inserted into the DB.


In Other words,

O'Henry will be converted into O\'Henry but once in the DB it should be just O'Henry. The slashes are not included because they aren't considered actual characters in the string. But rather escape characters so as to not throw off the insertion query.


Perhaps you can show us your insertion code or post it in the forum434 to see why you might be getting all those slashes.











----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[cmayo]

I understand that the values should have been inserted into the database as "O'Henry" instead of "O\'Henry" but I'm dealing with an existing database where all text insertions have already been filtered through PHP's addslashes() function:

$query = "INSERT INTO job_sites (name) VALUES (" . addslashes($_REQUEST['name']) . ")";

so all character data in the database containing apostrophes has those apostrophes escaped in the database as:

mysql> select name from job_sites where job_site_id = 1436;
+------------+
| name       |
+------------+
| Joe\'s Bar |
+------------+
1 row in set (0.00 sec)

On reflection, though, it does seem like I'm looking more for a PHP workaround than a pure MySQL solution, so I'll do as you suggested and post over there.

Thanks,
Chuck

 

[vacunita]

As I said it shouldn't have done that to begin with, both PHP fucntions (addslashes and mysql_real_escape_string) work in the same fashion. The slashes should have been ignored, unless something was done to the strings before running he insert statement.

You may be able to run through the DB and remove them with a PHP script. But the real question is why it did that.

So again posting your insertion scripts may be of help.

Having the Slashes there is plain wrong and will end up causing more trouble. If keeps adding slashes onto the information you'll end up with a DB that's more slashes than actual data.

I would think fixing the contents instead of working around them would be a better alternative.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[cmayo]

Ah geez, you're right. None of the insertion queries in the system are unescaping the $_REQUEST variables before escaping them for MySQL. The app's typical insertion code looks like:

<?php

require("include/db.inc");

print "REQUEST<pre>\n";
print_r($_REQUEST);
print "</pre>\n";

if(isset($_REQUEST['search'])) {

    $query = "INSERT INTO job_sites (name) VALUES ('".AddSlashes($_REQUEST['search'])."')";

    $result = mysql_query($query);

    if (!$result) {
        print 'Invalid query: <br />' . mysql_error() . "<br />Q<pre>$query</pre>\n";
    }

    print "Query:<pre>$query</pre>\n";

}

?>

<form>
    <input type="text" name="search" />
    <input name = "submit" type="submit" />
</form>

Which is leaving the $_REQUEST variable escaped, resulting in PHP escaping the escape character:

REQUEST
Array
(
    [search] => O\'Henry
    [PHPSESSID] => 233d3f20ff1ec4587785c040bb0cb7c9
)

Query:
INSERT INTO job_sites (name) VALUES ('O\\\'Henry')

Looks like all those insertion queries need something like

$query = "INSERT INTO job_sites (name) VALUES ('".mysql_real_escape_string(stripslashes($_REQUEST['search']))."')";

I do agree about fixing the data and the insertion queries, especially now after seeing the real issue... I'm just not sure my client's going to sign off on that much work.

Dangit.

Clumsy workaround, anyone?

 

[vacunita]

I believe Jpadie has nailed it in his response to your thread in the PHP forum.

For ease of use. Lets stop this thread here, and continue there, as this is now a PHP issue rather than mysql. For future reference the thread is: thread434-1546168



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[cmayo]

Agreed, thank you for your help.