if I allow anyone on the web to run SQL queries against my database, what are the obvious attacks hackers will try?

[lawrence k]

Okay, I just backed up my database, just in case.

The whole schema for the database is here:

http://www.accumulist.com/index.php?whatPage=db.php

You can run any SELECT query against this database that you want, and
send it as a GET request. This would be an example:

http://www.accumulist.com/output.php...rom%20tagCloud
The function that returns this checks to query to see if it contains
the words ALTER, DROP, EMPTY, GRANT, UPDATE, INSERT, and a bunch of
others. It calls die() if it sees any of those words.

For obvious reasons, I'm trepidatious about exposing the database to
this degree. What are some of the obvious, and not so obvious, attacks
that I shoudl expect and defend against?

[fletch]

DOS is simple enough

select * from table1,table2,...tableN

Will cause a cross product to be calculated.If each of three tables has
10 rows, the query above will return 10^3=1000 rows.

Postgresql also does TRUNCATE - not sure about mysql.

MySQL has good permissions, you could connect to the db as a different
user and with only a limited set of permissions.

What about functions?

select LOAD_FILE('/etc/passwd');'

[Geoff Berrow]

Message-ID: <11**********************@t31g2000cwb.googlegroups .com> from
lawrence k contained the following:

The function that returns this checks to query to see if it contains
the words ALTER, DROP, EMPTY, GRANT, UPDATE, INSERT, and a bunch of
others. It calls die() if it sees any of those words.

The general advice seems to be to ban everything that is not allowed
rather than to allow anything which is not banned.

I think I'd construct the query in code from the input variables, after
having sanitised the input using mysql_real_escape_string()

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Good Man]

"lawrence k" <lk******@geocities.com> wrote in
news:11**********************@t31g2000cwb.googlegr oups.com:

The function that returns this checks to query to see if it contains
the words ALTER, DROP, EMPTY, GRANT, UPDATE, INSERT, and a bunch of
others. It calls die() if it sees any of those words.

For obvious reasons, I'm trepidatious about exposing the database to
this degree. What are some of the obvious, and not so obvious, attacks
that I shoudl expect and defend against?

a question i have as an outsider is, why are you doing this in the first
place?

as mentioned in another post, how would you possibly guard against a DOS
attack?

fundamentally poor design.

[lawrence k]

Good Man wrote:

"lawrence k" <lk******@geocities.com> wrote in
news:11**********************@t31g2000cwb.googlegr oups.com:

The function that returns this checks to query to see if it contains
the words ALTER, DROP, EMPTY, GRANT, UPDATE, INSERT, and a bunch of
others. It calls die() if it sees any of those words.

For obvious reasons, I'm trepidatious about exposing the database to
this degree. What are some of the obvious, and not so obvious, attacks
that I shoudl expect and defend against?

a question i have as an outsider is, why are you doing this in the first
place?

So that outsiders can get the information in formats that I'd never
dream of. If I write every query myself, it forecloses the thing I want
most, which is people doing stuff with the contents of Accumulist that
I myself would never think of.

I've a long term goal of writing the whole database out every hour as a
giant RDF file, with all the relationships made explicit, and that
might allow the amount of spontaneous invention by outsiders that I'm
hoping for. But till then, I'm looking for an easier way to enable
this.

The simplest thing is for me to allow others to write their own SQL and
then for outsiders to pass in text files describing how they want the
output formatted.

If I can't make this secure, then I'll just write everything out as a
simple, huge XML file and let people use that.

[lawrence k]

fletch wrote:

DOS is simple enough

select * from table1,table2,...tableN

Will cause a cross product to be calculated.If each of three tables has
10 rows, the query above will return 10^3=1000 rows.
Right. That leads me into making rules, which is discouraging. I can
see the complexity of the rules rapidly expanding and me still missing
most of the important possible attacks.
MySQL has good permissions, you could connect to the db as a different
user and with only a limited set of permissions.
I like that idea. Do you have suggestions of what would constitute a
minimal set of permissions that would still enable outsiders to make
queries that I can think of?
What about functions?

select LOAD_FILE('/etc/passwd');'

I've added a lot of the functions to the forbidden list, I'll probably
end up banning 99% of them.
Or maybe I'll just put all the data in an XML file. This seems,
otherwise, too hard.

[fletch]

I like that idea. Do you have suggestions of what would constitute a
minimal set of permissions that would still enable outsiders to make
queries that I can think of?

Not really - I don't know enough to be authoritative on this.

[Gordon Burditt]

>> MySQL has good permissions, you could connect to the db as a different

user and with only a limited set of permissions.

I like that idea. Do you have suggestions of what would constitute a
minimal set of permissions that would still enable outsiders to make
queries that I can think of?

For read-only access to tables, a user needs SELECT (probably on
one database only) and possibly CREATE TEMPORARY TABLES (which is
sometimes needed implicitly for ORDER BY). This presumes that
you supply the tables and the data, created by an account that has
more privileges. This doesn't prevent running your database out
of disk space with temporary tables.

If you want to allow the user to alter data, but not the tables,
SELECT, INSERT, UPDATE, and DELETE privilege on one database, along
with CREATE TEMPORARY TABLES is probably sufficient. This does allow
them to wipe out any sample data and run your database out of disk
space.

This does not prevent hammering the db with queries (there are some
rate-limiting features for that) or loading down the server with
joins that create huge numbers of rows in the result.

What about functions?

select LOAD_FILE('/etc/passwd');'

This requires FILE privilege to read files on the server.
This is a privilege you shouldn't hand out lightly.

Gordon L. Burditt