查看sql是否使用绑定变量_使用SQL绑定变量提高应用程序性能和安全性

最新推荐文章于 2022-04-10 14:15:04 发布

cuyi7076

最新推荐文章于 2022-04-10 14:15:04 发布

阅读量840

点赞数

原文链接：https://www.ibm.com/developerworks/security/library/se-bindvariables/index.html

版权

查看sql是否使用绑定变量

使用SQL在Web应用程序和数据库之间传递数据时，可以选择将文字数据包含在SQL语句中或使用绑定变量。绑定变量是SQL语句中实际值的占位符。使用绑定变量而不是替换变量或文字编写SQL语句可以最大程度地缩短处理时间，并可以将应用程序性能提高20％到30％。使用绑定变量还可以帮助防止SQL注入攻击。

本文比较了在SQL语句中使用绑定变量（也称为绑定参数或动态参数）而不是替换变量或文字的性能和安全性优势。我简要介绍了绑定变量，然后演示了如何在SQL语句中使用它们并显示所带来的性能改进。我还将向您展示如何使用绑定变量有效地阻止示例Java应用程序中SQL注入攻击。

绑定变量概述

IBM InfoSphere Guardium数据库活动监视演示

观看此演示并了解IBMInfoSphere®Guardium®数据库活动监视如何通过整体的数据安全性和合规性方法来帮助保护分布式DBMS环境中的敏感数据。注册以查看IBM InfoSphere Guardium数据库活动监视演示。

绑定变量由占位符（例如，问号（ ? ） @name :name或@name ）指示的变量组成。占位符取决于您使用SQL数据库服务器。您可以在执行SQL语句之前，在运行时提供占位符的实际值。

绑定变量如何提高应用程序性能

在大多数关系数据库中，SQL语句分三个步骤处理：

解析SQL语句 ：验证SQL语句的语法和访问权限，并为SQL语句构建最佳（优化）执行计划。目前尚不知道占位符变量。
绑定变量 ：API为占位符提供实际值的地方。
执行：完成所选的执行计划和占位符变量的实际值。

每次将SQL语句发送到数据库时，都会执行精确的文本匹配，以查看共享池中是否已存在该语句。如果找不到匹配的语句，则数据库将对该语句执行硬解析。如果找到匹配的语句，则数据库启动软解析。

在硬解析中 ，需要分析 SQL语句，检查语法错误，检查表名和列名的正确性，并进行优化以找到最佳执行计划。
在软解析中 ，SQL语句已存在于共享池中，因此访问权限和会话验证几乎不需要进行任何处理。

使用绑定变量可启用软解析，这意味着在选择优化的执行计划时将花费更少的处理时间。有关如何处理SQL语句的信息以及SQL语句本身已保存在共享池中。

两种绑定方式

不同的API通过以下两种方式之一执行绑定：按值绑定在每次执行之前获取实际值。通过引用绑定使用在特定内存地址找到的值。

比较SQL语句

使用绑定变量具有明显的性能优势。对于一个人来说，在共享池中保存许多相似但独特SQL语句会浪费内存。编写具有可互换变量的语句会更有效。解析SQL语句也很耗时。减少硬解析的数量可以最大程度地减少CPU使用率。

让我们比较使用文字，替换变量和绑定变量对性能的影响。

文字

文字是直接传递给SQL查询的值。例如，您可以使用文字执行清单1中的两个查询：

清单1.两个带文字SQL查询

SQL> SELECT * FROM dual WHERE dummy = 'dummy_literal1';
no rows selected

SQL> SELECT * FROM dual WHERE dummy = 'dummy_literal2';
no rows selected

清单2显示了共享池中的查询：

清单2.带文字的共享池

SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  ORDER BY sql_text;

清单3显示了结果输出：

清单3.带文字SQL查询的输出

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = 'dummy_literal1'                1
SELECT * FROM dual WHERE dummy = 'dummy_literal2'                1
2 rows selected.

在这种情况下，两个查询都是分别解析的，因此共享池中有两个单独的查询。

替代变量

当在语句中使用替换变量时，将请求输入值，并重写该语句以包括该值。重写的语句将传递到数据库。结果，数据库服务器对替换变量一无所知。清单4显示了一个替换变量的示例：

清单4.两个带有替换变量SQL查询

SQL> SELECT * FROM dual WHERE dummy = '&dummy';
Enter value for dummy: dummy_substitution1
old   1: SELECT * FROM dual WHERE dummy = '&dummy'
new   1: SELECT * FROM dual WHERE dummy = 'dummy_substitution1'
no rows selected

