这个简单的字符串转义是否可以阻止任何SQL注入？ [英] Can this simple String escaping prevent any SQL Injections?

问题描述

我在一家公司工作，负责数据库模块的人严格禁止使用预准备语句。我担心他的实现不安全。

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.

这是我们目前用于进行SQL查询的代码（带有JDBC / MySQL 5.5的Java 8应用程序）：

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

编辑：
不幸的是我无法显示executeSQL（），因为有一个庞大的类层次结构混乱，一切都是分散的。但它归结为这样：

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

转移0.01

至：02020.020202.200202

名称：johnny tables'; drop table foobar -

transfer 0.01
to: 02020.020202.200202
name: johnny tables';drop table foobar --

将被转义为

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.

传输0.01

to：02020.020202.200202

名称：johnny tables'; drop table foobar--

schedule：1天后现在

transfer 0.01
to: 02020.020202.200202
name: johnny tables';drop table foobar--
schedule: 1 day from now

在db中存储订单

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

将存储为

johnny tables'; drop table foobar -

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. *

当您进入手动路线时，您必须确保转义变量的所有，每个实例，并考虑所有unicode字符，并考虑数据库引擎的所有独特性。

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.

* 5月实际上并不工作，取决于实际查询，连接，工会等..这是一个非常简单的例子

这篇关于这个简单的字符串转义是否可以阻止任何SQL注入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！