java - JSP/SQL 语法 - 在语句中使用变量

Question

我想知道是否可以使用选择下拉列表中的字符串在 sql 语句中使用。例如，用户从 dropbox 中选择“student”，该 dropbox 应该用于选择要在数据库中搜索的表。

索引.jsp

<% @page language = "java"
contentType = "text/html; charset=ISO-8859-1"
pageEncoding = "ISO-8859-1" %>
  < !DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd" >
< html >
  < head >
  < meta http - equiv = "Content-Type"
content = "text/html; charset=ISO-8859-1" >
  < title > Home < /title>
</head >
  < body >

  < form method = "post"
action = "validate.jsp" >
  < center > < h2 style = "color:green" > eBooking Login < /h2></center >
  < table border = "0"
align = "center" >
  < tr >
  < td > Enter Your Name: < /td>
<td><input type="text" name="username" required></td >
  < /tr>
<tr>
<td>Enter Your Password :</td >
  < td > < input type = "password"
name = "password"
required / > < /td>
</tr >
  < tr >
  < td > Select User Type: < /td>
<td><select name="usertype">
<option value="select">Student</option >
  < option value = "select" > PAT < /option>
<option value="select">Administrator</option >
  < /select>
</td >
  < /tr>
<tr>
<td></td >
  < td > < input type = "submit"
value = "Login" / > < /td>
</table >
  < /form>
</body >
  < /html>

验证.jsp

<% @page language = "java"
contentType = "text/html; charset=ISO-8859-1"
pageEncoding = "ISO-8859-1" %>
  < !DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd" >
<% @page import = "java.sql.*" %>
  <%
  try {
    String username = request.getParameter("username");
    String password = request.getParameter("password");
    String usertype = request.getParameter("usertype");
    String driver = ("com.mysql.jdbc.Driver");
    Class.forName(driver); // MySQL database connection
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/ebooking" + "?user=user&password=user");
    PreparedStatement pst = conn.prepareStatement("Select * from ? where stu_username=? and stu_password=?");
    pst.setString(1, usertype);
    pst.setString(2, username);
    pst.setString(3, password);
    ResultSet rs = pst.executeQuery();
    session.setAttribute("username", username);
    if (rs.next())

      response.sendRedirect("studentwelcome.jsp");
    else
      out.println("Invalid login credentials");
  } catch (Exception e) {
  System.out.println(e);
  out.println("Something went wrong !! Please try again");
} %>

学生欢迎.jsp

<% @page language = "java"
contentType = "text/html; charset=ISO-8859-1"
pageEncoding = "ISO-8859-1" %>


  < !DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd" >
< html >
  < head >
  < meta http - equiv = "Content-Type"
content = "text/html; charset=ISO-8859-1" >
  < title > Student Welcome < /title>
</head >
  < body >
  Welcome <%= session.getAttribute("username") %>
  < /body>
</html >

Answer 1

您使用的准备好的语句无效(表名作为参数)。这 ？仅适用于列值:

select col1, col2, ... colN from table_name where col1 = ? ... ...

避免将用户输入的表名作为字符串插入。一种(简化的)解决方案是映射可能的值并确保用户输入对应于其中之一。例如:

0 -> TABLE_1
1 -> TABLE_2
2 -> TABLE_3
...

在你的具体情况下

...
String usertypeCode = request.getParameter("usertype_code");
String driver = ("com.mysql.jdbc.Driver");
Class.forName(driver); // MySQL database connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/ebooking" + "?user=user&password=user");

if (usertypeCode.equals("0") {
    usertype = "TABLE_1";
} else if (usertypeCode.equals("1") {
    usertype = "TABLE_2";
} else if (usertypeCode.equals("2") {
    usertype = "TABLE_3";
} else {
    // handle invalid input
}

PreparedStatement pst = conn.prepareStatement("Select * from " + usertype + " where stu_username=? and stu_password=?");
pst.setString(1, username);
pst.setString(2, password);
ResultSet rs = pst.executeQuery();
...

综上所述，您真的不应该在您的 jsp 中放置查询(应该在 DAO 层中，而不是在 View 中)。