java防sql注入 转义,这个简单的字符串转义是否可以阻止任何SQL注入？

I'm working at a company where the person responsible for the database module is strictly against using prepared statements. I'm worrying that his implementation is not secure.

Here is the code we are currently using to make a SQL query (Java 8 Application with JDBC/MySQL 5.5):

String value = "Raw user input over HTTP-Form";

String sql = "SELECT * FROM db1.articles WHERE title like '" +

replaceSingleQuotes(value) + "'";

executeSQL(sql);

public static String replaceSingleQuotes(String value) {

value = value.replaceAll("\\\\", "\\\\\\\\");

return value.replaceAll("'", "\\\\'");

}

I was not able to come up with any injections but his solution seems very fishy to me. Can anyone point out any way how this escaping could be circumvented? He will not replace his code if I can't come up with anything and we have very sensitive information of thousands of customers in our application (banking).

Edit:

Unfortunately i can't show executeSQL() because there is a mess with a huge class hierarchy and everything is scattered. But it comes down to something like this:

String query = ... // query escaped with the above function

java.sql.Connection connection = ...

Statement stmt = connection.createStatement();

stmt.executeUpdate(query);

解决方案

One method of attack would be by "loading" the attack.

First, you inject as user name, bank transfer message, whatever into it

transfer 0.01

to: 02020.020202.200202

name: johnny tables';drop table foobar --

will be escaped to

johnny tables\';drop table foobar --

So far so good. protection in effect. our attach failed. We try the loading attack.

Now we are going to make a scheduled payment order.

This is assuming a common error is made, that once inserted in the database, the value is "safe" because it has been checked once.

transfer 0.01

to: 02020.020202.200202

name: johnny tables';drop table foobar--

schedule: 1 day from now

Storing the order in the db

'johnny tables\';drop table foobar--'

will be stored as

johnny tables';drop table foobar--

Now at midnight the scheduler kicks in and starts iterating the scheduled payments

select name from scheduled where time > x and < y

so the bank code starts to chrunch

String name = result['name'];

String acct = result['acct'];

String amt = result['amt'];

string query = "insert into payment_process (name,acct,amt) values('"+name+"','"+acct+"','"+amt+"');

and boom, your table is dropped. *

When you go the manual route, you have to ensure all, each and every instance of the variable is escaped, that all unicode characters are accounted for, that all idiocrancies of the database engine are accounted for.

Also, using prepared statements can give a significant speed boost, because you don't have to rebuild queries. You can just build them once, store them in a cache and just swap out the parameters.

Especially when iterating large lists they are a godsent.

The root problem is that he probably doesn't understand prepared statements, doesn't get them how they work. Insecurity triggered can make aggressive and protective of a certain way, even fanatical, just to prevent to admit you just don't know how they work.

Try to talk to him about it, if he doesn't wish to listen to reason go to his manager and explain the issue, and that if the site/app gets hacked, it will be on the head of your co-worker and your manager, and tell him the risks are HUGE. Point to the recent hacks where a lot of money was stolen like the swift hack.

* May not actually work, dependent on actual query, joins, unions etc.. it's a very simplified example