how does one normally search for text in a database that might have slashes applied to quotes?

A user writes this sentence: "It was the New Urbanist's nightmare of
sprawl run amok."

They input that and my PHP script hits it with addslashes() and then
the sentence gets put in the database. A slash is likely inserted in
front of the quote mark in "Urbanist's".

Later the user runs a search of the database for "New Urbanist's
nightmare".

Even if I run this query using LIKE, nothing will come back?

Should I hit the text with addslashes before I run the search?

How do most coders handle this?

re: how does one normally search for text in a database that might have slashes applied to quotes?

On 28 Sep 2004 12:02:02 -0700, lkrubner@geocities.com (lawrence) wrote:
[color=blue]
>A user writes this sentence: "It was the New Urbanist's nightmare of
>sprawl run amok."
>
>They input that and my PHP script hits it with addslashes() and then
>the sentence gets put in the database. A slash is likely inserted in
>front of the quote mark in "Urbanist's".[/color]

If it is, it should only be for databases that stuff values into SQL (a poor
design choice). For example, MySQL.

The idea is that this then results in the original value being inserted into
the database - the addslashes() is only to encode it as being suitable for the
SQL statement, the slashes should not be stored. If they are, it's been
addslashed too many times.
[color=blue]
>Later the user runs a search of the database for "New Urbanist's
>nightmare".
>
>Even if I run this query using LIKE, nothing will come back?[/color]

Only if you've mangled the data by escaping too many times.
[color=blue]
>Should I hit the text with addslashes before I run the search?[/color]

It depends on your database. Maybe you would, but only so that the un-slashed
version gets searched. This can get complicated further because LIKE may
support escapes as well - so this may become database specific.
[color=blue]
>How do most coders handle this?[/color]

<rant>
Hopefully by using a database or database abstraction layer that supports
placeholders and binding data separately from the SQL statement, avoiding the
whole issue entirely and reducing the risk of making a mistake and leaving
yourself open to SQL injection, and depending on database, severe performance
issues.
</rant>

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

re: how does one normally search for text in a database that might have slashes applied to quotes?

lawrence schrieb:
[color=blue]
> Later the user runs a search of the database for "New Urbanist's
> nightmare".
>
> Even if I run this query using LIKE, nothing will come back?
>
> Should I hit the text with addslashes before I run the search?[/color]

Yes. Or if you use MySQL use mysql_escape_string().
[color=blue]
> How do most coders handle this?[/color]

Some don't escape the special chars. They are likely to be under attack
very fast.

Regards,
Matthias

re: how does one normally search for text in a database that might have slashes applied to quotes?

*** lawrence escribió/wrote (28 Sep 2004 12:02:02 -0700):[color=blue]
> They input that and my PHP script hits it with addslashes() and then
> the sentence gets put in the database. A slash is likely inserted in
> front of the quote mark in "Urbanist's".
>
> Later the user runs a search of the database for "New Urbanist's
> nightmare".
>
> Even if I run this query using LIKE, nothing will come back?
>
> Should I hit the text with addslashes before I run the search?[/color]

Maybe I don't understand what you mean, but I can't see what the problem
is. Escaping quotes doesn't affect the content, it's just a way to allow
you to type quotes inside a quoted string.

$foo='O\'Brian';

This code creates a string with O'Brian as content, not O\'Brian.

If you don't escape the quotes when composing your SQL query, you'll just
get a syntax error:

SELECT * FROM table WHERE sentence LIKE 'New Urbanist's nightmare'



--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ Las dudas informáticas recibidas por correo irán directas a la papelera
-+ I'm not a free help desk, please don't e-mail me your questions
--

re: how does one normally search for text in a database that might have slashes applied to quotes?

Andy Hassall <andy@andyh.co.uk> wrote in message news:<65fjl0dulb34gnsscb6u67dek718od0ebg@4ax.com>. ..[color=blue]
>[color=green]
> >How do most coders handle this?[/color]
>
> <rant>
> Hopefully by using a database or database abstraction layer that supports
> placeholders and binding data separately from the SQL statement, avoiding the
> whole issue entirely and reducing the risk of making a mistake and leaving
> yourself open to SQL injection, and depending on database, severe performance
> issues.
> </rant>[/color]


That's tantalizing but doesn't actually answer the question. What do
you normally do? Which database do you use? How do you insert data?
Can you give me an example so I have some sense of what the
alternative looks like?

re: how does one normally search for text in a database that might have slashes applied to quotes?

Matthias Esken <muelleimer2004nospam@usenetverwaltung.org> wrote in message news:<cjcpci.1h4.1@usenet.esken.de>...[color=blue][color=green]
> > How do most coders handle this?[/color]
>
> Some don't escape the special chars. They are likely to be under attack
> very fast.[/color]

