[thelist] MySQL boolean search

[Means, Eric D]

-----Original Message-----
From: Nickolay Kolev [mailto:nmkolev at uni-bonn.de]
Sent: Thursday, October 03, 2002 8:16 AM
To: thelist at lists.evolt.org
Subject: [thelist] MySQL boolean search

> What bothers me are the security issues mentioned there. What are the
> dangers in not checking the user input and waht can be done about that?

The simplest answer is to escape any strings the user can enter.

For instance, in most DBs you can escape a quotation mark in a string by
doubling it up.  For instance, to correctly insert the string
it's a small world
into a varchar field, you'd need to do the following:
insert into mytable (myfield) values ('it''s a small world');

The double apostrophes tell the DB that you want to insert a single apos,
not end the string.  Most programming languages provide functions to do
this.  In VB:
strSQL = Replace$(strSQL, "'", "''") 	' do this before putting the
							'single quotes
around the string!

In php, you can do mysql_escape_string() (IIRC).

So when the user tries to enter malicious SQL, like:
a field'; DROP TABLE mytable;
you'll end up with the following statement:
select <columns> from mytable where field like 'a field''; DROP TABLE
mytable;'

So the user will end up searching for the malicious statement they thought
they'd get to execute.  Poor them.

Also, note that some programming languages and/or DBs have built-in
protection (to a degree) for this; IIRC the function mysql_query won't allow
you to perform multiple statements; only the first statement would be
executed.  So in the above example, even if you didn't escape the string
(NOT recommended), MySQL would only execute the select anyway.

Finally, proper DB permissions are a must.  Unless absolutely necessary,
queries which can be run by external (untrusted) users (and even usually
those run by trusted users; mistakes know no trust) should NEVER be run by a
user with DROP (or even DELETE) permissions.  In many cases you can limit
that user to SELECT only without hurting functionality.  That way, even if
something does get through, the DB won't let them do anything harmful.