SQL注入攻击尝试

SQL注入是在web开发中，不得不考虑的一个问题，近些年已经得到了较充分的重视。一般新手不注意的话，写出的程序容易被SQL注入攻击。比如笔者试了下面这样一个用户登录的页面（代码见附录）

数据库为mysql，用户表为userinfo，通过sql注入，就可以随意登录。
登录方式1
用户名：' or 1=1 or 1='1
密码：任意
select count(*) from userinfo where username = '' or 1=1 or 1='1' and password =''

登录方式2
用户名：任意
密码：' or '1'='1
select count(*) from userinfo where username = '' and password = '' or '1'='1'

登录方式3
用户名：' or 1 = 1 --
密码：'
select count(*) from userinfo where username = '' or 1 = 1 --' and password = '''

其它方式（没试成功，据说可以，对某些数据库可能会成功。）
用户名：空
密码：';drop table（或其它DDL语句，比如drop一个数据库）
select count(*) from userinfo = '' and passwd = '';drop table userinfo;--'

在jsp中，防止SQL注入攻击一般有两种办法：
1 字符串过滤 （http://sourceforge.net/projects/antisqlfilter）
2 放弃使用Statement，而改用preparedstatement（笔者代码中已经作了尝试）

附录A：参考资源
[1] sql语句过滤 http://sourceforge.net/projects/antisqlfilter
[2] 关于JSP防范SQL注入攻击 http://blog.niwota.com/a/136439.htm
[3] SQL注入漏洞攻防必杀技 http://dev.yesky.com/SoftChannel/72348977504190464/20050404/1930840.shtml

附录B：建表语句
create table userinfo(
  id int primary key auto_increment,
  username varchar(40),
  password varchar(100));

附录C：jsp文件源代码
<%@ page contentType="text/html;charset=gbk" %>
<%@ page import="java.sql.*" %>
<%!
public boolean validateUser(String username, String userpwd) {
    String url ="jdbc:mysql://localhost/mydb?user=admin&password=admin&characterEncoding=GB2312";
    String user = "admin";
    String password = "admin";

    Connection conn = null;
    Statement stmt = null;
    ResultSet res = null;

    try {
        //Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        Class.forName("com.mysql.jdbc.Driver");
    }
    catch(ClassNotFoundException e) {
        System.out.println("Failed to load mSQL driver.");
        return false;
    }

    String sql = "select count(*) from userinfo where username = '" + username +
        "' and password = '" + userpwd + "'";

    System.out.println(sql);
    int cnt = 0;

    try {
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.createStatement();
        res = stmt.executeQuery(sql);        

        while(res.next()) { // process results one row at a time
            cnt = res.getInt(1);
        }
    }
    catch(SQLException e) {
        e.printStackTrace();
    } finally {
        if (res != null) try { res.close(); } catch (SQLException e) { }
        if (stmt != null) try { stmt.close(); } catch (SQLException e) { }
        if (conn != null) try { conn.close(); } catch (SQLException e) { }
    }

    return (cnt > 0);
}

public boolean validateUser2(String username, String userpwd) {
    String url ="jdbc:mysql://localhost/mydb?user=admin&password=admin&characterEncoding=GB2312";
    String user = "admin";
    String password = "admin";

    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet res = null;

    try {
        //Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        Class.forName("com.mysql.jdbc.Driver");
    }
    catch(ClassNotFoundException e) {
        System.out.println("Failed to load mSQL driver.");
        return false;
    }

    String sql = "select count(*) from userinfo where username = ? and password = ?";

    int cnt = 0;

    try {
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, username);
        stmt.setString(2, userpwd);
        res = stmt.executeQuery();        

        while(res.next()) { // process results one row at a time
            cnt = res.getInt(1);
        }
    }
    catch(SQLException e) {
        e.printStackTrace();
    } finally {
        if (res != null) try { res.close(); } catch (SQLException e) { }
        if (stmt != null) try { stmt.close(); } catch (SQLException e) { }
        if (conn != null) try { conn.close(); } catch (SQLException e) { }
    }

    return (cnt > 0);
}
%>

<%
String op = request.getParameter("op");
if ("login".equals(op)) {
    String username = request.getParameter("username");
    String userpwd = request.getParameter("userpwd");
    if (validateUser(username, userpwd)) out.println("login ok");
    //if (validateUser2(username, userpwd)) out.println("login ok");
    else out.println("login faild");
}
%>

<html>
<head>
<meta http-equiv="Content-Language" content="zh-cn">
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>sql注入攻击</title>
</head>

<body>
<form method="POST" action="sqlinject.jsp?op=login">
    <p>
    用户名：<input type="text" name="username" size="20"><br>
&nbsp; 密码：<input type="text" name="userpwd" size="20"><br>
 <br>
 <input type="submit" value="提交" name="B1"></p>
</form>
</body>
</html>