SQL> SELECT * FROM dual WHERE dummy = '&dummy';
Enter value for dummy: dummy_substitution2
old   1: SELECT * FROM dual WHERE dummy = '&dummy'
new   1: SELECT * FROM dual WHERE dummy = ' dummy_substitution2'
no rows selected

清单5显示了共享池中的查询：

清单5.具有替换变量的共享池

SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  ORDER BY sql_text;

清单6显示了结果输出：

清单6.具有替换变量SQL查询的输出

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = 'dummy_substitution1'             1
SELECT * FROM dual WHERE dummy = 'dummy_substitution2'             1
2 rows selected.

再一次，两个语句都被分别解析，并且共享池中有两个单独的查询。就数据库服务器而言，文字和替换变量是相同的。

绑定变量

现在，我说明使用绑定变量如何影响共享池。清单7遵循与先前相同的格式：

清单7.两个带有绑定变量SQL查询

SQL> VARIABLE dummy VARCHAR2(30);

SQL> EXEC :dummy := 'dummy_bind1';
PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;
no rows selected

SQL> EXEC :dummy := 'dummy_bind2';
PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;
no rows selected

清单8是共享池的快照：

清单8.具有绑定变量的共享池

SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  ORDER BY sql_text;

清单9显示了输出：

清单9.具有绑定变量SQL查询的输出

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = :dummy                               2
1 row selected.

如您所见，在这种情况下，同一条SQL语句执行了两次，因此共享池中仅驻留一个SQL查询。

SQL解析和性能

如前所述，CPU使用率根据所需SQL解析类型而有所不同。文字和替换变量需要硬解析，与绑定变量所需的软解析相比，它消耗更多的CPU周期。

对于以下代码清单，我通过查询parse time cpu统计信息从V$MYSTAT检索了CPU使用率测量值。此统计信息表示用于解析（硬或软）的总CPU时间，以毫秒为单位。还显示共享池中存在的语句。

清单10是一个没有绑定变量的语句：

清单10.没有绑定变量的语句

SQL> DECLARE
  2    l_dummy  dual.dummy%TYPE;
  3  BEGIN
  4    FOR i IN 1 .. 10 LOOP
  5      BEGIN
  6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = ''' || TO_CHAR(i) || ''''
  7        INTO l_dummy;
  8      EXCEPTION
  9        WHEN NO_DATA_FOUND THEN
 10          NULL;
 11      END;
 12    END LOOP;
 13  END;
 14  /

PL / SQL过程成功完成。接下来，清单11执行该过程并检查CPU使用率：

清单11.没有绑定变量的CPU使用率

SQL> SELECT sn.name, ms.value
  2  FROM   v$mystat ms, v$statname sn
  3  WHERE  ms.statistic# = sn.statistic#
  4  AND    sn.name       = 'parse time cpu';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           63

1 row selected.

结果表明，会话期间使用了630毫秒的CPU时间进行解析。

清单12显示了共享池中的执行结果：

清单12.共享池中的执行

SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  AND    INSTR(sql_text, 'DECLARE') = 0
  7  ORDER BY sql_text;
SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = '1'                              1
SELECT dummy FROM dual WHERE dummy = '10'                             1
SELECT dummy FROM dual WHERE dummy = '2'                              1
SELECT dummy FROM dual WHERE dummy = '3'                              1
SELECT dummy FROM dual WHERE dummy = '4'                              1
SELECT dummy FROM dual WHERE dummy = '5'                              1
SELECT dummy FROM dual WHERE dummy = '6'                              1
SELECT dummy FROM dual WHERE dummy = '7'                              1
SELECT dummy FROM dual WHERE dummy = '8'                              1
SELECT dummy FROM dual WHERE dummy = '9'                              1
10 rows selected.

请注意，共享池包含10个使用文字的相似语句。

使用绑定变量

接下来，我使用绑定变量运行相同的查询。清单13显示了清单10中相同的语句，只是绑定变量被替换。

清单13.具有绑定变量SQL语句

SQL> DECLARE
  2    l_dummy  dual.dummy%TYPE;
  3  BEGIN
  4    FOR i IN 1 .. 10 LOOP
  5      BEGIN
  6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)'
  7        INTO l_dummy USING i;
  8      EXCEPTION
  9        WHEN NO_DATA_FOUND THEN
 10          NULL;
 11      END;
 12    END LOOP;
 13  END;
 14  /

PL / SQL过程成功完成。当我执行清单13中的过程并检查CPU使用率时，我得到的结果显示在清单14中：

清单14.带绑定变量的CPU使用率

SQL> SELECT sn.name, ms.value
  2  FROM   v$mystat ms, v$statname sn
  3  WHERE  ms.statistic# = sn.statistic#
  4  AND    sn.name       = 'parse time cpu';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           40
1 row selected.

