×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

quoting sql strings

quoting sql strings

quoting sql strings

(OP)

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.

RE: quoting sql strings

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

RE: quoting sql strings

(OP)
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.

RE: quoting sql strings

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

RE: quoting sql strings

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

--------------------------------------------------
Free Database Connection Pooling Software
http://www.primrose.org.uk

RE: quoting sql strings

(OP)
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.

RE: quoting sql strings

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:
www.securiteam.com/securityreviews/5DP0N1P76E.html

Chip H.

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

RE: quoting sql strings

Interesting, thanks guys !

--------------------------------------------------
Free Database Connection Pooling Software
http://www.primrose.org.uk

RE: quoting sql strings

(OP)
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.

RE: quoting sql strings

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:

CODE

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:

CODE

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/5KP0N1PC...

Chip H.

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

RE: quoting sql strings

(OP)
''' 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.

RE: quoting sql strings

(OP)
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.

RE: quoting sql strings

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

RE: quoting sql strings

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close