预编译种类及例子

1、java中的预编译：

（1）sql语句中变量的位置用？进行填补，后期再set进去：

    java.sql.PreparedStatement stmt = connection.prepareStatement(
            "SELECT * FROM users WHERE USERNAME = ? AND ROOM = ?");
    stmt.setString(1, username);
    stmt.setInt(2, roomNumber);
    stmt.executeQuery();

（2）使用特定的new MapSqlParameterSource()类，

将需要查询的变量整合成类似于php预编译中参数绑定的格式：

    String sql = "select * from user where userid=:userid";
    MapSqlParameterSource named_param = new MapSqlParameterSource();
    Timestamp tt = new Timestamp(System.currentTimeMillis());
    named_param.addValue("userid", "1");
    List list = this.jdbcTemplate.query(sql, named_param, new FirstGuideRowMapper());

（3）据朋友说java中在构造sql语句时也可以采用事务的预编译来预编译sql：

个人认为，事务的预编译是不能够彻底对sql注入进行防范的，因为只有当sql语句出错时，事务才会回滚，

而如果攻击者拼接成正确的sql语句，那么事务会正常执行并返回的。

    try {
      PreparedStatement pset_f = conn.prepareStatement(sql);
      pset_f.setString(1,inds[j]);
        pset_f.setString(2,id);
pset_f.executeUpdate(sql_update);
    }catch(Exception e){
      //e.printStackTrace();
   logger.error(e.message());
    } finally{

return;

}

2、php中的预编译：

（1）sql语句中变量的位置用？进行填补：
$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?");
$stmt->execute(array($username, $pass));

（2）采用参数绑定的方法对sql进行预编译：
$stmt = $dbh->prepare("SELECT * FROM users WHERE USERNAME=:username AND PASSWORD=:pass");
$stmt->execute(array('username' => $username, 'pass' => $pass));

3、perl中的预编译：

my $stmt = $dbh->prepare('SELECT * FROM users WHERE USERNAME = ? AND PASSWORD = ?');
$stmt->execute($username, $password);

4、C#中的预编译：

    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())
{
          // ...
}
    }

5、python中的预编译：

    import sqlite3
    conn = sqlite3.connect(':memory:')
    c = conn.cursor()

    _users = [('mother', 'red'),
                  ('father', 'green'),
            ('me', 'blue')]
    c.executemany('INSERT INTO users VALUES (?,?)', _users)

    params = ('sister', 'yellow')
    c.execute('SELECT * FROM users WHERE username=? AND room=?', params)
    c.fetchone()

6、Magic Direct SQL：

Virtual username Alpha 20 init: 'sister'

Virtual password Alpha 20 init: 'yellow'

SQL Command: SELECT * FROM users WHERE USERNAME=:1 AND PASSWORD=:2

Input Arguments: 1: username 2: password

参考文章：https://en.wikipedia.org/wiki/Prepared_statement