Statement
package com.cloud.statement.crud;
import org.junit.Test;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
public class StatementTest {
@Test
public void testLogin(){
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名: ");
String user = scanner.next();
// String user = "AA";
System.out.print("请输入密码: ");
String password = scanner.next();
// String password = "123456";
String sql = "select user,password from user_table where user = '"+ user +"' AND password = '"+ password +"'";
User returnUser = get(sql,User.class);
if (returnUser != null){
System.out.println("登录成功");
}else{
System.out.println("用户名不存在或密码错误");
}
}
//使用Statement实现对数据表的查询操作
public <T> T get(String sql,Class<T> clazz){
T t = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
//1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
//2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//3.加载驱动
Class.forName(driverClass);
//4.获取连接
conn = DriverManager.getConnection(url,user,password);
st = conn.createStatement();
rs = st.executeQuery(sql);
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()){
t = clazz.newInstance();
for(int i = 0;i< columnCount; i++){
//1.获取列的别名
String columnName = rsmd.getColumnLabel(i + 1);
//2.根据列名获取对应数据表中的数据
Object columnVal = rs.getObject(columnName);
//3.将数据表中得到的数据，封装进对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnVal);
}
return t;
}
}catch(Exception e)
{
e.printStackTrace();
}finally {
//关闭资源
if (rs!=null){
try{
rs.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(st!=null)
{
try{
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
return null;
}
}
0.gif
弊端sql注入的问题
package com.cloud.statement.crud;
import org.junit.Test;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Properties;
import java.util.Scanner;
public class StatementTest {
//使用Statement的弊端:需要拼写sql语句，并且存在SQL注入的问题
@Test
public void testLogin(){
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名: ");
// String user = scanner.next();
String user = scanner.nextLine();
// String user = "AA";
System.out.print("请输入密码: ");
// String password = scanner.next();
String password = scanner.nextLine();
// String password = "123456";
//select user,password from firstDB.user_table where user = '1' or ' AND password = '=1 or '1' = '1';
String sql = "select user,password from user_table where user = '"+ user +"' AND password = '"+ password +"'";
User returnUser = get(sql,User.class);
if (returnUser != null){
System.out.println("登录成功");
}else{
System.out.println("用户名不存在或密码错误");
}
}
//使用Statement实现对数据表的查询操作
public <T> T get(String sql,Class<T> clazz){
T t = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
//1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
//2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//3.加载驱动
Class.forName(driverClass);
//4.获取连接
conn = DriverManager.getConnection(url,user,password);
st = conn.createStatement();
rs = st.executeQuery(sql);
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()){
t = clazz.newInstance();
for(int i = 0;i< columnCount; i++){
//1.获取列的别名
String columnName = rsmd.getColumnLabel(i + 1);
//2.根据列名获取对应数据表中的数据
Object columnVal = rs.getObject(columnName);
//3.将数据表中得到的数据，封装进对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnVal);
}
return t;
}
}catch(Exception e)
{
e.printStackTrace();
}finally {
//关闭资源
if (rs!=null){
try{
rs.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(st!=null)
{
try{
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
return null;
}
}
1.gif
2.gif
很明显，如果用正确的账号密码，是可以查询到到信息。
select user,password from firstDB.user_table where user = 'AA' AND password = '123456';
但是如果执行一条错误的语句，也能查询到信息。
select user,password from firstDB.user_table where user = '1' or ' AND password = '=1 or '1' = '1';
这就是Statement的弊端。需要拼写sql语句，并且存在sql注入的问题。如何避免出现sql注入，只要用PreparedStatement(从Statement扩展而来)取代Statement
网友评论