Re: [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