MySQL - java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)
有一个接口查询邮件的列表时,因为我们想要支持模糊查询,而我又不想在xml文件中配置查询语句,想着直接使用SQL来实现
@Select("SELECT * FROM email WHERE name=#{name} like '%#{name}%'")
List<Email> selectEmailList(@Param("name") String emailName);
结果跑起来就报错了,看起来不是我想这那样,报错信息如下:
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3326)
at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3310)
at com.mysql.jdbc.PreparedStatement.setString(PreparedStatement.java:4146)
at ado.User_ADO.selOperation(User_ADO.java:60)
at unit.ProssSeverl.rundom(ProssSeverl.java:95)
at unit.ProssSeverl.regidit(ProssSeverl.java:71)
at unit.ProssSeverl.Dealwith(ProssSeverl.java:43)
at unit.ProssSeverl.service(ProssSeverl.java:36)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol
然后看到有博主这样写类似ibatis的模糊查询;
无效的方法:
select * from table1 where name like '%#name#%'
使用$代替#。此种方法就是去掉了类型检查,使用字符串连接,不过可能会有sql注入风险。
select * from table1 where name like '%$name$%'
这样也给我了一些启发,下面两种写法都是可以的:
@Select("SELECT * FROM email WHERE name like '%${name}%'")
List<Email> selectEmailList(@Param("name") String emailName);
@Select("SELECT * FROM email WHERE name like concat('%', #{name}, '%')")
List<Email> selectEmailList(@Param("name") String emailName);
编写MyBatis的映射语句时,尽量采用“#{xxx}”这样的格式。若不得不使用“${xxx}”这样的参数,要手工地做好过滤工作,来防止SQL注入攻击。
#{}:相当于JDBC中的PreparedStatement
${}:是输出变量的值
简单说,#{}是经过预编译的,是安全的;${}是未经过预编译的,仅仅是取变量的值,是非安全的,存在SQL注入。
done.