Escaping reserved words in MySQL: Unknown column 'PASSWORD' in 'field list'

Question

My problem is with regards to JDBC SQL. What i want to do is use my username in the employee entity to retrieve the password for my login. I have a datainitalization singleton bean to prepopulate my system admin.

Error Message in GlassFish.

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.1.v20150605-
31e8258): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 
'PASSWORD' in 'field list'
Error Code: 1054
Call: SELECT EMPLOYEEID, ACCESSRIGHT, ADDRESSLINE1, ADDRESSLINE2, 
CONTACTNUMBER, FIRSTNAME, LASTNAME, PASSWORD, POSTALCODE, USERNAME FROM 
EMPLOYEE WHERE USERNAME LIKE ?
bind => [1 parameter bound]
Query: ReadAllQuery(referenceClass=Employee sql="SELECT EMPLOYEEID, 
ACCESSRIGHT, ADDRESSLINE1, ADDRESSLINE2, CONTACTNUMBER, FIRSTNAME, LASTNAME, 
PASSWORD, POSTALCODE, USERNAME FROM EMPLOYEE WHERE USERNAME LIKE ?")

My method to retrieve password for login using username

    @Override
public Employee retrievePasswordByUsername(String username) throws UserNameNotFoundException
{
    //SELECT sl FROM SystemLogin sl WHERE sl.userName  = :inUserName
   return (Employee) em.createQuery("SELECT e FROM Employee e WHERE e.username LIKE :inUsername").setParameter("inUsername", username).getResultList();

}

This is my attempt to do a login method.

@Override
public Employee employeeLogin(String username, String password) throws 
InvalidLoginCredentialException
{


    Employee employee = null;
    try {
        employee = retrievePasswordByUsername(username);
    } catch (UserNameNotFoundException ex) {
        Logger.getLogger(EmployeeController.class.getName()).log(Level.SEVERE, null, ex);
    }

     if(employee.getPassword().equals(password))
        {
            return employee;
        }
        else
        {
            throw new InvalidLoginCredentialException("Username does not exist or invalid password!");
        }
    }

Lastly, this is the singleton bean that i mention for my system admin

     private void initializeData()
    {
        try {
            employeeControllerLocal.createNewEmployee(new Employee("Default", 
    "Administrator", "99999999", "NUS", "Computing","599999", 
    EmployeeAccessRight.ADMINISTRATOR, "admin", "password"));
        } catch (EmployeeExistException ex) {


 Logger.getLogger(DataInitializationSessionBean.class.getName()).log(Level.SEVERE
        , null, ex);
        } catch (GeneralException ex) {

  Logger.getLogger(DataInitializationSessionBean.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

This is part of my employee entity class.

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long employeeId;
@Column(length = 32, nullable = false)
private String firstName;
@Column(length = 32, nullable = false)
private String lastName;
@Column(length = 32, nullable = false)
private String contactNumber;
@Column(length = 32, nullable = false)
private String addressLine1;
@Column(length = 32, nullable = false)
private String addressLine2;
@Column(length = 32, nullable = false)
private String postalCode;
@Column(nullable = false)
@Enumerated(EnumType.STRING)
private EmployeeAccessRight accessRight;
@Column(unique = true)
private String username;
@Column(length = 32, nullable = false)
private String password;

Thanks everyone for reading this! Hope i'll find a solution soon. :)

PS: This question has been solved. I've also posted one link for anyone who needs to check if they need to escape any reserved words: https://dev.mysql.com/doc/refman/5.7/en/keywords.html

[`PASSWORD`](https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_password) is a *function*, you need to escape the column name or rename it. Also, you should ***never*** *store* plain text passwords in a database. — Elliott Frisch, Oct 21 '17 at 02:19
Hi! Thanks for your reply. I asked my prof and he said that for now the password is fine in plain text. It's for an assignment :) May i ask how do i escape the column name? Because i'm really new to this. — , Oct 21 '17 at 02:22
The error message says "Unknown column 'PASSWORD' in 'field list'", soit might be called something else... — Usagi Miyamoto, Oct 21 '17 at 02:34
Hi! Sorry but i don't really get it. So does it mean that i have a typo or something one of my code? Or is it that i just have to rename password in my column to passwords? or something? Thanks! — , Oct 21 '17 at 02:36
Check your database schema and also your mappings for your `Employee` entity... — Usagi Miyamoto, Oct 21 '17 at 02:41
Why does an `Employee` have `USERNAME` and `PASSWORD` columns? — Usagi Miyamoto, Oct 21 '17 at 02:45
Because it’s part of an assignment requirement. The system admin is also an employee. The entity class has username and password. That’s why i’m confused why it doesn’t work. Thanks for quick reply! — , Oct 21 '17 at 03:01

score 0 · Accepted Answer · answered Oct 21 '17 at 16:30

0

As PASSWORD is a reserved work you need to explicitly specify the escaped column name on your mapping

@Column(length = 32, nullable = false)
private String password;

Should be changes either to

@Column(name = "`PASSWORD`", length = 32, nullable = false)
private String password;

or to

@Column(name = "\"PASSWORD\"", length = 32, nullable = false)
private String password;

based on what variant is supported by your MySQL configuration (some details here)

answered Oct 21 '17 at 16:30

Aleh Maksimovich

2,622
8
19

Thanks! It's great. Now i can move on to solve my next problem. :) – Oct 22 '17 at 08:30
When the solution solves your issue please mark it appropriately so the question is mark answered. This help other people to search for answers and questions that still need answer. – Aleh Maksimovich Oct 22 '17 at 08:36
Done! Thanks! I'm new here so it took awhile. – Oct 22 '17 at 08:46

Escaping reserved words in MySQL: Unknown column 'PASSWORD' in 'field list'

1 Answers1