Re: [GENERAL] Prepared statement performance...

Thank you for your explanation. But I still do not see how
>          INSERT INTO Users (username) VALUES ('joe'; DROP TABLE users');
will be evaluated so that it drops table 'users'. Actually, this should
evaluate to a syntax error, shouldn't it?

----- Original Message -----
From: "Toby" <toby@paperjet.com>
To: <pgsql-jdbc@postgresql.org>
Sent: Monday, October 14, 2002 9:59 AM
Subject: Re: [JDBC] [GENERAL] Prepared statement performance...


>
> > >I do not clearly understand what the problem is with someone typing in
> > >"foo'; DROP TABLE bar;" into the "Name" field on your web form.
>
> many sites take text directly from text boxes in a web form and submit
them
> to a database without escaping the string, thereby allowing unscrupulous
> people to execute SQL directly on your live production database.
>
> for example, if there was a form like below
>
> <form action="blah.jsp" method="post">
>    <b>Username:</b> <input type="text" name="username" value="joe'; DROP
> TABLE users">
> </form>
>
>
> then on the server you have blah.jsp which will handle the processing for
> the page, which might look something like
>
> String username = request.getParameter("username");
> Connection conn =
> DriverManager.getConnection("jdbc:postgresql:somedatabase", "someuser",
> "somepassword");
> Statement stmt = conn.createStatement();
>
> // and now the crucial line
> stmt.execute("INSERT INTO Users (username) VALUES ('" + username + "')");
>
>
>
> from the above, you can see that whatever is entered into the username
FORM
> item is executed directly on the database which, in this case, can cause
> the INSERT statement to actually perform the following
>
>          INSERT INTO Users (username) VALUES ('joe'; DROP TABLE users');
>
> This might cause the users table to be dropped. Not entirely sure if the
> above would actually do this, but a little bit of mucking about with what
> you type into the FORM field would certainly do this. Of course, you need

> to make sure the user you execute the SQL as (when you login to the
> database) has DROP permissions, but nonetheless the security concern is
clear.
>
> A simple way to prevent this is to use a PreparedStatement
>
> PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Users
> (username) VALUES (%)");
> pstmt.setString(1, username);
> pstmt.execute();
>
> This would escape the username string correctly thus executing the
> following SQL
>
>          INSERT INTO Users (username) VALUES ('joe''; DROP TABLE users');
>
> NOTE: the ' after the word "joe" has 2 ' not 1.
>
> In this case, the username would be exactly what the user typed. The
> database would not try to execute the DROP TABLE statement.
>
> I use this method but then I also sometimes escape the string myself by
> simply replacing all instances of a single apostrophe character (') with 2
> ('').  (NOTE: do not replace it with the " quote mark, use 2 apostrophe
> characters).
>
> I suspect someone will lambast me for escaping the string myself since
"why
> do it when the driver will do it for you". Well, sometimes i find it
> necessary of convenient.
>
> So there you go.
> By the way, I've noticed many sites that have this error. It's sloppy and
> unforgiveable.
>
> toby
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org