结果表明，会话期间的解析使用了400毫秒的CPU时间。少于前一个示例中使用的数量的三分之二。现在让我们检查共享池。

清单15.具有绑定变量的共享池

SQL> SELECT sql_text,
  2         executions
  3  FROM   v$sql
  4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
  5  AND    INSTR(sql_text, 'sql_text') = 0
  6  AND    INSTR(sql_text, 'DECLARE') = 0
  7  ORDER BY sql_text;
SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)                 10
1 row selected.

如清单15所示，共享池中只有一条语句。

这些示例清楚地表明，用绑定变量替换文字可以节省内存使用量和CPU周期。在这些情况下，性能提高了约30％。

表现摘要

使用绑定变量的优点是由于数据库不需要为每个SQL语句重建其执行计划。绑定变量适用于完全相同SQL语句，唯一的区别在于值。使用绑定变量时，不包括实际值，而是在SQL语句中插入占位符。该语句在执行期间不会更改；只有值改变。

接下来，我展示了上下文中绑定变量的使用，评估了在示例Java程序中对SQL语句使用绑定变量的性能优势。

性能调整SQL语句

清单16代表了一个典型的Java程序，其中SQL语句是使用文字编写的。为每个循环创建一个新SQL语句。每次循环遇到新值时，都会创建并执行一个新SQL查询。

清单16.没有绑定变量的基本SQL查询

sql = "SELECT t.name FROM hr.employees t WHERE employee_id = ";

System.out.println("Start: " + new Date());

for(int i=0; i&lt;10000; i++)
    {
        statement = connection.createStatement();
        resultset = statement.executeQuery(sql + Integer.toString(i));
        if(resultset.next())
        {
            name = resultset.getString("name");
            doSomething(name);
        }
        resultset.close();
        statement.close();
    }

System.out.println("End: " + new Date());

执行此代码大约需要11秒钟。现在，让我们使用准备好的语句和绑定变量来重写代码。

带绑定变量的预备语句

在清单17中，已将定义了绑定变量的查询发送到服务器。在执行期间，我们将Java变量“ i ”绑定到SQL语句。因此，我们可以对10,000个查询使用相同的执行计划，从而通过最小化SQL解析来提高性能。

清单17.具有绑定变量的基本SQL语句

sql = "SELECT t.name FROM hr.employees t WHERE employee_id = ?";
System.out.println("Start: " + new Date());
for(int i=0; i&lt;10000; i++)
    {
        statement = connection.prepareStatement(sql);
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if(resultset.next())
        {
            name = resultset.getString("name");
            doSomething(name);
        }
        resultset.close();
        statement.close();
    }
            System.out.println("End: " + new Date());

这段代码花费了大约七秒钟的时间来执行。但是请注意，该代码为每个循环创建了一个新语句。我们可以通过仅创建一个语句并对每个循环重新使用它来改善此结果，如清单18所示：

清单18.重用一条语句

sql = "SELECT t.name FROM hr.employees t WHERE employee_id = ?";
statement = connection.prepareStatement(sql);
System.out.println("Start: " + new Date());
for(int i=0; i<10000; i++)
    {
        statement.setInt(1, i);
        resultset = statement.executeQuery();
        if(resultset.next())
        {
            name = resultset.getString("name");
            doSomething(name);
        }
        resultset.close();
    }
            System.out.println("End: " + new Date());

statement.close();

此Java代码执行与原始代码相同SQL操作大约需要4秒钟，而这花费了11秒钟。

SQL注入攻击的类型

在2013年，SQL Web注入攻击被Open Web Application Security Project评为第一大安全威胁（请参阅参考资料）。在SQL注入攻击中，恶意SQL语句通过输入字段插入Web应用程序的数据库中，以强制应用程序执行它们。为了使SQL注入攻击起作用，应用程序代码必须容易受到用户输入的攻击。 SQL注入攻击利用了应用程序的用户输入漏洞，该用户输入错误地过滤了带有嵌入式SQL语句的字符串文字转义字符，或者输入的类型不强。

下一节讨论了可以引发SQL注入攻击的两种类型的安全漏洞。

错误过滤的转义字符

在第一类攻击中，黑客将包含转义符和嵌入式SQL语句的文本放入Web应用程序表单字段或查询属性中。如果Web应用程序未过滤掉转义符，则带有恶意SQL语句的文本将传递到数据库中以执行。

以下代码行说明了此漏洞：

statement := "SELECT * FROM emp WHERE emp_name = '" + empName + "';"

如果从Web应用程序的表单字段中设置了empName ，则攻击者可以在empName字段中输入以下内容：

' or '1'='1

如果Web应用程序代码未转义单引号（'）字符，则将其原样包含在SQL语句中，从而产生以下新SQL语句：

