How can I escape single quotes in this scenario?

Question

I have a page which makes a jquery call to an api to receive multiple wikipedia urls. I then extract the article names from the url (i.e. get science from http://en.wikipedia.org/science etc), add single quotes (') to each one, string them together and finally send them to a php page, which makes a mysql select * from MyTable where title in('name1','name2','name3','name4') call. The problem arises when the article name already has a single quote in it (i.e. "Hick's law"), as it breaks the where in single quotes. Here's the code I'm using:

$.getJSON('http://ajax.googleapis.com/ajax/services/search/web?q=keyword site:en.wikipedia.org&rsz=8&v=1.0&callback=?',
                function (r) {
                var urls1="";
                $.each(r.responseData.results, function(i, item) {
                var thisurl = (i==0) ? "'" +  item.url.substring(item.url.lastIndexOf('/') + 1) + "'" : ",'" + item.url.substring(item.url.lastIndexOf('/') + 1) + "'";
                urls1 += thisurl.replace(/_/g,'%20');

});});
$('#quotes').html($('<div>').load('pr.php?s='+urls1 +' #quotes', function() {}

I'm adding the single quotes to the article names so the string should be all ready to go for the mysql where in.

So to recap, the steps are as follows:

Make an api call and get multiple Wikipedia urls,
get the article name from each url,
add them to the urls1 string while replacing underscores with spaces
send the urls1 string via ajax to the pr.php page.
In pr.php I do the following: "SELECT * FROM MyTable WHERE title in".$_GET['s']

I tried doing mysql_real_escape_string($_GET['s']) but that didn't work.

I'm now trying to escape any single quotes inside the article names so the where in doesn't break, but it's not working. I tried changing the above to

var thisurl=(i==0) ? "'"+item.url.substring(item.url.lastIndexOf('/') + 1).replace(/'/g, "\'")+"'":",'"+item.url.substring(item.url.lastIndexOf('/') + 1).replace(/'/g, "\'")+"'";

But it didn't work. Any ideas?

TIA!

[The Great Escapism (Or: What You Need To Know To Work With Text Within Text)](http://kunststube.net/escapism/) — deceze, Mar 07 '13 at 13:01
why are you trying to escape using JS? you should escape in PHP — JamesHalsall, Mar 07 '13 at 13:01
You are passing half the SQL statement via an URL? that sounds terrible, error prone and just plain wrong. Send json back to PHP, use json_encode/json_decode, and handle escaping server side (e.g. by using parametrized queries) — Mahn, Mar 07 '13 at 13:05
@deceze I tried the solution there, it didn't work. I'm adding some more info in the question. — Phil, Mar 07 '13 at 13:09
Am I understanding correctly that your problem is that your server-side SQL query breaks when you send data with an apostrophe from the client? Then you need to learn to escape SQL queries properly, on the server. That's not the client's job. — deceze, Mar 07 '13 at 13:12
@deceze the problem here is that the string received by the server looks like this: `'string1','string2','string'3','string4'`. So the problem is how to escape one quote (the one in `string'3`) but not all the others. I figured the best way was to take care of it before adding the other single quotes before sending the string to the server, but I'm willing to hear better ideas! — Phil, Mar 07 '13 at 13:16
Send the data in a format that separates individual items. E.g., send a JSON array. Then it's the server's job to assemble the query and escape each item individually. — deceze, Mar 07 '13 at 13:19

LeGEC · Answer 1 · 2013-03-08T08:31:02.633

You should not sanitize SQL queries on the client side, but on the server side.

The requests received by your server may be correctly escaped if they have been produced by your javascript code, but you have no control (and will never have) over how the request is sent from the client.

e.g : I could explicitly type a url in my browser address bar containing as many quotes as I would see fit.

On the php side, you can use mysql_escape_string :

<?
    $sanitized = array();
    foreach( $wikiPages as $page ){
        $sanitized[] = mysql_escape_string($page);
    }
    $values = "'".implode("','", $sanitized)."'";

score 0 · Answer 2 · answered Mar 07 '13 at 13:03

Because its HTML content you should be able to substitute instances of ' with ". It will then display correctly in the browser but you must remain cognizant when executing searches against the content you will need to perform the same substitution (e.g., "Hick's law" as a search term would become "Hick"s law" in the WHERE clause statement).

PHP probably has a built in for escaping HTML so I'd recommend looking for that before handling this yourself.

How can I escape single quotes in this scenario?

2 Answers2