Determining if my code contains hard-coded SQL queries?

Question

Here is a method authenticate the user password. It verify the user email and password from the database.

public long authenticate(String email, String encodePassword) {
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }

    try (
        Connection conn = DriverManager.getConnection("jdbc:connection", "adminusername","password");/* a) Database User Profile: root is who the user is b) Database user password */
        Statement stmt = conn.createStatement();
    ) /* execute mysql queries */ {
        String query = "Select id from User where email = '" + email + "' and password = '" + encodePassword + "'";
        System.out.println("query: " + query);
        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            // if the user id is there get it
            return rs.getLong("id");                
        }           
    } catch (SQLException e) {
        e.printStackTrace();
    }   

    // if the user id not there return -1 (authority failed)
    return -1; 
}

To determine whether my lecture is right that I have hard-coded SQL queries values in my code

It is not really clear what your question is. Did your lecturer complain about something in your code? Is this code from your lecture and you have a question about it? I hope it's not the latter, as there is a big problem in your code (and it *might* be what you/the lecturer mean by "hard coded", which is what I'd guess your question is about): you should always use prepared statements, see [How does Java's PreparedStatement work?](https://stackoverflow.com/q/419021), and not concat the variables directly (see the 2nd answer in the linked question for one of the reasons why). — Solarflare, Sep 11 '19 at 12:35
beside what @Solarflare is mentioning about security.. You should not plain text store password in the database ... Or use password column filter in the `WHERE` clause as [timing attacks](https://en.wikipedia.org/wiki/Timing_attack) becomes possible as RDMS are designed to give stabile results in close "constant' times especially when b tree indexes or caching in memory is involved.. — Raymond Nijland, Sep 11 '19 at 13:19

score 0 · Accepted Answer · answered Sep 11 '19 at 12:44

Your lecturer is trying to warn you about SQL injection.

SQL injection is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. It generally allows an attacker to view data that they are not normally able to retrieve. This might include data belonging to other users, or any other data that the application itself is able to access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application's content or behavior.

The corresponding part in your code is the following

String query = "Select id from User where email = '" + email + "' and password = '" + encodePassword + "'";

If the query returns the id of a user, then the login is successful. Otherwise, it is rejected.

Here, an attacker can log in as any user without a password simply by using the SQL comment sequence -- to remove the password check from the WHERE clause of the query. For example, submitting the email some@email'-- and a blank password results in the following query:

SELECT id FROM users WHERE email = 'some@email'--' AND password = ''

This query returns the user whose email is some@email and successfully logs the attacker in as that user without checking the password.

But my database is deployed to the cloud – P. Coker Sep 11 '19 at 13:59 — P. Coker, Sep 11 '19 at 13:59

Determining if my code contains hard-coded SQL queries?

1 Answers1