Thread: secure sql-statments

hi,
I want to make my web-app secure against evil sql-statments!

my sql-strings look like:

updateString = "update table_1 set col_1 = '" + postParam_1 + "'";
selectString = "select col_1 from table_1 where col_1 like '" + postParam + "'";
generalSelectString = postParam;

what characters do I have to quote, so that the client can't submit evil sql-statments?

ok: 2 characters i must quote: "'" -> "\'" and "\" -> "\\"
what characters do I need to quote else???
perhaps ";" -> "\;"

thanks
michi

> what characters do I have to quote, so that the client can't submit
> evil sql-statments?

I believe the only characters you need to escape for postgres are '\\'
and '\'', but it is easier to rely on the jdbc driver to do it for you
by using a prepared statement (assuming your using java 2):

PreparedStatement updateStatement = connection.prepareStatement
    ("update table_1 set col_1 = ?");
p.setString(1, postParam_1);

Doing it this way means there is less to worry about if you ever change
database backends (they might need differing characters escaped), and
the code has already had extensive testing.

Michael

Web Applications Developer
Open World Ltd, The Old Malthouse, Clarence Street, Bath, BA1 5NS.
Tel: +44 1225 444950                           Fax: +44 1225 336738
http://www.openworld.org/

Michi,

You should use PreparedStatements and you won't need to worry about
doing anything, as the driver will take care of all the work for you.

thanks,
--Barry

list@meinsenf.at wrote:

>
> hi,
> I want to make my web-app secure against evil sql-statments!
>
> my sql-strings look like:
>
> updateString = "update table_1 set col_1 = '" + postParam_1 + "'";
> selectString = "select col_1 from table_1 where col_1 like '" + postParam + "'";
> generalSelectString = postParam;
>
> what characters do I have to quote, so that the client can't submit evil sql-statments?
>
> ok: 2 characters i must quote: "'" -> "\'" and "\" -> "\\"
> what characters do I need to quote else???
> perhaps ";" -> "\;"
>
> thanks
> michi
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>