[sqlite] User input checking

[Steinar Midtskogen]

I'm planning to allow users to make database queries through a web
page. I'm thinking of letting the user provide the string that goes
between SELECT and FROM using the sqlite3 command tool, but what kind
of input checking is then needed?

Obviously, I need to check that the input doesn't contain any
semicolons, otherwise the user could enter something like "; DROP
big_table;". But is there anything else that needs checking? I only
want to make sure that the user can't change anything. If the query
is too big and will take forever, that's fine for now.

xkcd comes to mind: http://xkcd.com/327/ :)

--
Steinar
_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 22/03/12 22:57, Steinar Midtskogen wrote:
> I'm planning to allow users to make database queries through a web
> page. I'm thinking of letting the user provide the string that goes
> between SELECT and FROM using the sqlite3 command tool, but what kind
> of input checking is then needed?

Use the authorizer:

http://www.sqlite.org/c3ref/set_authorizer.html

Use limits to constrain possibilities:

http://www.sqlite.org/c3ref/limit.html

Call interrupt from a second thread to prevent runaway queries:

http://www.sqlite.org/c3ref/interrupt.html

> I need to check that the input ...

That approach requires you to have perfect code and not get outwitted. If
I was an attacker I'd be trying hex constants and unicode tricks.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9sKekACgkQmOOfHg372QTPfwCeK+axzhcmDtRrP+Lcd4R3EoWj
vhIAn1ed/MzltUXrpJmoY1MYMehqLDpl
=p6sE
-----END PGP SIGNATURE-----

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I also forgot to mention doing all that stuff on a second connection.
Open that one read-only (SQLITE_OPEN_READONLY).

You can also double check a statement makes no changes:

http://www.sqlite.org/c3ref/stmt_readonly.htmla

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9sKu4ACgkQmOOfHg372QT/WgCeMahjkI40RyhgvltXMfpE/cax
SPQAniSVzBeLj+KRIr33DyyOs/+RA0aa
=opYb

[Don V Nielsen]

Ruby on Rails -- ActiveRecord. ActiveRecord prevents sql injections, I
think. Using ERB in the html would give you that kind of functionality.
Correct?

dvn

[Roger Binns]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 23/03/12 06:33, Don V Nielsen wrote:
> Ruby on Rails -- ActiveRecord. ActiveRecord prevents sql injections,
> I think. Using ERB in the html would give you that kind of
> functionality. Correct?

I don't see the relevance of your comment. The requirement is for a user
to type some or all of an arbitrary SQL statement and then be able to
execute that statement against SQLite without "harm" happening.

Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk9szQcACgkQmOOfHg372QQNQQCfdxINH0/H3g6dXuwQ95l/G9zQ
040AoJolwVp5KpVmsZyMOQN4e0fmUn4H
=tkBO