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