MySQL: Searching for URI

MySQL: Searching for URI_Escaped text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marc Bissonnette - 31 Jul 2008 06:50 GMT

Hi all;

I'm hoping one of you have the answer to this little dillema:

Column `desc` of the table contants the full text description of items
within the data item to be searched. Since this is user-inputted data, it's
all URI Escaped.

If it wasn't escaped, I could do the query
SELECT * FROM `table` WHERE `desc` LIKE "car"
and only return items where the whole word "car" is present.

Of course, with it escaped, the word "car" is surrounded with %20's for
spaces. or at the beginning or end of the cell with no space either before
or after it.

So right now, I've got the script doing
SELECT * FROM `table` WHERE `desc` LIKE "%car%"

but of course that returns anything at all with the three letters c-a-r
next to each other.

I've tried
SELECT * FROM `table` WHERE (`desc` LIKE "car\%20" or `desc` LIKE "\%
20car" or `desc` LIKE "\%20car\%20")

and it returns nothing at all.

If someone could point me in the right direction, I'd be hugely
appreciative. I spent a couple of hours going through
http://dev.mysql.com/doc/
but (obviously) didn't find what I needed to find. Google wasn't my friend
either, though that is probably due to poor search queries ("search mysql
uri escaped data" etc)

Signature

Marc Bissonnette
Looking for a new ISP? http://www.canadianisp.com
Largest ISP comparison site across Canada.
Something to sell ? http://www.pennypinchernewspaper.com

Reply to this Message

sonic-news.com webmaster - 31 Jul 2008 13:28 GMT

> Hi all;
>
[quoted text clipped - 30 lines]
> either, though that is probably due to poor search queries ("search mysql
> uri escaped data" etc)

...WHERE LOCATE(substr,str)
concatenate the escape characters to the beginning and end of the long
string (and maybe convert it to one case), then search for the substring
with escape characters before and after.

Reply to this Message

Jerry Stuckle - 31 Jul 2008 13:47 GMT

> Hi all;
>
[quoted text clipped - 30 lines]
> either, though that is probably due to poor search queries ("search mysql
> uri escaped data" etc)

Marc,

I hope you don't have the %20 in your database. Even if it's input by
the user, it shouldn't be in the database. Rather, you should
urldecode() before storing the data in the database.

Signature

==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Reply to this Message

Andy Dingley - 31 Jul 2008 16:54 GMT

On 31 Jul, 06:50, Marc Bissonnette <dragnet\_@_/internalysis.com>
wrote:

> Column `desc` of the table contants the full text description of items
> within the data item to be searched. Since this is user-inputted data, it's
> all URI Escaped.

I think Jerry's statement is a little over-inclusive (you might be
storing HTML with URIs (as %URI;) in it after all, but this:

> "Since this is user-inputted data, it's all URI Escaped."

would suggest that this stuff isn't even a URI at all, it merely
collected that encoding when being submitted from the <form>. You
don't need to, and shouldn't, keep that stuff around URI-encoded any
longer that necessary. As you've noticed, it makes further processing
of it awkward.

Reply to this Message

John Bokma - 31 Jul 2008 17:34 GMT

> Hi all;
>
[quoted text clipped - 3 lines]
> within the data item to be searched. Since this is user-inputted data,
> it's all URI Escaped.

Then, like Jerry already wrote, you should unescape it before you insert
it into the database. (Also, I hope you're using the right MySQL module to
avoid SQL injections).

Signature

John Bokma http://johnbokma.com/

AISE/AWW/SEO/web development forum: http://seo-expert-wiki.com/

Reply to this Message

Marc Bissonnette - 31 Jul 2008 19:52 GMT

>> Hi all;
>>
[quoted text clipped - 7 lines]
> insert it into the database. (Also, I hope you're using the right
> MySQL module to avoid SQL injections).

Hrm, this would suggest that I probably am not:

In my script (perl):

use URI::Escape;
use DBI;

# a typical insertion routine looks like this:

$query="INSERT INTO `table` (";
for (@allvars) {
     $query.="`$_`,";
}
chop $query;
$query.=') VALUES (';
for (@allvars) {
     if ($in{$_} eq undef) {
          $query.='NULL,';
     } else {
          $in{$_}=uri_escape($in{$_});
          $query.="\"$in{$_}\",";
     }
}
$chop $query;
$query.=')';
my $dbh;
my $sth;
$dbh = DBI->connect("DBI:mysql:$dbname", $dbuser, $dbpass,{PrintError => 1,
RaiseError => 1});
$sth=$dbh->prepare($query);
$sth->execute();
$sth->finish;
$dbh->disconnect;

So to answer Jerry's question, yes, the string "See the dog run" would be
stored as "See%20the%20dog%20run"

or the string:

There is "quoted text" in this string

would be stored as:

There%20is%20%22quoted%20text%22%20in%20this%20string

I am willing to be educated as to how I should be doing this better :) :)

Signature

Marc Bissonnette
Looking for a new ISP? http://www.canadianisp.com
Largest ISP comparison site across Canada.
Something to sell ? http://www.pennypinchernewspaper.com

Reply to this Message

John Bokma - 31 Jul 2008 20:42 GMT

> In my script (perl):

use strict;
use warnings;

(since you have $chop $query somewhere below, I guess you don't have
those two lines?)

> use URI::Escape;
> use DBI;
[quoted text clipped - 6 lines]
> }
> chop $query;

you could have written that as:

$query .= join ',', map { "`$_`" } @allvars;

(saves you the chop to remove the ,)

> $query.=') VALUES (';
> for (@allvars) {
> if ($in{$_} eq undef) {
> $query.='NULL,';
> } else {
> $in{$_}=uri_escape($in{$_});

Why, oh why do you uri_escape this? To protect yourself against SQL
injection? Don't do this, but:

$query .=') VALUES (';

# add a place holder (?) for each value in @allvalues:
$query .= join ',', ( '?' ) x @allvalues;

> $query.=')';
> my $dbh;
[quoted text clipped - 3 lines]
> $sth=$dbh->prepare($query);
> $sth->execute();

replace with

$sth->execute( @allvalues );

> $sth->finish;
> $dbh->disconnect;

So in short (example, assuming $name and $address needs to be inserted):

my $query = 'INSERT INTO sometable ( name, address ) VALUES ( ?, ? )';
my $sth = $dbh->prepare( $query );
$sth->execute( $name, $address );
$sth->finish;

See perldoc DBI for more information. This protects you from SQL
injection. Also, note that DBI replaces undef with NULL (and vice
versa).

Signature

John Bokma http://johnbokma.com/

AISE/AWW/SEO/web development forum: http://seo-expert-wiki.com/

Reply to this Message

Marc Bissonnette - 31 Jul 2008 21:16 GMT

>
>> In my script (perl):
[quoted text clipped - 63 lines]
> injection. Also, note that DBI replaces undef with NULL (and vice
> versa).

Oh that is *very* cool - thank you very much.

I've got a fresh cup of coffee and am sitting down to do just that
(perldoc DBI)

Thanks again - I suspect this going to shrink the size of quite a few of
my db tables.

Signature

Marc Bissonnette
Looking for a new ISP? http://www.canadianisp.com
Largest ISP comparison site across Canada.
Something to sell ? http://www.pennypinchernewspaper.com

Reply to this Message

Webmaster Forum / General / Web Design / July 2008

MySQL: Searching for URI_Escaped text