How would outsiders know? If it is an open source project (and mine
is) then they outsiders could read the code and see the weakness, but,
speaking hypothetically, if I wasn't giving my code away for free, how
would outsiders find out about the weakness? Perhaps you mean a
malicious internal user would eventually launch an attack?

re: how does one normally search for text in a database that might have slashes applied to quotes?

"Alvaro G. Vicario" <kAlvaroNOSPAMTHANKS@terra.es> wrote in message news:<qi882k8hqs1j$.b2vxsi580ak1.dlg@40tude.net>.. .[color=blue][color=green]
> > Should I hit the text with addslashes before I run the search?[/color]
>
> Maybe I don't understand what you mean, but I can't see what the problem
> is. Escaping quotes doesn't affect the content, it's just a way to allow
> you to type quotes inside a quoted string.
>
> $foo='O\'Brian';
>
> This code creates a string with O'Brian as content, not O\'Brian.[/color]

Maybe I should play around with my Linux box more and then I'd
understand these things, but logging into my MySql database over the
web using phpMyAdmin I see the slashes still there, or if I pull text
out of the database and don't hit them with stripslashes then the
slashes are still there. I've the impression that the slashes are
stored in the database.

re: how does one normally search for text in a database that might have slashes applied to quotes?

*** lawrence escribió/wrote (29 Sep 2004 12:01:42 -0700):[color=blue]
> Maybe I should play around with my Linux box more and then I'd
> understand these things, but logging into my MySql database over the
> web using phpMyAdmin I see the slashes still there, or if I pull text
> out of the database and don't hit them with stripslashes then the
> slashes are still there. I've the impression that the slashes are
> stored in the database.[/color]

There's a PHP configuration issue that's pretty confusing. PHP has some
options to automatically add slashes to form variables. It's called "magic
quotes". It can be the reason for finding slashes where there shouldn't be.
Check the output of phpinfo() for magic quotes.

Also, it's interesting to note that not al database management systems use
the same escaping system. MySQL uses \' while SQL server uses ''
(duplicating quotes).

--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ Las dudas informáticas recibidas por correo irán directas a la papelera
-+ I'm not a free help desk, please don't e-mail me your questions
--

re: how does one normally search for text in a database that might have slashes applied to quotes?

*** lawrence escribió/wrote (29 Sep 2004 11:59:52 -0700):[color=blue]
> How would outsiders know?[/color]

If you have a computer with a public IP you're getting all sort of random
attack attempts all the time. Some people _are_ bored.

In any case, if you are willing to break a web site SQL injection is
probably your first guess. Also, not escaping quotes can cause errors not
related to attacks: a user can type a quote for many reasons.



--
-+ Álvaro G. Vicario - Burgos, Spain
+- http://www.demogracia.com (la web de humor barnizada para la intemperie)
++ Las dudas informáticas recibidas por correo irán directas a la papelera
-+ I'm not a free help desk, please don't e-mail me your questions
--

re: how does one normally search for text in a database that might have slashes applied to quotes?

On 29 Sep 2004 11:57:18 -0700, lkrubner@geocities.com (lawrence) wrote:
[color=blue]
>Andy Hassall <andy@andyh.co.uk> wrote in message news:<65fjl0dulb34gnsscb6u67dek718od0ebg@4ax.com>. ..[color=green]
>>[color=darkred]
>> >How do most coders handle this?[/color]
>>
>> <rant>
>> Hopefully by using a database or database abstraction layer that supports
>> placeholders and binding data separately from the SQL statement, avoiding the
>> whole issue entirely and reducing the risk of making a mistake and leaving
>> yourself open to SQL injection, and depending on database, severe performance
>> issues.
>> </rant>[/color]
>
>
>That's tantalizing but doesn't actually answer the question. What do
>you normally do? Which database do you use? How do you insert data?
>Can you give me an example so I have some sense of what the
>alternative looks like?[/color]

I use Oracle at work: http://www.php.net/manual/en/ref.oci8.php

An examples of using placeholders is here:

http://www.php.net/manual/en/function.ocibindbyname.php

The key is that you can bind any value you like and don't even need to think
about escaping - the value you bind is what is stored, end of story.

MySQL doesn't support placeholders natively, but you can use a database
abstraction layer on top to emulate them, giving you the extra safety that they
provide:

e.g.
http://pear.php.net/manual/en/packag...on.prepare.php

Or write your own - anything to avoid embedding values directly into SQL
directly in various places - better to have it all in one place, so if escaping
needs to be done it's done behind the scenes consistenly, and not directly by
the caller where it might be forgotten or done wrong.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

re: how does one normally search for text in a database that might have slashes applied to quotes?

lawrence wrote:
[color=blue]
> Matthias Esken <muelleimer2004nospam@usenetverwaltung.org> wrote in message news:<cjcpci.1h4.1@usenet.esken.de>...[color=green][color=darkred]
>>> How do most coders handle this?[/color]
>>
>> Some don't escape the special chars. They are likely to be under attack
>> very fast.[/color]
>
> How would outsiders know?[/color]

