quoting sql strings 1

[ericbrunson]

I inherited a J2EE app and I'm getting a lot of mysql errors when there's a quote or an apostrophe in the value of a field to be inserted or updated.

Is there a nice java function that I can wrap my field values in that would properly protect these symbols from the interpretter?

Thanks,
e.

 

[chiph]

You can use a PreparedStatement object, which should handle that for you.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ericbrunson]

That would involve a great deal of recoding. I'm going with field.replaceall( "'", "''" ) unless there's a more customized solution. Ohio State has a nice class called SQLString, I'll probably use that if I'm ever comdemned to write Java.

 

[chiph]

OK, but be aware that you're leaving yourself open to a SQL Injection attack.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[sedj]

Chip, what is a "SQL Injection attack." ?


--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[ericbrunson]

I think he's talking about something like this.

I build a sql statement like:

String sql = "SELECT * FROM MYTAB " +
"WHERE KEY = '" + keyterm + "'";

If someone submits this as keyterm to my jsp:

somekey'; delete from mytab; select * from mytab where key = 'somekey

Notice how the quotes like up with mine to create 3 valid sql statements.

This would a) require an intimate knowlege of my schema and code, b) require that the mysql connector will allow you to execute three commands in a row like that (though you can get much fancier with the attacks) and c) assume that I didn't properly quote the string before I executed it.

If you properly protect the quotes, then the attack is void.

 

[chiph]

Actually, depending on the database engine used, you can reveal a target's schema in order to provide more information for further attacks.

There used to be a better walkthru of an attack, but this one is pretty good too:

http://www.securiteam.com/securityreviews/5DP0N1P76E.html

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[sedj]

Interesting, thanks guys !

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[ericbrunson]

Interesting article, but all based on the ability to close the quotes and start a new command. So, if I'm properly quoting the string, there's no risk. The further exploits also depend on the insecurity of MS builtins, which don't affect me.

 

[chiph]

OK, here's an example.

In order to allow a customer named O'Reilly, you double up the single quotes in code and do a string concatenation to build your SQL statement. The database is now happy and the values go in OK. The DB saw:

INSERT INTO tbl_Customer 
(Name) 
VALUES 
('O''Reilly');

An attacker enters ');DROP tbl_GoodStuff;-- into your application. After doubling the quotes up, the database sees:

INSERT INTO tbl_Customer 
(Name) 
VALUES 
(''');DROP tbl_GoodStuff;--');

So, the single quote gets opened, doubled, and closed. The semicolon terminates the previous statement, and the DROP statement executes. The double-dash is the ANSI SQL comment character, so the rest of the statement gets ignored.

While the link I pointed out earlier is specifc to MS SQLServer, doing a Google reveals that just about any database is susceptible to this, including MySQL:

http://www.securiteam.com/securityreviews/5KP0N1PC1W.html

Chip H.

____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ericbrunson]

''' is not a valid string in mysql, it is an unterminated quoted string containing an escaped single quote. But you're right, there are probably plenty of scenarios I haven't considered.

I did some research and realized that when you suggested PreparedStatements you were talking about a java class. I thought you were referring to some sort of MySQL stored procedure or something.

I will definitely look into them, I've got the docs here now and it looks like what I need.

Thanks,
e.

 

[ericbrunson]

I took chiph's advice and converted some key actions over to using PreparedStatements and it worked. like a charm. Thanks.

It turns out the problem wasn't even the quotes, it was actually choking on Spanish titles for music, but the PreparedStatements handle it beautifully.

 

[chiph]

Glad to hear that it worked for you.

A side-benefit of using PreparedStatements is potentially faster execution.

The database is sometimes able to cache your SQL statements, and if they were "prepared", then the database has less work to do because it no longer has to check the SQL for validity (make sure the tables & columns exist, make sure the syntax is correct, etc) prior to execution.

It's not as fast as a stored procedure would be of course, but it's still a nice benefit to have. ;-)

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ericbrunson]

I have already benefited from that feature. One of the operations I converted was iterating over music tracks so I was able to prepare the SQL and bind the artist and title fields in the loop.

Thanks again.