SELECT * FROM emp WHERE emp_name = '' or '1'='1';

当执行此代码时，它将返回表emp所有数据，因为WHERE子句中的'1'='1'始终为true。攻击者将成功检索数据库中每个员工的数据。

SQL注释和语句

另一常见攻击是将注释恶意注入SQL语句中，从而阻止其余查询被执行。可以注入三种类型SQL注释，如下所示：

' or '1'='1' -- '
' or '1'='1' ({ '
' or '1'='1' /* '

恶意注入SQL语句中的前三个输入中的任何一个都将阻止查询的其余部分。

攻击者还可以在现有语句的末尾添加恶意SQL语句。例如，以下语句中的empName的值将导致emp表被删除。在允许多个语句的API中，还将删除从userinfo表中选择的所有数据。

a';DROP TABLE emp; SELECT * FROM userinfo WHERE 't' = 't

此输入将呈现最终SQL语句，如下所示：

SELECT * FROM emp  WHERE emp_name = 'a';DROP TABLE emp; SELECT * FROM userinfo WHERE 't' = 't';

SQL数据库服务器安全性

虽然大多数SQL数据库服务器实现允许通过一次调用执行多个语句，但是某些API（例如PHP的mysql_query();函数）不允许这样做。这种保护措施可以防止攻击者注入新查询，但不会阻止他们修改现有查询。

类型处理不正确

当用户输入的数据类型未经验证时，会发生第二种类型SQL注入攻击。例如，程序员可能无法验证数字字段的用户输入，如下所示：

statement := "SELECT * FROM userinfo WHERE id = " + id_var + ";"

id_var的可接受值是数字，但是在将其设置为SQL查询之前没有进行验证。如果变量id_var与申请表相关联，则攻击者可以如下所示进行设置：

1;DROP TABLE users, yielding the following SQL: 

SELECT * FROM userinfo WHERE id=1;DROP TABLE users;

如果成功传递，则此语句将导致SQL数据库服务器从数据库中删除users表。

防止SQL注入攻击

将绑定变量作为参数传递给SQL预准备语句时，JDBC驱动程序会自动对其进行转义。产生的转义字符串将变量视为用户数据，并且SQL数据库服务器无法将其解释为SQL语句。因此，在将用户提供的数据添加到SQL语句之前，必须对其进行转义。清单19显示了添加到SQL语句的绑定变量user ID ：

清单19.具有绑定变量的准备好的语句

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();

prepStmt对象的setString方法转义userID字符串并将其添加到SQL语句中。通过userID变量传入的所有恶意SQL语句在输入的转义版本中userID视为不可执行。

绑定数据库中的变量

最后，我快速回顾了常见数据库编程环境中绑定变量的语法。

在SQL * Plus中，将使用绑定变量，如清单20所示：

清单20. SQL * Plus中的绑定变量

SQL> variable deptno number
SQL> exec :deptno := 10
SQL> select * from emp where deptno = :deptno;

PL / SQL处理与绑定变量有关的大多数问题。以清单21中的PL / SQL为例：

清单21.在PL / SQL中绑定变量

create or replace procedure dsal(p_empno in number)
as
  begin
    update emp
    set sal=sal*2
    where empno = p_empno;
    commit;
  end;
/

注意，对PL / SQL变量的每个引用都是一个绑定变量。

在Microsoft SQL Server的情况下，您将使用sp_executesql ，绑定变量将类似于： @var1 。清单22显示了一个示例：

清单22. Microsoft SQL Server中的绑定变量

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

清单23是MySQL Server中带有单个绑定变量的准备好的语句：

清单23. MySQL Server准备好的语句中的绑定变量

mysql> prepare stmt from 
    -> 'select count(*) from information_schema.schemata where schema_name = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> set @schema := 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> execute stmt using @schema;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

清单24是带有多个绑定变量的准备好的语句：

清单24. MySQL Server准备好的语句中的多个绑定变量

mysql> prepare stmt from 
    -> 'select count(*) 
    -> from information_schema.schemata 
    -> where schema_name = ? or schema_name = ?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute stmt 
    -> using @schema1,@schema2
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

结论

在本文中，我介绍了绑定变量，并使用代码示例说明了它们在性能影响方面如何与替代变量和文字进行比较。如您所见，在SQL语句中使用绑定变量可以将SQL执行的性能度量提高多达30％。使用绑定变量也是防止SQL注入攻击的一种已知防御方法（请参阅参考资料）。我演示了几种类型SQL注入攻击，并向您展示了如何在许多常见的数据库编程环境中使用绑定变量。

翻译自: https://www.ibm.com/developerworks/security/library/se-bindvariables/index.html