基本的JDBC使用:
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
/**
* 测试查询所有用户的类
*
*/
public class QueryAll {
@Test
public void testQueryAll(){
Connection conn= null;
Statement stmt = null;
ResultSet rs = null;
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url ="jdbc:mysql://localhost:3306/mybase";
String username="root";
String password="xuyiqing";
conn = DriverManager.getConnection(url,username,password);
//3.获取执行sql语句对象
stmt = conn.createStatement();
//4.编写sql语句
String sql = "select * from user";
//5.执行sql语句
rs = stmt.executeQuery(sql);
//6.处理结果集
while(rs.next()){
System.out.println("用户名:"+rs.getString(2)+" 密码:"+rs.getString("upassword"));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
View Code
接下来分析SQL注入问题:
数据库准备:
CREATE DATABASE mybase;
USE mybase;
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(64),
upassword VARCHAR(64)
);
INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");
SELECT * FROM users;
JDBC代码:
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
public class TestLogin {
@Test
public void testLogin() {
try {
login("zhangsan", "123");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void login(String username, String password) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mybase";
String usern = "root";
String pwd = "xuyiqing";
Connection conn = DriverManager.getConnection(url, usern, pwd);
Statement stmt = conn.createStatement();
String sql = "select * from users where " + "username='" + username + "'and upassword='" + password + "'";
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
System.out.println("登录成功");
System.out.println(sql);
} else {
System.out.println("账号或密码错误!");
}
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
View Code
这时候运行,输出:
登录成功
select * from users where username='zhangsan'and upassword='123'
如果这样:
login("zhangsan", "1234");
输出:
账号或密码错误!
但是,如果这样做:
@Test
public void testLogin() {
try {
login("zhangsan' or 'zhangsan", "666");
} catch (Exception ex) {
ex.printStackTrace();
}
}
输出却是登录成功!:
登录成功
select * from users where username='zhangsan' or 'zhangsan'and upassword='666'
明显数据库中没有这个用户和相应的密码,但是登录成功,这里就是简单的SQL注入攻击
解决办法:
1.分开验证,先判断用户存在否
2.预处理对象,使用如下的方法:
package demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
public class TestLogin {
@Test
public void testLogin() {
try {
login("zhangsan' or 'zhangsan", "666");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void login(String username, String password) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mybase";
String usern = "root";
String pwd = "xuyiqing";
Connection conn = DriverManager.getConnection(url, usern, pwd);
String sql = "select * from users where username=? and upassword=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
System.out.println("登录成功");
System.out.println(sql);
} else {
System.out.println("账号或密码错误!");
}
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
}
}
View Code
这时候输出的账号或密码错误,解决了上边的SQL注入问题