PostgreSQL SQL Injection Attack Mitigation

 SQL注入攻击指利用应用程序数据库接口漏洞进行攻击.

 

statement = "SELECT * FROM users WHERE name = '" + userName + "';"

userName :  ' or '1'='1

SELECT * FROM users WHERE name = '' or '1'='1';
 

' or '1'='1' -- '

SELECT * FROM users WHERE name = '' OR '1'='1' -- ';
 

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

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

statement := "SELECT * FROM userinfo WHERE id = " + a_variable + ";"
a_variable应该是int类型, 但是如果没有强类型机制的话, 攻击者就可以利用这个漏洞进行SQL注入.
如果a_variable等于1;DROP TABLE users
那么SQL变成 : 
SELECT * FROM userinfo WHERE id=1;DROP TABLE users;
 
3. 使用以上方法还可以获取数据库版本, 根据版本漏洞进行攻击.
例如 : 
注入
select version();
 
 
规避举例 : 
1. 使用绑定变量
使用绑定变量后, 语句模板将提前提交给数据库, 例如
INSERT INTO PRODUCT (name, price) VALUES (?, ?)
数据库收到模板后, 解析,编译,执行sql优化器并保存结果.
对于PostgreSQL来说, 每个会话保持各自的sql优化结构. 但是并不一定每次都是有固定的执行计划, 因此不同的传入变量并不会影响执行计划的正确性. 参考.
http://blog.163.com/digoal@126/blog/static/1638770402012112452432251/
后续应用程序将变量值传递或者(bind)给数据库.
因为SQL解析已经提前处理, 因此能规避SQL注入攻击.
 
以下截取自wiki : 
Java JDBC [edit]
This example uses Java and the JDBC API:
java.sql.PreparedStatement stmt = connection.prepareStatement( "SELECT * FROM users WHERE USERNAME = ? AND ROOM = ?"); stmt.setString(1, username); stmt.setInt(2, roomNumber); stmt.executeQuery(); 
Java PreparedStatement provides "setters" (setInt(int), setString(String), setDouble(double), etc.) for all major built-in data types.
PHP PDO [edit]
This example uses PHP and PHP Data Objects (PDO):
$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?"); $stmt->execute(array($username, $password)); 
PERL DBI [edit]
This example uses Perl and DBI:
my $stmt = $dbh->prepare('SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?'); $stmt->execute($username, $password); 
C# ADO.NET [edit]
This example uses C# and ADO.NET:
using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT * FROM users WHERE USERNAME = @username AND ROOM = @room"; command.Parameters.AddWithValue("@username", username); command.Parameters.AddWithValue("@room", room); using (SqlDataReader dataReader = command.ExecuteReader()) { // ... } } 
ADO.NET SqlCommand will accept any type for the value parameter of AddWithValue, and type conversion occurs automatically.
Note the use of "named parameters" (i.e. "@username") rather than "?" - this allows you to use a parameter multiple times and in any arbitrary order within the query command text.
Python DB-API [edit]
This example uses Python DB-API with SQLite and paramstyle='qmark':
import sqlite3
conn = sqlite3.connect(':memory:') c = conn.cursor() _users = [('A', 'red'), ('B', 'green'), ('C', 'blue')] c.executemany('INSERT INTO users VALUES (?,?)', _users) params = ('B', 'green') c.execute('SELECT * FROM users WHERE username=? AND room=?', params) c.fetchone()
 
2. 字符逃逸
php参考 : 
http://php.net/manual/en/function.pg-escape-string.php
使用pg_escape_string函数将字符串中的特殊字符转义, 因此不会带来前面的问题.
例如 将1个单引号转成2个单引号. 
http://blog.163.com/digoal@126/blog/static/163877040201342185210972/
 
3. 强制类型
对于变量本应该是int类型的, 就不允许输入字符类型.
 
4. 数据库权限控制
把业务程序性用户的权限收到最小, 满足业务需求即可.
 
[其他]
1. 平时做好归档的备份以及基础备份很重要, 即使真的被攻击了, 也可以恢复到被攻击前的时间点, 将数据挽回.
2. 对于重要的表, 最好所有的DML, DDL都要进行跟踪记录. 例如使用触发器跟踪变更.
参考 : 
http://blog.163.com/digoal@126/blog/static/163877040201252575529358/
 
[参考]
1. http://en.wikipedia.org/wiki/Sql_injection
2. http://blog.163.com/digoal@126/blog/static/1638770402012910234402/
3. http://www.postgresql.org/docs/devel/static/functions-string.html
4. http://php.net/manual/en/function.pg-escape-string.php
5. http://www.postgresql.org/docs/9.3/static/runtime-config-compatible.html
6. http://blog.163.com/digoal@126/blog/static/163877040201342185210972/
7. http://en.wikipedia.org/wiki/Prepared_statement
8. http://en.wikipedia.org/wiki/Object-relational_mapping
9. http://en.wikipedia.org/wiki/Query_optimization
10. http://blog.163.com/digoal@126/blog/static/1638770402012112452432251/