When I try to attack a website, SQL injection is one of the first steps.

Regards,
Matthias

re: how does one normally search for text in a database that might have slashes applied to quotes?

Andy Hassall <andy@andyh.co.uk> wrote in message news:<g15ml0tshci2gupiubppksjttm0ldrd2p1@4ax.com>. ..[color=blue][color=green]
> >lkrubner wrote:
> >That's tantalizing but doesn't actually answer the question. What do
> >you normally do? Which database do you use? How do you insert data?
> >Can you give me an example so I have some sense of what the
> >alternative looks like?[/color]
>
> I use Oracle at work: http://www.php.net/manual/en/ref.oci8.php
>
> An examples of using placeholders is here:
>
> http://www.php.net/manual/en/function.ocibindbyname.php
>
> The key is that you can bind any value you like and don't even need to think
> about escaping - the value you bind is what is stored, end of story.[/color]

I can't use this particular idea because I'm using an abstraction
layer that needs to avoid database specific stuff like this. I do wish
I knew more about placeholders though, as a concept.



[color=blue]
> MySQL doesn't support placeholders natively, but you can use a database
> abstraction layer on top to emulate them, giving you the extra safety that they
> provide:
>
> e.g.
> http://pear.php.net/manual/en/packag...on.prepare.php
>
> Or write your own - anything to avoid embedding values directly into SQL
> directly in various places - better to have it all in one place, so if
> escaping needs to be done it's done behind the scenes consistenly, and not
> directly by
> the caller where it might be forgotten or done wrong.[/color]

I've written my own abstraction layer so my code never knows what
datastore it is talking to, but I'm still having trouble with the
concept placeholders. It seems when the underlying database is MySql
all I can hope to do is allow the abstraction layer to addslash() and
stripslash() consistently. And even that can not be very consistent
because the software is often used in hosted environments where the
user won't be able to edit their own php.ini file and therefore can't
control whether magic_quotes is on or off.

re: how does one normally search for text in a database that might have slashes applied to quotes?

Matthias Esken <muelleimer2004nospam@usenetverwaltung.org> wrote in message news:<cjfku2.1a0.1@usenet.esken.de>...[color=blue]
> lawrence wrote:
>[color=green]
> > Matthias Esken <muelleimer2004nospam@usenetverwaltung.org> wrote in message news:<cjcpci.1h4.1@usenet.esken.de>...[color=darkred]
> >>> How do most coders handle this?
> >>
> >> Some don't escape the special chars. They are likely to be under attack
> >> very fast.[/color]
> >
> > How would outsiders know?[/color]
>
> When I try to attack a website, SQL injection is one of the first steps.[/color]

Can you tell me how it works? I've asked in the past for details on
sites h-zone but the kids who hang out there just giggle and never
give answers. I think they only share information in chat.

Let's put it this way, when someone is attacking my site, what does it
look like in my logs? What should I look for?

If you've got an ip address and you happen to know that the server
with that ip address is running red hat linux 9 and a bunch of other
services (secure telnet, normal ftp, apache, sendmail, a bunch of
other stuff) what weaknesses do you probe for and how? If you know the
ip address of a server that acts exclusively as a MySql server, what
kind of attack can you launch?

re: how does one normally search for text in a database that might have slashes applied to quotes?

"Alvaro G. Vicario" <kAlvaroNOSPAMTHANKS@terra.es> wrote in message news:<8qq8mlti79el$.od8wgun0c1ah.dlg@40tude.net>.. .[color=blue]
> *** lawrence escribió/wrote (29 Sep 2004 12:01:42 -0700):[color=green]
> > Maybe I should play around with my Linux box more and then I'd
> > understand these things, but logging into my MySql database over the
> > web using phpMyAdmin I see the slashes still there, or if I pull text
> > out of the database and don't hit them with stripslashes then the
> > slashes are still there. I've the impression that the slashes are
> > stored in the database.[/color]
>
> There's a PHP configuration issue that's pretty confusing. PHP has some
> options to automatically add slashes to form variables. It's called "magic
> quotes". It can be the reason for finding slashes where there shouldn't be.
> Check the output of phpinfo() for magic quotes.[/color]

Yes. My software has to work in hosted environments where the user may
now have access to their own php.ini file. I should test more, in the
code, to see if magic quotes are on. I think its on the list of things
to do, but its pretty far down the list. When I get a good idea for my
software it takes me a while to implement - its 1.5 megs of PHP code,
which is tough to revamp in a hurry. There is about a million ways I'd
like to clean it up, and having the code test for more of the php.ini
settings is one of those things I'd love to do.


[color=blue]
> Also, it's interesting to note that not al database management systems use
> the same escaping system. MySQL uses \' while SQL server uses ''
> (duplicating quotes).[/color]

Good point.