准备好的语句如何防范 SQL 注入攻击?

发布于 2024-12-17 16:37:50 字数 357 浏览 1 评论 0原文

准备好的语句如何帮助我们预防SQL 注入 攻击?

维基百科说:

准备好的语句能够抵御 SQL 注入,因为 参数值,稍后使用不同的方式传输 协议,不需要正确转义。如果按照原来的说法 模板不是从外部输入派生的,SQL注入不能 发生。

我不太明白其中的原因。用简单的英语和一些例子进行简单的解释是什么?

How do prepared statements help us prevent SQL injection attacks?

Wikipedia says:

Prepared statements are resilient against SQL injection, because
parameter values, which are transmitted later using a different
protocol, need not be correctly escaped. If the original statement
template is not derived from external input, SQL injection cannot
occur.

I cannot see the reason very well. What would be a simple explanation in an easy English and some examples?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(10

泪冰清 2024-12-24 16:37:50

这个想法非常简单 - 查询和数据分别发送到数据库服务器。
就这样。

SQL注入问题的根源在于代码和数据的混合

事实上,我们的SQL查询是合法程序
我们正在动态创建这样一个程序,动态添加一些数据。因此,数据可能会干扰程序代码,甚至改变它,正如每个 SQL 注入示例所示(所有示例都在 PHP/Mysql 中):

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

将生成常规查询,

SELECT * FROM users where id=1

而此代码

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

将生成恶意查询 它之所以

SELECT * FROM users where id=1; DROP TABLE users;

有效,是因为我们将数据直接添加到程序主体中,并且它成为程序的一部分,因此数据可能会改变程序,并且根据传递的数据,我们将有一个常规输出或一个表<代码>用户已删除。

虽然对于准备好的语句,我们不会更改我们的程序,但它保持不变
这就是重点。

我们首先向服务器发送一个程序

$db->prepare("SELECT * FROM users where id=?");

,其中的数据被一些称为参数或占位符的变量替换。

请注意,完全相同的查询被发送到服务器,其中没有任何数据!然后我们使用第二请求发送数据,基本上与查询本身分开:

$db->execute($data);

因此它不能改变我们的程序并造成任何损害。
很简单——不是吗?

我必须添加的唯一一件事在每个手册中总是被省略:

准备好的语句只能保护数据文字,但不能与任何其他查询部分一起使用。
因此,一旦我们必须添加动态标识符(例如字段名称),准备好的语句就无法帮助我们。我最近解释过此事,所以我不再重复。

The idea is very simple - the query and the data are sent to the database server separately.
That's all.

The root of the SQL injection problem is in the mixing of the code and the data.

In fact, our SQL query is a legitimate program.
And we are creating such a program dynamically, adding some data on the fly. Thus, the data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

SELECT * FROM users where id=1

while this code

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have a regular output or a table users deleted.

While in case of prepared statements we don't alter our program, it remains intact
That's the point.

We are sending a program to the server first

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variable called a parameter or a placeholder.

Note that exactly the same query is sent to the server, without any data in it! And then we're sending the data with the second request, essentially separated from the query itself:

$db->execute($data);

so it can't alter our program and do any harm.
Quite simple - isn't it?

The only thing I have to add that always omitted in the every manual:

Prepared statements can protect only data literals, but cannot be used with any other query part.
So, once we have to add, say, a dynamical identifier - a field name, for example - prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.

撑一把青伞 2024-12-24 16:37:50

下面是一个用于设置示例的 SQL 语句:

CREATE TABLE employee(name varchar, paymentType varchar, amount bigint);

INSERT INTO employee VALUES('Aaron', 'salary', 100);
INSERT INTO employee VALUES('Aaron', 'bonus', 50);
INSERT INTO employee VALUES('Bob', 'salary', 50);
INSERT INTO employee VALUES('Bob', 'bonus', 0);

Inject 类容易受到 SQL 注入攻击。查询与用户输入动态粘贴在一起。查询的目的是显示有关 Bob 的信息。根据用户输入,工资或奖金。但是恶意用户通过在 where 子句中添加相当于“或 true”的内容来操纵输入,从而破坏查询,从而返回所有内容,包括本应隐藏的有关 Aaron 的信息。

import java.sql.*;

public class Inject {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=user&password=pwd";
        Connection conn = DriverManager.getConnection(url);

        Statement stmt = conn.createStatement();
        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='" + args[0] + "'";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

运行此程序,第一种情况是正常使用,第二种情况是恶意注入:

c:\temp>java Inject salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary'
salary 50

c:\temp>java Inject "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary' OR 'a'!='b'
salary 100
bonus 50
salary 50
bonus 0

您不应该使用用户输入的字符串连接来构建 SQL 语句。它不仅容易受到注入的攻击,而且还会对服务器产生缓存影响(语句发生变化,因此不太可能获得 SQL 语句缓存命中,而绑定示例始终运行相同的语句)。

以下是避免此类注入的 Binding 示例:

import java.sql.*;

public class Bind {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=postgres";
        Connection conn = DriverManager.getConnection(url);

        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?";
        System.out.println(sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, args[0]);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

使用与前一个示例相同的输入运行此示例,显示恶意代码不起作用,因为没有与该字符串匹配的 paymentType:

c:\temp>java Bind salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?
salary 50

c:\temp>java Bind "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?

Here is an SQL statement for setting up an example:

CREATE TABLE employee(name varchar, paymentType varchar, amount bigint);

INSERT INTO employee VALUES('Aaron', 'salary', 100);
INSERT INTO employee VALUES('Aaron', 'bonus', 50);
INSERT INTO employee VALUES('Bob', 'salary', 50);
INSERT INTO employee VALUES('Bob', 'bonus', 0);

The Inject class is vulnerable to SQL injection. The query is dynamically pasted together with user input. The intent of the query was to show information about Bob. Either salary or bonus, based on user input. But the malicious user manipulates the input corrupting the query by tacking on the equivalent of an 'or true' to the where clause so that everything is returned, including the information about Aaron which was supposed to be hidden.

import java.sql.*;

public class Inject {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=user&password=pwd";
        Connection conn = DriverManager.getConnection(url);

        Statement stmt = conn.createStatement();
        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='" + args[0] + "'";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

Running this, the first case is with normal usage, and the second with the malicious injection:

c:\temp>java Inject salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary'
salary 50

c:\temp>java Inject "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary' OR 'a'!='b'
salary 100
bonus 50
salary 50
bonus 0

You should not build your SQL statements with string concatenation of user input. Not only is it vulnerable to injection, but it has caching implications on the server as well (the statement changes, so less likely to get a SQL statement cache hit whereas the bind example is always running the same statement).

Here is an example of Binding to avoid this kind of injection:

import java.sql.*;

public class Bind {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=postgres";
        Connection conn = DriverManager.getConnection(url);

        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?";
        System.out.println(sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, args[0]);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

Running this with the same input as the previous example shows the malicious code does not work because there is no paymentType matching that string:

c:\temp>java Bind salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?
salary 50

c:\temp>java Bind "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?
请别遗忘我 2024-12-24 16:37:50

基本上,使用准备好的语句,来自潜在黑客的数据将被视为数据 - 并且它不可能与您的应用程序 SQL 混合和/或被解释为 SQL(当传入的数据直接放入您的应用程序时,可能会发生这种情况)应用程序 SQL)。

这是因为准备好的语句首先“准备” SQL 查询以找到有效的查询计划,并稍后发送可能来自表单的实际值 - 此时查询才真正执行。

更多精彩信息请参见:

准备好的语句和 SQL注射

Basically, with prepared statements the data coming in from a potential hacker is treated as data - and there's no way it can be intermixed with your application SQL and/or be interpreted as SQL (which can happen when data passed in is placed directly into your application SQL).

This is because prepared statements "prepare" the SQL query first to find an efficient query plan, and send the actual values that presumably come in from a form later - at that time the query is actually executed.

More great info here:

Prepared statements and SQL Injection

东走西顾 2024-12-24 16:37:50

我通读了答案,仍然觉得有必要强调阐明准备陈述本质的关键点。考虑两种方法来查询涉及用户输入的数据库:

朴素方法

一种将用户输入与某些部分 SQL 字符串连接起来以生成 SQL 语句。在这种情况下,用户可以嵌入恶意SQL命令,然后将其发送到数据库执行。

String SQLString = "SELECT * FROM CUSTOMERS WHERE NAME='"+userInput+"'"

例如,恶意用户输入可能导致 SQLString 等于 "SELECT * FROM CUSTOMERS WHERE NAME='James';DROP TABLE CUSTOMERS;'

由于恶意用户, SQLString 包含 2 条语句,其中第二条语句(“DROP TABLE CUSTOMERS”)会

Prepared statements

在 中造成损害。在这种情况下,由于查询和数据的分离,用户输入永远不会被视为 SQL 语句,因此永远不会被执行。正是因为这个原因,任何恶意 SQL 代码都会被注入。因此,在上述情况下,“DROP TABLE CUSTOMERS” 永远不会被执行

简而言之,通过用户输入引入的准备好的语句将不会被执行!< /强>

I read through the answers and still felt the need to stress the key point which illuminates the essence of Prepared Statements. Consider two ways to query one's database where user input is involved:

Naive Approach

One concatenates user input with some partial SQL string to generate a SQL statement. In this case the user can embed malicious SQL commands, which will then be sent to the database for execution.

String SQLString = "SELECT * FROM CUSTOMERS WHERE NAME='"+userInput+"'"

For example, malicious user input can lead to SQLString being equal to "SELECT * FROM CUSTOMERS WHERE NAME='James';DROP TABLE CUSTOMERS;'

Due to the malicious user, SQLString contains 2 statements, where the 2nd one ("DROP TABLE CUSTOMERS") will cause harm.

Prepared Statements

In this case, due to the separation of the query & data, the user input is never treated as a SQL statement, and thus is never executed. It is for this reason, that any malicious SQL code injected would cause no harm. So the "DROP TABLE CUSTOMERS" would never be executed in the case above.

In a nutshell, with prepared statements malicious code introduced via user input will not be executed!

拔了角的鹿 2024-12-24 16:37:50

当您创建准备好的语句并将其发送到 DBMS 时,它会存储为 SQL 查询以供执行。

稍后将数据绑定到查询,以便 DBMS 使用该数据作为执行的查询参数(参数化)。 DBMS 不会使用您绑定的数据作为已编译的 SQL 查询的补充;而是使用您绑定的数据作为已编译 SQL 查询的补充。这只是数据。

这意味着使用预准备语句执行 SQL 注入基本上是不可能的。准备好的语句的本质及其与 DBMS 的关系阻止了这种情况的发生。

When you create and send a prepared statement to the DBMS, it's stored as the SQL query for execution.

You later bind your data to the query such that the DBMS uses that data as the query parameters for execution (parameterization). The DBMS doesn't use the data you bind as a supplemental to the already compiled SQL query; it's simply the data.

This means it's fundamentally impossible to perform SQL injection using prepared statements. The very nature of prepared statements and their relationship with the DBMS prevents this.

意犹 2024-12-24 16:37:50

SQL Server 中,使用准备好的语句绝对是防注入的,因为输入参数不会形成查询。这意味着执行的查询不是动态查询。
SQL 注入易受攻击语句的示例。

string sqlquery = "select * from table where username='" + inputusername +"' and password='" + pass + "'";

现在,如果 inoutusername 变量中的值类似于 a' 或 1=1 --,则此查询现在变为:

select * from table where username='a' or 1=1 -- and password=asda

其余部分在 -- 之后进行注释,因此它永远不会像使用一样被执行和绕过准备好的语句示例如下。

Sqlcommand command = new sqlcommand("select * from table where username = @userinput and password=@pass");
command.Parameters.Add(new SqlParameter("@userinput", 100));
command.Parameters.Add(new SqlParameter("@pass", 100));
command.prepare();

所以实际上你不能发送另一个参数,从而避免 SQL 注入......

In SQL Server, using a prepared statement is definitely injection-proof because the input parameters don't form the query. It means that the executed query is not a dynamic query.
Example of an SQL injection vulnerable statement.

string sqlquery = "select * from table where username='" + inputusername +"' and password='" + pass + "'";

Now if the value in the inoutusername variable is something like a' or 1=1 --, this query now becomes:

select * from table where username='a' or 1=1 -- and password=asda

And the rest is commented after --, so it never gets executed and bypassed as using the prepared statement example as below.

Sqlcommand command = new sqlcommand("select * from table where username = @userinput and password=@pass");
command.Parameters.Add(new SqlParameter("@userinput", 100));
command.Parameters.Add(new SqlParameter("@pass", 100));
command.prepare();

So in effect you cannot send another parameter in, thus avoiding SQL injection...

空气里的味道 2024-12-24 16:37:50

关键短语是不需要正确转义。这意味着您无需担心人们试图输入破折号、撇号、引号等...

这一切都为您处理。

The key phrase is need not be correctly escaped. That means that you don't need to worry about people trying to throw in dashes, apostrophes, quotes, etc...

It is all handled for you.

半窗疏影 2024-12-24 16:37:50
ResultSet rs = statement.executeQuery("select * from foo where value = " + httpRequest.getParameter("filter");

假设您在 Servlet 中拥有该功能,您是对的。如果恶意者为“过滤器”传递了错误的值,您可能会破解您的数据库。

ResultSet rs = statement.executeQuery("select * from foo where value = " + httpRequest.getParameter("filter");

Let’s assume you have that in a Servlet you right. If a malevolent person passed a bad value for 'filter' you might hack your database.

萌︼了一个春 2024-12-24 16:37:50

根本原因#1 - 分隔符问题

Sql 注入是可能的,因为我们使用引号来分隔字符串,并且也作为字符串的一部分,有时无法解释它们。如果我们有不能在字符串数据中使用的分隔符,那么 SQL 注入就永远不会发生。解决分隔符问题就消除了sql注入问题。结构查询就是这样做的。

根本原因#2 - 人性,人们都是狡猾的,一些狡猾的人是恶意的 所有人都会犯错误

sql注入的另一个根本原因是人性。人们,包括程序员,都会犯错误。当您在结构化查询上犯错时,不会使您的系统容易受到 SQL 注入的攻击。如果不使用结构化查询,错误可能会产生 sql 注入漏洞。

结构化查询如何解决 SQL 注入的根本原因

结构化查询通过将 sql 命令放在一个语句中并将数据放在单独的编程语句中来解决分隔符问题。编程语句创建所需的分离。

结构化查询有助于防止人为错误造成严重的安全漏洞。
对于人类会犯的错误,使用结构查询时不会发生sql注入。有一些方法可以防止不涉及结构化查询的 SQL 注入,但这些方法中的正常人为错误通常会至少导致一些 SQL 注入暴露。结构化查询可以避免 SQL 注入的故障。与任何其他编程一样,使用结构化查询,您几乎可以犯世界上所有的错误,但是您所犯的错误都不能变成由 SQL 注入接管的 ssstem。这就是为什么人们喜欢说这是防止sql注入的正确方法。

现在,您已经了解了 sql 注入的原因以及导致它们在使用时无法实现的结构化查询的本质。

Root Cause #1 - The Delimiter Problem

Sql injection is possible because we use quotation marks to delimit strings and also to be parts of strings, making it impossible to interpret them sometimes. If we had delimiters that could not be used in string data, sql injection never would have happened. Solving the delimiter problem eliminates the sql injection problem. Structure queries do that.

Root Cause #2 - Human Nature, People are Crafty and Some Crafty People Are Malicious And All People Make Mistakes

The other root cause of sql injection is human nature. People, including programmers, make mistakes. When you make a mistake on a structured query, it does not make your system vulnerable to sql injection. If you are not using structured queries, mistakes can generate sql injection vulnerability.

How Structured Queries Resolve the Root Causes of SQL Injection

Structured Queries Solve The Delimiter Problem, by by putting sql commands in one statement and putting the data in a separate programming statement. Programming statements create the separation needed.

Structured queries help prevent human error from creating critical security holes.
With regard to humans making mistakes, sql injection cannot happen when structure queries are used. There are ways of preventing sql injection that don't involve structured queries, but normal human error in that approaches usually leads to at least some exposure to sql injection. Structured Queries are fail safe from sql injection. You can make all the mistakes in the world, almost, with structured queries, same as any other programming, but none that you can make can be turned into a ssstem taken over by sql injection. That is why people like to say this is the right way to prevent sql injection.

So, there you have it, the causes of sql injection and the nature structured queries that makes them impossible when they are used.

冷弦 2024-12-24 16:37:50

简单的例子:

  "select * from myTable where name = " + condition;

如果用户输入是:

  '123'; delete from myTable; commit;

查询将像这样执行:

  select * from myTable where name = '123'; delete from myTable; commit;

The simple example:

  "select * from myTable where name = " + condition;

And if user input is:

  '123'; delete from myTable; commit;

The query will be executed like this:

  select * from myTable where name = '123'; delete from myTable; commit;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文