isuservalid方法中的SQL statmenr和if（valid == true）语句有什么问题 [英] What’s wrong with the SQL statmenr in the isuservalid method and the if (valid ==true) statment
本文介绍了isuservalid方法中的SQL statmenr和if（valid == true）语句有什么问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
当我运行文件时，它会在数据库中的值时保持空值。此外，当使用有效标记时，Kees在控制台中说无效，它会在控制台上保持输出无效。你能告诉我什么问题吗。
见下面的代码;
When I run the file it keeps saying null when theirs values in the database. Also, Kees saying invalid in the console when a valid tag is used, it keeps outputting invalid on the console. Can you let me know what the problem is please.
See the code below;
*/
// Creating web servlet for RFIDDao and creating public class which extends HttpServlet
@WebServlet("/RFIDDao")
public class RFIDDao extends HttpServlet {
// Creating private static final serialVersionUID
private static final long serialVersionUID = 1L;
// Declaring GSON utility object
// Connection = null and statement.
Gson gson = new Gson();
Connection conn = null;
static Statement stmt;
public static final String userid = "16038287"; // change this to be your student-id
// Creating topic for the server
public static final String TOPIC_SERVER = userid + "/server";
public static final String BROKER_URL = "tcp://iot.eclipse.org:1883";
//private MqttClient client;
// Creating public void init method for ServletConfig which throws ServletException
public void init(ServletConfig config) throws ServletException {
// init method is run once at the start of the servlet loading
// This will load the driver and establish a connection
super.init(config);
// Adding the mysql workbench username and assword, in order to connect to the database
String user = "rashidd";
String password = "rooSedef6";
// Usiing port 6306 instead of 3306 and connecting to the mudfoot server
String url = "jdbc:mysql://mudfoot.doc.stu.mmu.ac.uk:6306/rashidd";
// Loading the database driver
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception e) {
System.out.println(e);
} // close catch exception e
// Creating try to get a connection with the user/pass
try {
conn = DriverManager.getConnection(url, user, password);
// Print messages out onto the console
System.out.println("Sensor to DB server is up and running\n");
System.out.println("Upload sensor data with http://localhost:8080/IOTServer/RFIDDao?sensorname=xxx&sensorvalue=nnn");
System.out.println("View last sensor reading at http://localhost:8080/IOTServer/RFIDDao?getdata=true\n\n");
// System.out.println("DEBUG: Connection to database successful.");
stmt = conn.createStatement();
//System.out.println("debug: Statement created " + stmt);
} catch (SQLException se) {
se.printStackTrace();
System.out.println(se);
System.out.println("\nDid you alter the lines to set user/password in the sensor server code?");
} // Close catch sql exception
} // init()
// Creating destroy method
public void destroy() {
try {
conn.close();
} catch (SQLException se) {
System.out.println(se);
} // Close catch sql exception se
} // destroy()
/***
* Creating rfiddao method,
* Super is a method which is used inside a sub-class method, to call a method in the super class.
* Private method of the super-class would not be called.
* Only public and protected methods can be called by the super keyword.
* It is also used by class constructors to invoke constructors of its parent class.
*/
public RFIDDao() {
// Calling the super method
super();
/*try {
client = new MqttClient(BROKER_URL, userid+"-server_publisher");
// create mqtt session
MqttConnectOptions options = new MqttConnectOptions();
options.setCleanSession(false);
options.setWill(client.getTopic(userid + "/LWT"), "I'm gone :(".getBytes(), 0, false);
client.connect(options);
} catch (MqttException e) {
System.out.println("mqtt");
e.printStackTrace();
System.exit(1);
}*/
} // Close public RFIDDao method
/**
* Creating do get method, to insert data into the database.
* Then retrieve the data, depending on sensorname
* Creating suser valid method which would then check if valid user is using valid tag to open the door.
* getAllDoorLockStatus
* Retrieve all RFID Data.
* @return
* @throws SQLException
*/
// doGet is a method which supports the servlet HTTP GET requests
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setStatus(HttpServletResponse.SC_OK);
// Declare a SensorData object to hold the incoming data
SensorData oneSensor = new SensorData("unknown", "unknown");
// Check to see whether the client is requesting data or sending it
String getdata = request.getParameter("getdata");
ServletRequest req = null;
// SensorData sensordata = null;
// if no getdata parameter, client is sending data
if (getdata == null) {
// getdata is null, therefore it is receiving data
// Extracting the parameter data holding the sensordata
String sensorJsonString = request.getParameter("sensordata");
// Extracting the parameter data holding the sensorname
String data = request.getParameter("sensorname");
// Creating boolean for is user valid
boolean valid = isUserValid(data);
// If valid userhas been used return valid user/tag and door open
if (valid == true) {
System.out.println("valid user/tag");
// Creating new sensor data object
oneSensor = new SensorData(data, "Door open", "16038287");
// send command to open the door
PrintWriter writer = new PrintWriter(response.getOutputStream());
writer.write("true"); // Writer = true
writer.close();
}
// Otherwise, return invalid user/tag and door closed
else {
System.out.println("invalid user/tag");
oneSensor = new SensorData(data, "Door closed", "16038287");
} // Close else
// Problem if sensordata parameter not sent, or is invalid json
if (sensorJsonString != null) {
// Converting the json string to an object of type SensorData
// Also, calling the sensordata class
oneSensor = gson.fromJson(sensorJsonString, SensorData.class);
// Update sensor values and send back response
PrintWriter out = response.getWriter();
// Calling the updateSensorTable method
try {
updateSensorTable(oneSensor);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
out.close(); // close out
} // endif sensorJsonString not null
} // end if getdata is null
else { // Otherwise retrieve and return data in json format
// retrive data sensor by sensorname parameter
String sensorname = request.getParameter("sensorname");
if (sensorname != null) {
PrintWriter out = response.getWriter();
out.println(retrieveSensorData(sensorname));
out.close();
}
} // CLose else
} // CLose protected void doget method
// Creating do post method
//doPost is used for HTTP POST requests
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// Post is same as Get, so pass on parameters and do same
doGet(request, response);
} // CLose protected void dopost
/**
* Creating boolean to check if a valid user is trying to open the door, using specific sensor name (tagid) = 1600ee15e9
* Selecting everything from the door lock valid table where the id is '1600ee15e9'
* This will then check in the second table if its valid user or not and then reutrn success message.
* A tag data object is an instance of a class.
* @param sensorname
* @return
*/
public boolean isUserValid(String sensorname) {
// Creating tag data object
TagData oneSensor = new TagData("1600ee15e9", "VALID TAG", "Room101");
try {
// select everything from the door lock valid table where the sensorname is valid tag 9sensorname)
// String sql = "SELECT sensorname FROM doorlockValid WHERE sensorname = '"+sensorname+"'";
String sql = "SELECT * FROM doorlockValid WHERE sensorname=+sensorname";
// execute sql query
//stmt.executeUpdate(sql);
stmt.executeQuery(sql);
System.out.println("DEBUG: statement correct" +stmt);
System.out.println(sql); // print the query result
System.out.println(">> Debug: All RFID data displayed");
ResultSet resultset = null;
// If sensor name is 1600ee15e9 then it's valid user and returns true
if (resultset.getString("sensorname").equals(sensorname))
return true;
} catch (SQLException e) {
System.out.println(e.getMessage());
} catch (Exception e) {
System.out.println(e.getMessage());
} // CLose catch
// Otherwise return false if invalid user is trying to open the door
return false;
} // CLose buplic booelan is user valid method
// Creating static update sensor table to insert the status of the door lock to the database
public static void updateSensorTable(SensorData oneSensor) throws Exception {
try {
// Creating the INSERT statement from the parameters
// set time inserted to be the current time on database server
String updateSQL = "insert into doorlock(userid, sensorname, sensorvalue, timeinserted) "
+ "values('" + oneSensor.getUserid() + "','" + oneSensor.getSensorname() + "','"
+ oneSensor.getSensorvalue() + "',"
+ "now());";
// Print out debug message to the console
System.out.println("DEBUG: Update: " + updateSQL);
// Execute query
stmt.executeUpdate(updateSQL);
//publish_to_Android(oneSensor);
// Print debug message to the console
System.out.println("DEBUG: Update successful ");
} catch (SQLException se) {
// Problem with update, return failure message
System.out.println(se);
System.out.println("\nDEBUG: Update error - see error trace above for help. ");
return;
} // CLose catch sql exception
// all ok, return
return;
} // Close private void update sensor table method
/**
* Creating private string to retrieveSensorData
* Declaring a string to hold the sql select, set its value to a select
* statement that will retrieve all values from the doorlock table
* where the sensorname equals the parameter supplied "where sensorname='" + sensorname + "'".
* Then adding the qualifier " order by timeinserted asc" to the sql
* statement to ensure that the data is returned in ascending order
* (i.e. newest last).
* http://localhost:8080/IOTServer/RFIDDao?getdata&sensorname=4d004a5587
* http://localhost:8080/IOTServer/RFIDDao?getdata&sensorname=1600ee15e9
* @param sensorname
* @return
*/
private String retrieveSensorData(String sensorname) {
//SELECT from mudfoot server where sensorname = the sensorname inserted
// String selectSQL = "select * from rashidd.doorlock where sensorname = \"" + sensorname + "\"";
String selectSQL = "select * from rashidd.doorlock where sensorname='" +
sensorname + "' order by timeinserted asc";
//String selectSQL = "select * from rashidd.doorlock";
ResultSet rs = null; // creating variable for result set
// Creating array list of sensor data
ArrayList<SensorData> s = new ArrayList<>();
// Declaring a Result set called rs
// rs = ((java.sql.Statement) statement).executeQuery(selectSQL);//RESULT FROM
// QUERY
// Creating try to declare ArrayList of SensorData called allSensors to hold results,
// and initialise it
try {
/**
* iterate over the result set created by the select for each of
* the columns in the table, putting them into oneSensor with
* the corresponding set method for sensorname, sensorvalue, userid, timeinserted, using either
* getString or getDouble and then execute query.
*/
rs = conn.createStatement().executeQuery(selectSQL);
System.out.println("DEBUG: statement correct" +selectSQL);
// Creating while resultset
while (rs.next()) {
// String sensorname, String sensorvalue, String userid, String sensordate
SensorData oneSensor = new SensorData(rs.getString("sensorname"), rs.getString("sensorvalue"),
rs.getString("userid"), rs.getString("timeinserted"));
s.add(oneSensor); // add the data
} // CLose while loop for rs.next
} catch (SQLException ex) {
System.out.println("Error in SQL " + ex.getMessage());
} // Close catch sql exception e
// Return the responce in json format
return new Gson().toJson(s);
} // Close private string retrieve sensor data
// Creating public void publish to android to publish the sensor data to the application, in order to view the data on the appication
// public void publish_to_Android(SensorData a)throws Exception
// {
//
// Gson g = new Gson();
// final MqttTopic motorTopic = client.getTopic(TOPIC_SERVER);
// motorTopic.publish(new MqttMessage(g.toJson(a).getBytes()));
//
//
// }
} // Close public class sensor server db
我尝试了什么：
我试过调试代码仍然没有，并检查了我的sql中的不同语句测试了他们。它们在我的sql工作台中工作但不在eclipse中工作。
What I have tried:
I have tried debugging the code still nothing and also checked different statements in my sql and tested them. They work in my sql workbench but not in eclipse.
推荐答案
String selectSQL = "select * from rashidd.doorlock where sensorname='" +
sensorname + "' order by timeinserted asc";
永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。
名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。
SQL注入 - 维基百科 [ ^ ]
SQL注入 [ ^ ]
按示例进行SQL注入攻击 [ ^ ]
PHP：SQL注入 - 手册 [ ^ ]
SQL注入预防备忘单 - OWASP [ ^ ]
我该怎么办？解释没有技术术语的SQL注入？ - 信息安全堆栈交换 [ ^ ]
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]
您似乎试图测试null初始化变量的结果。
You seem to have tried to test the results of a null initialised variable.
//stmt.executeUpdate(sql);
stmt.executeQuery(sql);
System.out.println("DEBUG: statement correct" +stmt);
System.out.println(sql); // print the query result
System.out.println(">> Debug: All RFID data displayed");
ResultSet resultset = null; // <-----
// If sensor name is 1600ee15e9 then it's valid user and returns true
if (resultset.getString("sensorname").equals(sensorname)) // <----
return true;
这篇关于isuservalid方法中的SQL statmenr和if（valid == true）语句有什么问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
