Spring Unknown column in 'where clause'

Question

I am trying to pass the username from a spring security login page to a mysql select which is used by a page called Timetables to display a table from the database. The page is supposed to only display the data pertaining to a certain user.

However no matter what I try I get a 500 error saying

nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'admin1' in 'where clause'

admin1 is the username used to log in.

I've tried testing the variable with System.out.println(logininame); and it is indeed a string.

I've tried several tricks but the only thing which works is if I manually add ' 'admin1' ' at the end of the string. Since I have more than one user that is not a solution.

Any ideas where I might be getting it wrong. The error occurs at the end of the sql string in the service class.

Controller which reads the username

@Controller
public class TimetableController {

@Autowired
TimetableService service;

@Autowired
AssignmentsService serv;

@RequestMapping(value = {"/Timetable"}, method = RequestMethod.GET)
public String index(Model md){
    org.springframework.security.core.Authentication auth = SecurityContextHolder.getContext().getAuthentication();
    System.out.println(auth.getName());
    String loginname = auth.getName();
    md.addAttribute("timetables", service.findAll(loginname));
    return "Timetable";
 }

//request for adding new entry

}

The service class with the query

@Service
public class TimetableService {

@Autowired
JdbcTemplate template;

public List<Timetable> findAll(String loginname) {
   // System.out.println(loginname);
   // String test = "admin1";
    String sql = "  SELECT timetables.timetableId, timetables.assignmentId, timetables.date, " +
            "timetables.hoursWorked, users.username, projects.projectName FROM timetables" +
            " INNER join assignments on assignments.assignmentId = timetables.assignmentId" +
            " INNER JOIN users on users.userId = assignments.userId" +
            " INNER JOIN projects on assignments.projectId = projects.projectId where username=" + loginname;

    RowMapper<Timetable> rm = new RowMapper<Timetable>() {
        @Override
        public Timetable mapRow(ResultSet resultSet, int i) throws SQLException {
            Timetable timetable = new Timetable(resultSet.getInt("timetableId"),
                    resultSet.getInt("assignmentId"),
                    resultSet.getDate("date"),
                    resultSet.getInt("hoursWorked"));

            return timetable;
        }
    };

    return template.query(sql, rm);
}

The table from Timetable.html

<table class="table table-bordered">
  <thead>
  <tr>
    <th>id</th>
    <th>project</th>
    <th>date</th>
    <th>number of hours</th>
  </tr>
  </thead>
  <tbody>
  <tr th:each = "obj: ${timetables}">
    <td th:text="${obj.timetableId}">45</td>
    <td th:value="${obj.assignmentId}">vasi</td>
    <td th:text="${obj.date}"></td>1 ian</td>
    <td th:text="${obj.hoursWorked}"></td>
  </tr>
  </tbody>
</table>

Answer 1

you need to pass the username as string to query. Like " INNER JOIN projects on assignments.projectId = projects.projectId where username='" + loginname + "'"