Sql safe strings in java w/o prep'd statements

Question

Keeping prepared statements aside, Alternatively I want to "stay safe" from sql injection in java...

I thought of doing this (a htmlentity conversion) ?

suspectedInputvariable.replace("'","&#39;")
                      .replace(";","&#359;")
                      .replace("\"","&#34;");

is suspectedInputvariable now safe to be embedded with a sql query?

I wrote a separate class where i just issue a query and nothing more arguments — everlasto
– everlasto, Commented Mar 24, 2012 at 11:53
@everlasto: That's not a reason not to use a prepared statement. — Michael Borgwardt
– Michael Borgwardt, Commented Mar 24, 2012 at 12:02

Joni · Accepted Answer · 2012-03-24 12:11:03Z

3

First, why would you want to do such a thing? The driver knows how to safely treat strings. Just use a PreparedStatement.

Second, you have to escape \ and some other characters, too. If you handle all the characters listed here your code should be reasonably safe with MySQL: http://dev.mysql.com/doc/refman/4.1/en/mysql-real-escape-string.html The list of characters for other databases may differ.

edited Mar 24, 2012 at 12:11

answered Mar 24, 2012 at 11:52

Joni

112k14 gold badges151 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

everlasto Over a year ago

eager to know if possible w/o ppd stt .. and there are ways to escape backslashes too

Joni Over a year ago

Of course it is possible, but it's easy to get it wrong. For example PHP has mysql_escape_string, that mostly works, until someone figured out that it can be fooled by unicode strings. Now they recommend mysql_real_escape_string. The risk of having your database open to all imaginable queries is just too scary.

everlasto Over a year ago

Thanks Joni, for now just have to escape only \x00, \n, \r, \, ', " and \x1a until someone figures out the next hack ;) .. if i could find something than ppd stt, it will drastically reduce my time.. tats y im interested..

Joni Over a year ago

You could create the prepared statement once and hold it in a cache. They are meant to be reused with different parameters.. or does the sql itself vary? (list of fields, tables, where-conditions..)

Michael Borgwardt Over a year ago

@everlasto: it's hard to even imagine a scenario where prepared statements, correctly used, cause any significant performance problems.

Michael Borgwardt · Accepted Answer · 2012-03-24 12:01:42Z

2

is suspectedInputvariable now safe to be embedded with a sql query?

Probably not. There are all kinds of little-known features in various SQL dialects that could be used to circumvent this blacklist.

Just use prepared statements. Period.

answered Mar 24, 2012 at 12:01

Michael Borgwardt

347k81 gold badges491 silver badges726 bronze badges

1 Comment

everlasto Over a year ago

so ppd stts seems to be the final destination :/

Collectives™ on Stack Overflow

Sql safe strings in java w/o prep'd statements

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related