java - SQL语法异常 : could not execute query

Question

我正在使用 Struts2 和 Hibernate，当我使用字符串 test 搜索数据时出现以下错误，但当我使用数字 111 搜索时对我有用。我从 bean 类中获取此值并在 bean 类中定义字符串类型的属性。

下面我提供代码:

public String retrieveRecords() 
{    
    String empId = p.getEmpId();
    String paramValue = "";
    if(empId !=null)
        if(!(empId.isEmpty()))
        paramValue =" where b.empId="+empId;

    String empName = p.getEmployeeName();
    if(empName !=null && empName != "")
    {
        if(!(empName.isEmpty())){
        if(paramValue == "")
         paramValue =" where b.employeeName="+empName;
        else
         paramValue =paramValue + " and b.employeeName="+empName;
        }
    }
    System.out.println("=========paramvalues===="+paramValue);
    recList = (List<RequestBean>) session.createQuery("from RequestBean b"+paramValue).list();
    request.setAttribute("rec", recList);
    System.out.println("got size"+recList);
    return SUCCESS;
}

bean 类:

public class RequestBean {

    private Long id;
    private String empId;
    private String employeeName;
    private String employeeType;
    private String personnalNumber;
    private String contactNumber;
    private String companyName;
    private String address;
    private String remarks;
    private String empStatus = "E";
    private Date joiningDate = null;
    private Date created;

    /************* Getters ************************/

    public Long getId() {
        return id;
    }

    public String getEmpId() {
        return empId;
    }

    public String getEmployeeName() {
        return employeeName;
    }

    public String getEmployeeType() {
        return employeeType;
    }

    public String getPersonnalNumber() {
        return personnalNumber;
    }

    public String getContactNumber() {
        return contactNumber;
    }

    public String getCompanyName() {
        return companyName;
    }

    public String getAddress() {
        return address;
    }

    public String getRemarks() {
        return remarks;
    }

    public Date getJoiningDate() {
        return joiningDate;
    }

    public String getEmpStatus() {
        return empStatus;
    }

    public Date getCreated() {
        return created;
    }

   /******************* Setters ***************************/

    public void setId(Long id) {
        this.id = id;
    }
    public void setEmpId(String empId) {
        this.empId = empId;
    }
    public void setEmployeeName(String employeeName) {
        this.employeeName = employeeName;
    }
    public void setEmployeeType(String employeeType) {
        this.employeeType = employeeType;
    }
    public void setPersonnalNumber(String personnalNumber) {
        this.personnalNumber = personnalNumber;
    }
    public void setContactNumber(String contactNumber) {
        this.contactNumber = contactNumber;
    }
    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public void setRemarks(String remarks) {
        this.remarks = remarks;
    }
    public void setJoiningDate(Date joiningDate) {
        this.joiningDate = joiningDate;
    }
    public void setEmpStatus(String empStatus) {
        this.empStatus = empStatus;
    }
    public void setCreated(Date created) {
        this.created = created;
    }

}

映射:

<hibernate-mapping>
<class name="com.ims.bean.RequestBean" table="EMPDETAILS">

<id name="id" column="id">
   <generator class="increment"/>
</id>

<!-- <property name="id"     column="id" /> -->
<property name="empId"  column="empId"/>
<property name="employeeName"  column="empName"/>
<property name="employeeType"  column="empType"/>
<property name="personnalNumber"  column="personnalNum"/>
<property name="contactNumber"  column="contactNo"/>
<property name="companyName"  column="empCompanyName"/>
<property name="address"  column="address"/>
<property name="remarks"  column="remarks"/>
<property name="joiningDate"  column="joiningDate"/>
<property name="empStatus"  column="empStatus"/>
<property name="created"  column="created"/>

</class>
</hibernate-mapping>

错误:

org.hibernate.exception.SQLGrammarException: could not execute query
    org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
    org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    org.hibernate.loader.Loader.doList(Loader.java:2231)
    org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
    org.hibernate.loader.Loader.list(Loader.java:2120)
    org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
    org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
    org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
    org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
    org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
    com.ims.DAO.RequestControllerDAO.retrieveRecords(RequestControllerDAO.java:60)
    sun.reflect.GeneratedMethodAccessor76.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    java.lang.reflect.Method.invoke(Unknown Source)
    ognl.OgnlRuntime.invokeMethod(OgnlRuntime.java:891)
    ognl.OgnlRuntime.callAppropriateMethod(OgnlRuntime.java:1293)
    ognl.ObjectMethodAccessor.callMethod(ObjectMethodAccessor.java:68)
    com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethodWithDebugInfo(XWorkMethodAccessor.java:117)
    com.opensymphony.xwork2.ognl.accessor.XWorkMethodAccessor.callMethod(XWorkMethodAccessor.java:108)
    ognl.OgnlRuntime.callMethod(OgnlRuntime.java:1369)
    ognl.ASTMethod.getValueBody(ASTMethod.java:90)
    ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
    ognl.SimpleNode.getValue(SimpleNode.java:258)
    ognl.Ognl.getValue(Ognl.java:494)
    ognl.Ognl.getValue(Ognl.java:458)

Answer 1

SQLGrammarException 被抛出是因为 Hibernate 生成的 SQL 查询有错误的 SQL 语法。你构建查询的方式是错误的，你不应该将值(尤其是字符串值)连接到结果查询，因为这样的代码很容易受到可能的 SQL injection 攻击。攻击。相反，您可以在查询字符串中使用参数

String empId = p.getEmpId();
String paramValue = "";
if (empId !=null && !empId.isEmpty())
    paramValue = " where b.empId=:empId";
String empName = p.getEmployeeName();
if (empName !=null && !empName.isEmpty()) {
    if (paramValue == "")
     paramValue =" where b.employeeName=:empName";
    else
     paramValue =paramValue + " and b.employeeName=:empName"; 
}       
System.out.println("=========paramvalues===="+paramValue);
Query query = session.createQuery("from RequestBean b"+paramValue);
//now set parameter values
if(empId !=null && !empId.isEmpty())
  query.setParameter("empId", empId);
if(empName !=null && !empName.isEmpty())
  query.setParameter("empName", empName);
recList = (List<RequestBean>) query.list();