我正在尝试检查数据库中是否存在某个用户,然后对该用户执行一些验证过程,如果不存在,则返回入侵者消息。为此,我使用了Java和MySQL。这是我的代码。
public class Check{
public boolean isPresent(){
connect();// private method that connects to db
boolean isAvailable = false;
String query = "SELECT EXISTS(SELECT 1 FROM students WHERE matric =" + this.myId + ")"; // this.myId has been passed into the constructor
try{
Statement statement;
ResultSet resultSet;
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
if(resultSet.absolute(1)){
isAvailable = true;
}else{
isAvailable = false;
}catch(SQLException e){
e.printStackTrace();
}finally{
closeConnection();// private method that closes connection
return isAvailable;
}
}I have checked for a solution like this earlier but it does not seem to解决了这个问题。我得到的错误是:“where子句中的未知列CCE”,还有其他来自JDBC API的错误。在我看来,我从构造器传入的值似乎被截断了。
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'CCE' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2499)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1432) 发布于 2015-12-22 14:26:21
从简单修复到正确性的
1-引用您的ID将修复您的SQL错误(添加‘around id)
String query = "SELECT EXISTS(SELECT 1 FROM students WHERE matric = '" + this.myId + "')"; // this.myId has been passed into the constructor但是,使用subselect表示存在吗?为什么不直接获取学生呢?
2-移除子选择
String query = "SELECT * FROM students WHERE matric = '" + this.myId + "'";
try{
Statement statement;
ResultSet resultSet;
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
if(resultSet.next()){
isAvailable = true;
}else{
isAvailable = false;
} //Missing in your code把myId想象成..。myId = "killer'); DROP TABLE STUDENTS; --" -你不想以这种方式执行它。
String query = "SELECT * FROM students WHERE matric = ?";
try{
PreparedStatement statement;
ResultSet resultSet;
statement = connection.prepareStatement(query);
statement.setString(1, myId);
resultSet = statement.executeQuery();可读性
通过使用try-with-resource,您可以摆脱大多数捕获:
String query = "SELECT * FROM students WHERE matric = ?";
try (PreparedStatement statement = connection.prepareStatement(query)) {
statement.setString(1, myId);
try(ResultSet resultSet = statement.executeQuery()) {
return rs.next();
}
} catch(SQLException se) {
se.printStackTrace();
return false;
}发布于 2015-12-22 14:14:29
SQL查询不是Java语句/表达式。
在变量的位置
this.myId
使用占位符,即
?
示例:"SELECT EXISTS(SELECT 1 FROM students WHERE matric =?"
并将其包含在双引号内。
请记住使用PreparedStatement而不是语句
PreparedStatement pst=connection.createStatement();使用statement = connection.createStatement();创建查询后,只需将myId变量设置为查询即可,如下所示:
pst.setString(1,this.myID);这应该会对你有帮助
发布于 2015-12-22 14:39:30
尝试将id作为参数传递,并使用预准备语句使您的代码更有效
public boolean isUserExsit(String id) {
boolean isDuplicated = false;
Connection connection = lockConnection();
String sql = "SELECT 1 FROM students WHERE matric = ? ";
try {
PreparedStatement statement = connection.prepareStatement(sql);
if (statement != null) {
statement.setString(1, id);
try {
ResultSet results = statement.executeQuery();
if (results != null) {
try {
if (results.next()) {
isDuplicated = true;
}
} catch (Exception resultSetException) {
resultSetException.printStackTrace();
}
results.close();
}
} catch (Exception statmentExcption) {
statmentExcption.printStackTrace();
}
statement.close();
}
} catch (Exception generalException) {
generalException.printStackTrace();
}
releaseConnection(connection);
return isDuplicated;
}https://stackoverflow.com/questions/34409421
复制相似问题