动态SQL机制简介
动态SQL（又名“Prepared statement”）是一种特殊的SQL查询执行方式，即查询语句本身中含有变量，在查询时需要传入一个具体变量后执行相应查询，因此查询结果是动态的。
各编程语言执行动态SQL的基本步骤为：建立连接->创建查询->设置查询参数->执行查询（并可能返回结果）。其中，对于日期时间型变量在执行时需要转换为字符串形式。
动态SQL机制主要特点有：
- 安全性好，主要体现在能够有效防范SQL注入式攻击。
- 灵活性、可移植性强。在不同程序条件下，修改SQL的查询参数即可。
- 由特点2，程序的健壮性和可移植性也会提高。
因此，动态SQL机制广泛被各类提供连接SQL的编程语言广泛使用。由于Python语言本身在选择数据过程中就可以直接附带变量参数（详见：https://my.oschina.net/Samyan/blog/1577680），本文对于Python语言的动态SQL机制就不再赘述。
本文所用数据库结构
本文所用数据库结构非常简单，为webappdemo数据库下的表user，含有两栏VARCHAR(50)类型字符串，分别为username和password，以username为此表主键。
小论注入式攻击
现有一个典型的php文件，内含登录界面代码如下所示：
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>登录界面</title>
</head>
<form action="login.php" method="post">
<input type="text" name="username" value="' or 1 = 1 -- "/><br />
<input type="password" name="password"/><br />
<input type="submit" value="Log In"/>
</form>
<body>
</body>
</html>
<?php
/* This is a POORLY DESIGNED login script, for use only in demonstrating an "SQL Injection" attack. */
print '<p> connecting to database ... </p>';
// connect to server, select database
$link = mysql_connect('localhost', 'root', '123456')
or die('Could not connect: ' . mysql_error());
print '<p> connected successfully </p>';
mysql_select_db('webappdemo') or die('could not select database');
// perform SQL query
$query = "select username from user where username = '" . $_POST['username'] . "' and password = '" . $_POST['password'] . "';";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$firstLine = mysql_fetch_array($result, MYSQL_ASSOC);
$loggedInName = $firstLine['username'];
print '<p> the result returned was: ' . $loggedInName . '</p>';
if ($loggedInName == '') {
print '<h1 style="color:red">login failed: wrong name or password</h1>';
} else {
print '<h1 style="color:blue">hi, ' . $loggedInName . ', you are logged in successfully!</h1>';
}
// free resultset
mysql_free_result($result);
// close connection to database
mysql_close($link);
?>
运行此代码时，用户通过将用户名和密码输入表单并提交，之后程序通过连接数据库查询是否存在与用户名和密码相匹配的一栏用户信息，若能查询到，则返回相应用户信息，否则拒绝登录。测试该例子可以发现，该程序对于确实输入错误用户名和密码的用户具有拦截作用，但是对于在用户名栏输入"' or 1 = 1; --" 的用户却允许其登录第一个用户（Alice）的账户，从而造成风险。
在MySQL workbench中运行下列语句：
SELECT * FROM user WHERE username = '' or 1 = 1 -- AND password = "123456";
会发现双杠后面的语句都被注释掉了，因此返回值会是整张表。
这种方式通过向登录页面“注入”SQL脚本语句的一部分造成风险，称为SQL注入式攻击。
若将php脚本的查询部分换成动态SQL，由于动态SQL对于输入是作为变量处理的，则能很好避免这个问题，从而防范SQL注入式攻击。其代码如下：
<?php
print '<p> connecting to database ... </p>';
// connect to server, select database
$conn = new mysqli("localhost", "root", "123456", "webappdemo");
if ($conn->connect_error) {
echo "Failed to connect to MySQL: (" . $conn->connect_error.")";
}
$username = $_POST['username'];
$password = $_POST['password'];
// prepare and bind
$stmt = $conn->prepare("select username from user where username = ? and password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
if(!($stmt->execute())){
print "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
};
$result = $stmt->get_result();
$firstLine = $result->fetch_assoc();
$loggedInName = $firstLine['username'];
print '<p> the result returned was: ' . $loggedInName . '</p>';
if ($loggedInName == '') {
print '<h1 style="color:red">login failed: wrong name or password</h1>';
} else {
print '<h1 style="color:blue">hi, ' . $loggedInName . ', you are logged in successfully!</h1>';
}
// free resultset
mysqli_free_result($result);
// close connection to database
mysqli_close($conn);
?>
程序运行结果：
Java程序使用动态SQL语句示例
package prepstatementdemo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
public class PrepStatementDemo {
private Connection connect = null;
private PreparedStatement prep_stmt = null;
private ResultSet retSet = null;
public static void main(String[] args) {
PrepStatementDemo jdbcSelect = new PrepStatementDemo();
jdbcSelect.selectFromDB();
}//end main method
public void selectFromDB() {
try {
// this will load the MySQL driver, each DB has its own driver
Class.forName("com.mysql.jdbc.Driver");
// setup the connection with the DB.
connect = DriverManager.getConnection(
"jdbc:mysql://localhost/webappdemo",
"root", "123456");
// resultSet gets the result of the SQL query
prep_stmt = connect.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
prep_stmt.setString(1, "Bob");
prep_stmt.setString(2, "5678910");
/*
//For date statements, strings shall be used.
//If classes such as GregorianCalendar is used, can be parsed to string.
prep_stmt.setString(1, "2017-07-15");
prep_stmt.setString(2, "2017-09-20");
*/
retSet = prep_stmt.executeQuery();
if(retSet.next()) {
System.out.println("User logged in successful!");
System.out.println("Welcome, " + retSet.getString(1));
}
else {
System.out.println("Username / password error! Log in refused!");
}
prep_stmt.close();
connect.close();
} catch (SQLException | ClassNotFoundException ex) {
ex.printStackTrace();
}//end try-catch
}//end method insert
}//end class
程序运行结果如图：
C++程序使用动态SQL语句示例
#include<iostream>
#include<stdlib.h>
#include "mysql_connection.h"
#include "mysql_driver.h"
#include "mysql_error.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
//Need to include prepared_statement class
#include <cppconn/prepared_statement.h>
using namespace std;
int main(int argc, char *argv[])
{
try {
sql::mysql::MySQL_Driver *driver;
driver = sql::mysql::get_mysql_driver_instance();
sql::Connection *con;
con = driver->connect("tcp://127.0.0.1:3306", "root", "123456");
sql::PreparedStatement *prep_stmt;
sql::ResultSet *retSet;
con->setSchema("webappdemo");
prep_stmt = con->prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
prep_stmt->setString(1,"Alice");
prep_stmt->setString(2, "123456");
retSet = prep_stmt->executeQuery();
/*Must be getting next here,
Otherwise user with wrong information
can log in!!!!*/
if(retSet->next()) {
cout << "User logged in successful!" << endl;
cout << "Welcome, " << retSet->getString(1) << endl;
}
else {
cout << "Username / password error! Log in refused!" << endl;
}
delete retSet;
delete prep_stmt;
delete con;
}
catch (sql::SQLException &e) {
/*
MySQL Connector/C++ throws three different exceptions:
- sql::MethodNotImplementedException (derived from sql::SQLException)
- sql::InvalidArgumentException (derived from sql::SQLException)
- sql::SQLException (derived from std::runtime_error)
*/
cout << "# ERR: SQLException in " << __FILE__;
cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
/* what() (derived from std::runtime_error) fetches error message */
cout << "# ERR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << " )" << endl;
system("pause");
return EXIT_FAILURE;
}
system("pause");
}
程序运行结果如图：
参考资料：
- Python中MySQLConnector模块使用方法详解，URL：http://www.111cn.net/phper/python/67319.htm
- “connector-cpp-en.a4.pdf.” .
- “connector-odbc-en.a4.pdf.” .
- tutorialspoint.com, “JDBC - Create Database Example,” www.tutorialspoint.com. [Online]. Available: https://www.tutorialspoint.com/jdbc/jdbc-create-database.htm. [Accessed: 16-Nov-2017].
- “MySQL :: MySQL 5.7 Reference Manual :: 27.8.6 C API Function Overview.” [Online]. Available: https://dev.mysql.com/doc/refman/5.7/en/c-api-function-overview.html. [Accessed: 17-Nov-2017].
- 王珊和萨师煊，《数据库系统概论》第5版，高等教育出版社，2016年2月，北京
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
