安装 在我们开始之前,我们需要在Java程序中使用SQLite,以确保我们有SQLite的JDBC驱动程序和Java的机器上。可以查看如何在机器上安装Java的教程。现在,让我们来检查一下如何设置SQLite的JDBC驱动程序。 下载 sqlite-jdbc-(VERSION).jar 的最新版本: sqlite-jdbc . 添加下载的jar文件sqlite-jdbc-(VERSION).jar 在类路径中,可以用带 - classpath选项,如下面例子。 前提条件 在学习本教程,应该对Java编程语言的一个很好的理解。因为要处理RDBMS,所以它需要对SQL和数据库的概念有很好的理解。 JDBC代表Java与数据库的连接,这对Java编程语言和广泛的数据库之间独立于数据库的连接标准的Java API。…
Java: SQLite 数据库基本操作 之 去除大小写敏感,防注入,execute,executeQuery和executeUpdate的区别
去除大小写敏感
大部分数据库在进行字符串比较的时候,对大小写是不敏感的。但是,最近使用SQLite的时候,却发现它的情况恰好相反。
假设表User的结构和值如下:
UserName | |
---|---|
1 | User1 |
执行下面的SQL语句:
SELECT * FROM [User] WHERE UserName = 'user1'
结果是没有找到任何记录。明显地,SQLite在进行字符串比较的时候,默认对大小写是敏感的。这会对应用程序造成不良的影响。比如,用户在输入用户名的时候必须严格按照大小写输入,这是一种很不好的用户体验;用户表中可能同时存在 user 和 USER 两个用户,容易造成混淆。
由于SQLite是近年来才兴起的数据库,中文资料少得很。Google了一些英文资料,终于找到了三种解决方案:
方案一:使用大小写转换函数LOWER、UPPER
SELECT * FROM [User] WHERE LOWER(UserName) = 'user1'
方案二:在进行比较时强制声明不区分大小写
SELECT * FROM [User] WHERE UserName = 'user1' COLLATE NOCASE
方案三:创建表时声明该字段不区分大小写
CREATE TABLE [User] ( [UserName] NVARCHAR(20) COLLATE NOCASE );
如果在任何情况下都不需要对大小写敏感,方案三是最好的解决方案;如果只是少量查询对大小写不敏感,可以用方案二。而方案一由于用到了函数,可能有额外的性能损失,不推荐使用。
防注入
对java有了解的同学基本上都体验过JDBC,基本都了解PreparedStatement,PreparedStatement相比Statement基本解决了SQL注入问题,而且效率也有一定提升。
关于PreparedStatement和Statement其他细节我们不讨论,只关心注入问题。无论读者是老鸟还是菜鸟,都需要问一下自己,PreparedStatement真的百分百防注入吗?
接下来我们研究一下PreparedStatement如何防止注入,本文以MySQL数据库为例。
为了避免篇幅过长,我这里只贴代码片段,希望读者能有一定的基础。
String sql = "select * from goods where min_name = ?"; // 含有参数 PreparedStatement st = conn.prepareStatement(sql); st.setString(1, "儿童"); // 参数赋值 System.out.println(st.toString()); //com.mysql.jdbc.JDBC4PreparedStatement@d704f0: select * from goods where min_name = '儿童'
这段代码属于JDBC常识了,就是简单的根据参数查询,看不出什么端倪,但假如有人使坏,想注入一下呢?
String sql = "select * from goods where min_name = ?"; // 含有参数 PreparedStatement st = conn.prepareStatement(sql); st.setString(1, "儿童'"); // 参数赋值 System.out.println(st.toString()); //com.mysql.jdbc.JDBC4PreparedStatement@d704f0: select * from goods where min_name = '儿童\''
简单的在参数后边加一个单引号,就可以快速判断是否可以进行SQL注入,这个百试百灵,如果有漏洞的话,一般会报错。
之所以PreparedStatement能防止注入,是因为它把单引号转义了,变成了\’,这样一来,就无法截断SQL语句,进而无法拼接SQL语句,基本上没有办法注入了。
所以,如果不用PreparedStatement,又想防止注入,最简单粗暴的办法就是过滤单引号,过滤之后,单纯从SQL的角度,无法进行任何注入。
其实,刚刚我们提到的是String参数类型的注入,大多数注入,还是发生在数值类型上,幸运的是PreparedStatement为我们提供了st.setInt(1, 999);这种数值参数赋值API,基本就避免了注入,因为如果用户输入的不是数值类型,类型转换的时候就报错了。
好,现在读者已经了解PreparedStatement会对参数做转义,接下来再看个例子。
String sql = "select * from goods where min_name = ?"; // 含有参数 PreparedStatement st = conn.prepareStatement(sql); st.setString(1, "儿童%"); // 参数赋值 System.out.println(st.toString()); //com.mysql.jdbc.JDBC4PreparedStatement@8543aa: select * from goods where min_name = '儿童%'
我们尝试输入了一个百分号,发现PreparedStatement竟然没有转义,百分号恰好是like查询的通配符。
正常情况下,like查询是这么写的:
String sql = "select * from goods where min_name like ?"; // 含有参数 st = conn.prepareStatement(sql); st.setString(1, "儿童" + "%"); // 参数赋值 System.out.println(st.toString()); //com.mysql.jdbc.JDBC4PreparedStatement@8543aa: select * from goods where min_name like '儿童%'
查询min_name字段以”儿童”开头的所有记录,其中”儿童”二字是用户输入的查询条件,百分号是我们自己加的,怎么可能让用户输入百分号嘛!等等!如果用户非常聪明,偏要输入百分号呢?
String sql = "select * from goods where min_name like ?"; // 含有参数 st = conn.prepareStatement(sql); st.setString(1, "%儿童%" + "%"); // 参数赋值 System.out.println(st.toString()); //com.mysql.jdbc.JDBC4PreparedStatement@8543aa: select * from goods where min_name like '%儿童%%'
聪明的用户直接输入了”%儿童%”,整个查询的意思就变了,变成包含查询。实际上不用这么麻烦,用户什么都不输入,或者只输入一个%,都可以改变原意。
虽然此种SQL注入危害不大,但这种查询会耗尽系统资源,从而演化成拒绝服务攻击。
那如何防范呢?笔者能想到的方案如下:
·直接拼接SQL语句,然后自己实现所有的转义操作。这种方法比较麻烦,而且很可能没有PreparedStatement做的好,造成其他更大的漏洞,不推荐。
·直接简单暴力的过滤掉%。笔者觉得这方案不错,如果没有严格的限制,随便用户怎么输入,既然有限制了,就干脆严格一些,干脆不让用户搜索%,推荐。
目前做搜索,只要不是太差的公司,一般都有自己的搜索引擎(例如著名的java开源搜索引擎solr),很少有在数据库中直接like的,笔者并不是想在like上钻牛角尖,而是提醒读者善于思考,每天都在写着重复的代码,却从来没有停下脚步细细品味。
有读者可能会问,为什么我们不能手动转义一下用户输入的%,其他的再交给PreparedStatement转义?这个留作思考题,动手试一下就知道为什么了。
注意,JDBC只是java定义的规范,可以理解成接口,每种数据库必须有自己的实现,实现之后一般叫做数据库驱动,本文所涉及的PreparedStatement,是由MySQL实现的,并不是JDK实现的默认行为,也就是说,不同的数据库表现不同,不能一概而论。
简单实例:
String query = "INSERT INTO Justcode_iKeepstudying (id, name, country) VALUES (?, ?,?)"; //String query = "UPDATE Justcode_iKeepstudying SET name= ? WHERE id = ?"; try { preparedStatement = conn.prepareStatement(query); preparedStatement.setInt(1, i); preparedStatement.setString(2, line); preparedStatement.setString(3, "EU"); preparedStatement.executeUpdate(); i++; } catch (SQLException e) { e.printStackTrace(); }
execute,executeQuery和executeUpdate的区别
1>方法executeQuery
用于产生单个结果集(ResultSet)的语句,例如:被执行最多的SELECT 语句。
这个方法被用来执行 SELECT 语句,但也只能执行查询语句,执行后返回代表查询结果的ResultSet对象。例如:
Class.forName("com.mysql.jdbc.Driver"); //加载数据库驱动 Connection conn = null; conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root","1234"); //使用DriverManager获取数据库连接 Statement stmt = conn.createStatement(); //使用Connection来创建一个Statment对象 ResultSet rs =stmt.executeQuery("select * from teacher"); //执行查询语句并且保存结果 while (rs.next()){ System.out.println(rs.getInt(1) + "/t" + rs.getString(2)); } //把查询结果输出来
2>方法executeUpdate
用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL(数据定义语言)语句,例如 CREATE TABLE 和 DROP TABLE。
INSERT、UPDATE 或 DELETE 语句的效果是修改表中零行或多行中的一列或多列。
executeUpdate 的返回值是一个整数(int),指示受影响的行数(即更新计数)。
对于 CREATE TABLE 或 DROP TABLE 等不操作行的语句,executeUpdate 的返回值总为零。例如:
Class.forName("com.mysql.jdbc.Driver"); //加载数据库驱动 Connection conn = null; conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root","1234"); //使用DriverManager获取数据库连接 Statement stmt = conn.createStatement(); //使用Connection来创建一个Statment对象 return stmt.executeUpdate(sql); //执行DML语句,返回受影响的记录条数
3>方法execute:
可用于执行任何SQL语句,返回一个boolean值,表明执行该SQL语句是否返回了ResultSet。
如果执行后第一个结果是ResultSet,则返回true,否则返回false。
但它执行SQL语句时比较麻烦,通常我们没有必要使用execute方法来执行SQL语句,而是使用executeQuery或executeUpdate更适合。
但如果在不清楚SQL语句的类型时则只能使用execute方法来执行该SQL语句了。例如:
Class.forName(driver); //加载驱动 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root","1234"); //获取数据库连接 stmt = conn.createStatement(); //使用Connection来创建一个Statment对象 boolean hasResultSet = stmt.execute(sql); //执行SQL,返回boolean值表示是否包含ResultSet if (hasResultSet) { //如果执行后有ResultSet结果集 rs = stmt.getResultSet(); //获取结果集 ResultSetMetaData rsmd = rs.getMetaData(); //ResultSetMetaData是用于分析结果集的元数据接口 int columnCount = rsmd.getColumnCount(); while (rs.next()){//输出ResultSet对象 for (int i = 0 ; i < columnCount ; i++ ) { System.out.print(rs.getString(i + 1) + "/t"); } System.out.print("/n"); } } else { System.out.println("该SQL语句影响的记录有" + stmt.getUpdateCount() + "条"); }
提供一个查询方法:
public HashMap<Integer, HashMap<String,String>> find(Connection conn, String sql) { try { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData rsm = rs.getMetaData(); HashMap<Integer, HashMap<String,String>> data = new HashMap<>(); int j = 0; while (rs.next()) { HashMap<String, String> row = new HashMap<>(); for (int i = 1; i <= rsm.getColumnCount(); i++) {// 数据库里从 1 开始 String c = rsm.getColumnName(i); String v = rs.getString(c); row.put(c,v); } data.put(j, row); j++; } stmt.close(); rs.close(); return data; } catch (Exception e) { return null; } }
更多参考:Java: SQLite 数据库基本操作
本文:Java: SQLite 数据库基本操作 之 去除大小写敏感,防注入,execute,executeQuery和executeUpdate的区别