【学习笔记】sql注入问题和Preparement
sql注入问题
SQL 注入就是在用户输入的字符串中加入 SQL 语句,如果在设计不良的程序中忽略了检查,那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行,攻击者就可以执行计划外的命令或访问未被授权的数据。
例如:
恶意拼接查询
select * from users where id = $user_id
如果传入的参数为:“1234;delete from users”,那么这句sql就变成了
select * from users where id = 1234;delete from users
最终导致users表中的所有数据都被删除。
除此之外还有:利用注释执行非法命令、传入非法参数、添加额外条件
都会导致信息泄露或资源浪费等情况。
代码案例:
package com.lesson02;
import com.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlInject {
public static void main(String[] args) throws SQLException {
login("'or' 1=1","'or' 1=1");
}
//登录
public static void login(String username,String password) throws SQLException {
Connection connection = JdbcUtils.getConnection();
Statement statement = connection.createStatement();
String sql = "select * from users where `NAME` = '"+username+"' and `password` = '" + password + "'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("name:" + resultSet.getObject("NAME"));
System.out.println("password" + resultSet.getObject("PASSWORD"));
System.out.println("==========================");
}
JdbcUtils.release(connection,statement,resultSet);
}
}
从上面的例子可以看出,我们传入的username和password都不是正确的
而是 “ ‘ or ’ 1=1 ” ,这句sql就变成了
select * from users where `NAME` = '' or '1=1' and `password` = '' or '1=1'
也就是name和password等于空 或永远等于true
所以就把所有的user信息打印了出来,导致信息的泄露
Preparement 对象
-
Preparement 是 Statement的子类
-
更安全,可以防止sql注入
-
在执行sql语句前,需要预编译sql
-
sql中的参数用 ? 来占位
-
预编译完成后,要手动赋值
例子:
-
插入数据
package com.lesson03; import com.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Date; public class PreparementInsert { public static void main(String[] args) throws SQLException { Connection connection = JdbcUtils.getConnection(); //使用 ? 占位符 代替参数 String sql = "insert into users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)values (?,?,?,?,?)"; //先预编译sql PreparedStatement ps = connection.prepareStatement(sql); //手动给参数赋值 ps.setInt(1,5); //第一个参数是表示第几个占位符即第几个? 第二个参数是具体的值 ps.setString(2,"xiaohong"); ps.setString(3,"123561"); ps.setString(4,"123456@123.com"); ps.setDate(5,new java.sql.Date(new Date().getTime())); int i = ps.executeUpdate(); if (i>0){ System.out.println("插入成功"); } JdbcUtils.release(connection,ps,null); } }
-
删除数据
package com.lesson03; import com.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class PreparementDelete { public static void main(String[] args) throws SQLException { Connection connection = JdbcUtils.getConnection(); String sql = "delete from users where `NAME` = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,"xiaohong"); int i = ps.executeUpdate(); if (i>0){ System.out.println("删除成功"); } JdbcUtils.release(connection,ps,null); } }
-
修改数据
package com.lesson03; import com.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Date; public class PreparementUpdate { public static void main(String[] args) throws SQLException { Connection connection = JdbcUtils.getConnection(); String sql = "update users set `NAME` = ?,`PASSWORD` = ?,`email`= ?,`birthday`=? where `id` = ?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1,"小张三"); ps.setString(2,"123小张三"); ps.setString(3,"小张三@qq.com"); ps.setDate(4,new java.sql.Date(new Date().getTime())); ps.setInt(5,4); int i = ps.executeUpdate(); if (i>0){ System.out.println("修改成功"); } JdbcUtils.release(connection,ps,null); } }
-
查询数据
package com.lesson03; import com.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class PreparementQuery { public static void main(String[] args) throws SQLException { Connection connection = JdbcUtils.getConnection(); String sql = "select * from users where id=?"; PreparedStatement ps = connection.prepareStatement(sql); ps.setInt(1,4); ResultSet resultSet = ps.executeQuery(); while (resultSet.next()){ System.out.println("id:" + resultSet.getInt("id")); System.out.println("name:" + resultSet.getString("NAME")); System.out.println("pwd:" + resultSet.getString("PASSWORD")); System.out.println("email:" + resultSet.getString("email")); System.out.println("birthday:" + resultSet.getDate("birthday")); } JdbcUtils.release(connection,ps,resultSet); } }
Preparement 防止sql注入的本质是把用户传进来的参数当成字符串