首先看一段代码:
模拟用户登录:
public class UserLogin {
public static void main(String[] args) {
Map<String,String> map = initUI();
boolean flag = check(map.get("username"),map.get("password"));
System.out.println(flag?"登录成功":"登录失败");
}
private static boolean check(String username, String password) {
Connection conn = null;
Statement stat = null;
ResultSet res = null;
try {
ResourceBundle rb = ResourceBundle.getBundle("resource/db");
// 1、注册驱动
String driver = rb.getString("driver");
Class.forName(driver);
// 2、获取连接
String url = rb.getString("url");
String user = rb.getString("user");
String pw = rb.getString("password");
conn = DriverManager.getConnection(url, user, pw);
// 3、获取数据库操作对象
stat = conn.createStatement();
// 4、执行SQL
String sql = "select * from t_user where username='"+username+"' and password='"+password+"'";
res = stat.executeQuery(sql);
// 5、处理查询结果集
if (res.next()){
return true;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat!=null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return false;
}
private static Map<String, String> initUI() {
Scanner sca = new Scanner(System.in);
System.out.print("请输入用户名:");
String username=sca.next();
System.out.print("请输入密码:");
String password=sca.next();
Map<String,String> map = new HashMap<>();
map.put("username", username);
map.put("password", password);
return map;
}
}
db.properties:
# mysql 连接信息
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bjpowernode
user=root
password=**********
解释:上面的代码是模拟用户登录的密码,如果我正常的输入数据库中已有的username和password,就会出现登录成功字样,如果我输入的username是lisi,password是lisi’or’1’='1,这样也会登录成功,这就是出现了SQL注入的问题,导致SQL注入的根本原因是:用户输入的信息中含有SQL语句中的关键字,并且这些关键字参与了SQL编译;解决SQL注入的方法:使用PreparedStatement对SQL语句进行预编译,然后给占位符?传值,即使用户输入的信息中含有SQL语句的关键字,但是关键字没有参与编译也是不会起作用的,这样可以防止SQL注入问题;
解决SQL注入问题之后的用户登录代码如下:
public class UserLogin {
public static void main(String[] args) {
Map<String,String> map = initUI();
boolean flag = check(map.get("username"),map.get("password"));
System.out.println(flag?"登录成功":"登录失败");
}
private static boolean check(String username, String password) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
try {
ResourceBundle rb = ResourceBundle.getBundle("resource/db");
// 1、注册驱动
String driver = rb.getString("driver");
Class.forName(driver);
// 2、获取连接
String url = rb.getString("url");
String user = rb.getString("user");
String pw = rb.getString("password");
conn = DriverManager.getConnection(url, user, pw);
// 3、获取预编译的数据库操作对象
String sql = "select * from t_user where username = ? and password = ?";// ?是占位符,接受将来的值
ps = conn.prepareStatement(sql);// 把上面的SQL语句框架发送给DBMS,然后DBMS进行编译
ps.setString(1, username);// 给占位符传值
ps.setString(2, password);
// 4、执行SQL
res = ps.executeQuery();
// 5、处理查询结果集
if (res.next()){
return true;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return false;
}
private static Map<String, String> initUI() {
Scanner sca = new Scanner(System.in);
System.out.print("请输入用户名:");
String username=sca.next();
System.out.print("请输入密码:");
String password=sca.next();
Map<String,String> map = new HashMap<>();
map.put("username", username);
map.put("password", password);
return map;
}
}
解释:其实就是把JDBC的第3步和第4步改了一下,在第3步中先把SQL语句框架写出来,里面需要传的参数用占位符?代替,然后获得预编译的数据库操作对象,然后根据占位符的数据类型通过不同的方法给占位符传值,方法里面第一个参数代表第几个占位符,这样来说,Statement由于会发生SQL注入问题,那它就没有作用了吗,当然不是,两个作用不同,下面我们进行Statement和PreparedStatement 的对比:1、Statement会导致SQL注入,PreparedStatement会防止SQL注入;2、Statement适合SQL语句拼接的业务,PreparedStatement 适合传值的业务,大多数情况使用PreparedStatement ;3、PreparedStatement 会在编译阶段进行类型的安全检查,也就是方法和里面传输的值的类型需要对应起来;4、PreparedStatement 效率更高,因为它编译一次,可以执行多次,因为如果两条SQL语句相同,那么你执行多次其实也就编译了一次,然后运行了多次,如果两条SQL语句有细微不同,甚至就是一个空格,也是编译两次的,上面说了Statement和PreparedStatement 的不同,下面给出实际的例子,比如通过输入不同的asc和desc来让该改变薪资的排列方式,其实这种运用的也很广泛,比如京东的手机价格排序、淘宝的商品价格排序都是可以升序和降序的,先弄一个出错的吧,使用PreparedStatement 例子如下:
public class Query2 {
public static void main(String[] args) {
Scanner sca = new Scanner(System.in);
System.out.print("请输入desc或者asc完成员工薪资的降序或者升序排列:");
String orderKey = sca.next();
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
try {
ResourceBundle rb = ResourceBundle.getBundle("resource/db");
// 1、注册驱动
String driver = rb.getString("driver");
Class.forName(driver);
// 2、获取连接
String url = rb.getString("url");
String user = rb.getString("user");
String pw = rb.getString("password");
conn = DriverManager.getConnection(url, user, pw);
// 3、获取预编译的数据库操作对象
String sql = "select * from emp order by sal ?";
ps = conn.prepareStatement(sql);
ps.setString(1, orderKey);
// 4、执行SQL
res = ps.executeQuery();
// 5、处理查询结果集
while (res.next()){
System.out.println("ename="+res.getString("ename")+",sal="+res.getString("sal"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
解释:这种会报错的,因为假设你输入desc,那么给占位符赋值的时候是加上’'单引号的,运行的时候不认识’desc’是什么东西,所以会报错,这个时候就需要使用Statement了,使用Statement例子如下:
public class Query {
public static void main(String[] args) {
Scanner sca = new Scanner(System.in);
System.out.print("请输入desc或者asc完成员工薪资的降序或者升序排列:");
String orderKey = sca.next();
Connection conn = null;
Statement stat = null;
ResultSet res = null;
try {
ResourceBundle rb = ResourceBundle.getBundle("resource/db");
// 1、注册驱动
String driver = rb.getString("driver");
Class.forName(driver);
// 2、获取连接
String url = rb.getString("url");
String user = rb.getString("user");
String pw = rb.getString("password");
conn = DriverManager.getConnection(url, user, pw);
// 3、获取数据库操作对象
stat = conn.createStatement();
// 4、执行SQL
String sql = "select * from emp order by sal "+orderKey;
System.out.println(sql);
res = stat.executeQuery(sql);
// 5、处理查询结果集
while (res.next()){
System.out.println("ename="+res.getString("ename")+",sal="+res.getString("sal"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stat!=null){
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
解释:这个就可以正确实现我们想要的作用了,上面提到了我们大多数情况使用的都是PreparedStatement,上面的程序中我们只演示了PreparedStatement对DQL语句的操作,下面我们演示如何摔死PreparedStatement进行DML语句的操作以及LIke模糊查询的使用:
PreparedStatement进行DML语句的操作:
public class CRUD {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
try {
ResourceBundle rb = ResourceBundle.getBundle("resource/db");
// 1、注册驱动
String driver = rb.getString("driver");
Class.forName(driver);
// 2、获取连接
String url = rb.getString("url");
String user = rb.getString("user");
String pw = rb.getString("password");
conn = DriverManager.getConnection(url, user, pw);
// 3、获取预编译的数据库操作对象
/*String sql = "insert into dept values(?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, 70);
ps.setString(2, "天龙部");
ps.setString(3, "东京");*/
/*String sql = "update dept set dname=?,loc=? where deptno=?";
ps = conn.prepareStatement(sql);
ps.setString(1,"营销部");
ps.setString(2,"北京");
ps.setInt(3,70);*/
String sql = "delete from dept where deptno=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 70);
// 4、执行SQL
int i = ps.executeUpdate();
System.out.println(i);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
解释:CRUD分别代表增加(Create)、读取(Retrieve)、更新(Update)和删除(Delete)
PreparedStatement进行Like模糊查询的操作:
public class Like {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
try {
ResourceBundle rb = ResourceBundle.getBundle("resource/db");
// 1、注册驱动
String driver = rb.getString("driver");
Class.forName(driver);
// 2、获取连接
String url = rb.getString("url");
String user = rb.getString("user");
String pw = rb.getString("password");
conn = DriverManager.getConnection(url, user, pw);
// 3、获取预编译的数据库操作对象
String sql = "select * from emp where ename like ?";
ps = conn.prepareStatement(sql);
ps.setString(1, "%S%");
// 4、执行SQL
res = ps.executeQuery();
// 5、处理查询结果集
while (res.next()){
System.out.println(res.getString("ename")+","+res.getString("sal"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (res!=null){
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}