mysql语句防止攻击_准备好的语句如何防止SQL注入攻击？

下面是用于设置示例的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子句中添加相当于“或真”的“或真”语句来操纵破坏查询的输入，从而返回所有内容，包括应该隐藏的有关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语句缓存，而BIND示例总是运行相同的语句)。

下面是一个避免这种注入的绑定示例：

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=?