英文:
I am confused on how to use PreparedStatements using Java and Derby sql server
问题
以下是您提供的内容的翻译：
"Just a note, this is for class. I would go to the class material, but it doesn't address this (the school is kinda garbage). And when I ask the teacher, he says to google it.
I've tried googling it, but my understanding is not good enough yet sadly.
My setup is as follows. It's a web application that uses DerbyDB, Glassfish 5, Java, and JavaScript servlets.
I am a bit lost on using Prepared Statements.
My Authentication Java code has an SQL injection vulnerability, and I am trying to solve it. Everyone says to use Prepared Statements, so I am trying.
My code is below. This is how it works though. It checks the usernames (an email) that was input to the user_id from the sdev_users table. Then it takes the user_id and checks it in the user_info table to the password stored under the user_id to see if it matches.
The prepared statements are at the bottom, but I figured y'all would like to see the full thing, just in case!
Authenticate.java"
请注意，上述内容已经翻译完成。
英文:
Just a note, this is for class. I would go to the class material, but it doesn't address this(the school is kinda garbage). And when i ask the teacher, he says to google it.
I've tried googling it, but my understanding is not good enough yet sadly.
My setup is as follows. Its a web application that uses DerbyDB, Glassfish 5, Java and javascript servlets.
I am a bit lost on using Prepared Statements.
My Authentication java code has an sql inject vulnerability and i am trying to solve it. Everyone says to use PreparedStatements, so i am trying.
My code is below. This is how it works though. It checks the usernames(an email)that was input to the user_id from thesdev_users table. then it takes the user_id and checks it in user_info table to the password stored under the user_id to see if it matches.
The prepared statements are at the bottom, but i figured yall would like to see the full thing, just in case!
Authenticate.java
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package SDEV425_HW4;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.derby.jdbc.ClientDataSource;
/**
*
* @author jim
*/
public class Authenticate extends HttpServlet {
// variables
private String username;
private String pword;
private Boolean isValid;
private int user_id;
private HttpSession session;
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code>
* methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
try (PrintWriter out = response.getWriter()) {
/* TODO output your page here. You may use following sample code. */
out.println("<!DOCTYPE html>");
out.println("<html>");
out.println("<head>");
out.println("<title>Servlet Authenticate</title>");
out.println("</head>");
out.println("<body>");
out.println("<h1>Servlet Authenticate at " + request.getContextPath() + "</h1>");
out.println("<h1>Results are " + username + "," + isValid +"," +user_id +"," +this.username + "</h1>");
out.println("</body>");
out.println("</html>");
}
}
// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
/**
* Handles the HTTP <code>GET</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Handles the HTTP <code>POST</code> method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// Get the post input
this.username = request.getParameter("emailAddress");
this.pword = request.getParameter("pfield");
this.isValid = validate(this.username, this.pword);
response.setContentType("text/html;charset=UTF-8");
// Set the session variable
if (isValid) {
// Create a session object if it is already not created.
session = request.getSession(true);
session.setAttribute("UMUCUserEmail", username);
session.setAttribute("UMUCUserID", user_id);
// Send to the Welcome JSP page
RequestDispatcher dispatcher = request.getRequestDispatcher("welcome.jsp");
dispatcher.forward(request, response);
} else {
// Not a valid login
// refer them back to the Login screen
request.setAttribute("ErrorMessage", "Invalid Username or Password. Try again or contact Jim.");
RequestDispatcher dispatcher = request.getRequestDispatcher("login.jsp");
dispatcher.forward(request, response);
}
}
/**
* Returns a short description of the servlet.
*
* @return a String containing servlet description
*/
@Override
public String getServletInfo() {
return "Short description";
}// </editor-fold>
// Method to Authenticate
public boolean validate(String name, String pass) {
boolean status = false;
int hitcnt=0;
try {
ClientDataSource ds = new ClientDataSource();
ds.setDatabaseName("SDEV425");
ds.setServerName("localhost");
ds.setPortNumber(1527);
ds.setUser("sdev425");
ds.setPassword("sdev425");
ds.setDataSourceName("jdbc:derby");
Connection conn = ds.getConnection();
String sql = "select user_id from sdev_users where email = '" + this.username + "'";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(user_id, 0);
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
user_id = rs.getInt(1);
}
if (user_id > 0) {
String sql2 = "select user_id from user_info where user_id = " + user_id + "and password = '" + this.pword + "'";
PreparedStatement stmt2 = conn.prepareStatement(sql2);
stmt2.setString(user_id, pword);
ResultSet rs2 = stmt2.executeQuery(sql2);
while (rs2.next()) {
hitcnt++;
}
// Set to true if userid/password match
if(hitcnt>0){
status=true;
}
}
} catch (Exception e) {
System.out.println(e);
}
return status;
}
}
答案1
得分: 2
任何用户输入都应视为“污染”。这可能是企图入侵您的尝试。
这意味着每当您将用户输入直接作为代码运行时，您实际上已经把您的服务器交给了任何想要的人。
您在此代码中已经“搞砸”了两次。
第一次是在您的HTML响应中：
out.println("<h1>Results are " + username + "," + isValid + "," +user_id + "," +this.username + "</h1>");
out.println("</body>");
好的。我将创建一个新的用户帐户，并将我的用户名设置为：
rzwitserloot <script>/* 哈哈，在这里做一些恶意操作 */
然后我就得手了。
您需要_转义_这些内容。获取一个HTML转义器，将所有不安全的输入都通过这个转义器处理一遍。
接下来是SQL语句。
String sql = "select user_id from sdev_users where email = '" + this.username + "'";
很好。我将使我的用户名为whatever';-- DROP TABLE sdev_users;，然后毁掉你的一天。
这是您如何使用预处理语句来转义字符串：
String sql = "select user_id from sdev_users where email = ?";
// 注意：传递给prepareStatement的字符串必须始终是常量。
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, email); // 这会安全地替换第一个（1）个问号（?）。
英文:
Any user input is to be considered 'tainted'. It may be an attempt to hack your stuff.
That means anytime you just run user input as code, you've basically handed your server over to whomever wants.
You've messed this up TWICE in this code.
First time is in your HTML response:
out.println("<h1>Results are " + username + "," + isValid +"," +user_id +"," +this.username + "</h1>");
out.println("</body>");
okay. I shall make a new user account, and make my username:
rzwitserloot <script>/* haha do evil things here */
and I got you.
You need to escape these things. Get an HTML escaper and throw all unsafe input through this.
Next one is the SQL statement.
String sql = "select user_id from sdev_users where email = '" + this.username + "'";
Cool. I'm gonna make my username whatever';-- DROP TABLE sdev_users; and ruin your day.
This is how you use preparedstatement to escape strings:
String sql = "select user_id from sdev_users where email = ?";
// Note: The string you feed to prepareStatement must ALWAYS be a constant.
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, email); // this replaces the first (1) ?. Safely.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论