1 sql注入
1.1 使用字符串拼接导致数据异常 sql语句拼接
// 构建SQL查询语句，注意这里存在SQL注入风险
String sql = "select name,age from user where name='" + username + "' and password ='" + password + "'";
System.out.println("sql语句为:" + sql);
1.2 若使用超级用户名登录则造成数据泄露
public static void main(String[] args) {
// 测试用例1：正常用户名
// String username = "张三";
// 测试用例2：恶意构造的用户名，用于SQL注入攻击
String username1 = "'or 1=1 or' '='";
boolean flag = login(username1, "111111111111");
System.out.println(flag);
}
输出结果
1.3 非法登录 绕过验证
1.4 先通过用户名验证 再验证密码
// 如果查询结果有记录，说明用户名存在
if (resultSet.next()) {
// 2. 继续验证取出的密码
String pass = resultSet.getString("password");
// 3. 继续判断数据库中的密码与用户传入的密码是否匹配
if (pass != null && pass.equals(password)) {
// 密码匹配成功
return true;
} else {
// 密码匹配失败
return false;
}
}
2 预处理
执行sql语句有 Statement PreparedStatement(防注入，特殊字符处理，效率高)
预处理使用占位符,之后再赋值
// 使用预编译的SQL插入语句
String sql1 = "insert into user(password,name,phone)" + "values(?,?,?)";
// 使用预编译的SQL语句
PreparedStatement ps = null;
try {
// 预编译SQL语句
ps = conn.prepareStatement(sql1);
// 为预编译语句中的占位符赋值
ps.setString(1, "999999");
ps.setString(2, "测试ps");
ps.setString(3, "138457856523");
// 执行预编译的SQL语句并获取影响的行数
int row = ps.executeUpdate();
System.out.println("-----" + row);
3 加载配置文件
加载配置文件
• 在文件中配置连接属性
import com.yw.utils.ConnectionUtil;
import java.sql.Connection;
public class test {
public static void main(String[] args) {
/**
* 配置文件读取成功
*/
Connection conn = ConnectionUtil.getConn();
System.out.println(conn);
}
}
• 在连接工具类中使用读取属性文件的方式获取连接参数
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 创建连接
*/
public class ConnectionUtil {
/**
* io流
*/
private static String driver;
private static String url;
private static String username;
private static String password;
static {
InputStream is = ConnectionUtil.class.getResourceAsStream("/db.properties");
Properties p = new Properties();
try {
p.load(is);
driver = p.getProperty("driver");
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
// password = p.getProperty("password");
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 建立连接方法
* @return
*/
public static Connection getConn(){
Connection conn = null;
try {
Class.forName(driver);//加载驱动
conn = DriverManager.getConnection(url,username,password);//给了三个参
} catch (Exception e) {
throw new RuntimeException(e);
}
return conn;
}
public static void close(Connection connection,Statement statement,ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
• db.properties 直接使用文本文件进行编辑
username = root password = root driver = com.mysql.cj.jdbc.Driver url = jdbc:mysql://localhost:3306/java_demo1
4 元数据集
import com.sun.xml.internal.ws.addressing.WsaActionUtil;
import com.yw.utils.ConnectionUtil;
import java.sql.*;
public class test1 {
public static void main(String[] args) {
String sql = "select * from user";
query(sql);
}
public static void query(String sql){
Connection conn = ConnectionUtil.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
/*ResultSetMetaData metaData = rs.getMetaData();//元数据
int count = metaData.getColumnCount();//获取列数
System.out.println(count);
for (int i = 0; i < count; i++) {
String catalogName = metaData.getColumnName(i+1);
int columnType = metaData.getColumnType(i + 1);
System.out.println(catalogName+"==="+columnType);
}*/
ResultSetMetaData rsd = rs.getMetaData();//获取元数据
String columnClassName = rsd.getColumnClassName(1);//返回字段类型
int columnType = rsd.getColumnType(2);
String columnName = rsd.getColumnName(1);
String columnName1 = rsd.getColumnName(2);
System.out.println(columnClassName+"====="+columnType+"=========="+columnName+"========"+columnName1);
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
ConnectionUtil.close(conn,ps,rs);
}
}
}
5 封装BaseDao
BaseDao
import com.yw.utils.ConnectionUtil;
import java.sql.*;
public class BaseDao {
private String username ="root";
private String password ="123456";
private String driver ="com.mysql.cj.jdbc.Driver";
private String url = "jdbc:mysql://localhost:3306/java_demo1";
private Connection connection;//连接
private PreparedStatement ps;//执行sql语句
private ResultSet rs;//返回结果集
/**
* 连接方法
* @return
*/
public Connection getConnection(){
try {
if(connection == null || connection.isClosed()){ // 或者已经关闭
Class.forName(driver);//加载驱动
connection = DriverManager.getConnection(url,username,password);
}
return connection;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/* public Connection getConnection(){
try {
if(connection == null || connection.isClosed()) {
Class.forName(driver);
connection = DriverManager.getConnection(url,username,password);
}
return connection;
} catch (Exception e) {
throw new RuntimeException(e);
}
}*/
/* public int update(String sql,Object ... params){ //sql以参数的形式传入
connection = getConnection();
System.out.println(sql);
try {
ps = connection.prepareStatement(sql);
*//*int i = ps.executeUpdate();
return i;*//*
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
int i = ps.executeUpdate();
return i;
}catch (Exception e){
e.printStackTrace();
}
return -1;
}*/
/**
* 更新方法
* @param sql
* @param params 增加 加一 删除 少一个 修改 where id= ? 返回影响行
* @return
*/
public int update(String sql,Object...params){
connection = getConnection();//获得连接
System.out.println(sql);
try {
ps = connection.prepareStatement(sql);//使用预编译 占位符
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
int i = ps.executeUpdate();//返回影响行数
return i;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/* public ResultSet query(String sql,Object... params){
System.out.println(sql);
connection = getConnection();//连接
try {
ps = connection.prepareStatement(sql);//预编译
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
rs = ps.executeQuery();
return rs;
} catch (Exception e) {
throw new RuntimeException(e);
}
}*/
/**
*
* @param sql
* @param params 查询
* @return
*/
public ResultSet Query(String sql,Object...params){
connection = getConnection();
try {
ps = connection.prepareStatement(sql);
for (int i = 1; i < params.length; i++) {
ps.setObject(i,params[i]);
}
rs = ps.executeQuery();
return rs;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public void close(){
try {
if(rs != null){
rs.close();
rs = null;
}
if(ps != null){
ps.close();
ps = null;
}
if(connection != null){
connection.close();
connection = null;
}
}catch (Exception e){
e.printStackTrace();
}
}
}
TestBase
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestBase {
public static void main(String[] args) throws SQLException {
/* String sql = "insert into user(password,name,age,phone)values(?,?,?,?)";
Object[] params = {"654321","basedao",12,"1547895132"};
BaseDao baseDao = new BaseDao();
int i = baseDao.update(sql,params);
*/
/**
* 测试更新方法
*/
/* String sql = "insert into user(password,name,age,phone)values(?,?,?,?)";
BaseDao baseDao = new BaseDao();
Object[] params = {"898989","测试params",15,"189878564"};
baseDao.update(sql,params);*/
/*
String sql = "select password,name,phone from user";
BaseDao baseDao = new BaseDao();
ResultSet query = baseDao.query(sql);//不能关闭
try{
while (query.next()){
String password = query.getString("password");
String name = query.getString("name");
String phone = query.getString("phone");
System.out.println(password+name+password);
}
}catch (Exception e){
e.printStackTrace();
}*/
String sql1 = "select password,name, phone from user";
BaseDao baseDao = new BaseDao();
ResultSet query = baseDao.Query(sql1);
while (query.next()){
String pass = query.getString("password");
String name = query.getString("name");
String phone = query.getString("phone");
System.out.println("pass="+pass+"name="+name+"phone="+phone);
}
}
}
