CODEWITHSUNDEEP
sqlstringhibernatepostgresqldouble-quotes
Anuj Balan
Anuj Balan

Reputation: 7729

Unable to frame sql query correctly from java

I am trying to query data from my app, through postgresql DB. What I want to achieve is from "Users" where "Username"='stackoverflow'

If this query is written in the SQl editor and run, it gives the expected result.

Thus, am trying to send the query in the same format, as

String SQL_QUERY = "from " + "\"Users\"" + " where " + "Username" + "=" + "'" + request.getParameter("username") + "'";

When I run this, I get the following error: unexpected char: '"' [from "Users" where Username=user1]

If I remove the double quotes, it wont work, saying table named users doesn't exist. What is the way out of this deadlock? Where am I going wrong ??

Thank you.

Upvotes: 0

Views: 569

Answers (3)

Anuj Balan
Anuj Balan

Reputation: 7729

Following the Postgresql syntax has in the end helped in getting a solution. Changed all the column names to small capitals and then tried, which solved most of the problems. Thanks to A.H

        String SQL_QUERY = "FROM Users WHERE username ='" + username + "'";
        Query query = objSession.createQuery(SQL_QUERY);

        List list = query.list();
        for (Iterator it = list.iterator(); it.hasNext();) {
            Users objUsers = (Users) it.next();
            System.out.println("Username: " + objUsers.userName);
            System.out.println("Password: " + objUsers.password);
            System.out.println("Name: " + objUsers.name);
        }

Upvotes: 0

A.H.
A.H.

Reputation: 66263

This should be what you want:

String username = request.getParameter("username");
String SQL_QUERY = "SELECT * FROM \"Users\" WHERE \"Username\" ='" + username + "'";

You can check this by 

System.out.println(SQL_QUERY);

which prints

SELECT * FROM "Users" WHERE "Username" ='foo'

ATTENTION/DANGER: This statement will solve your problem with the PostgreSQL mixed-case tablename and columnnames. BUT providing the value for Username to the query this way makes you vulnerable to even the easiest SQL-Injection attack. Please use PreparedStatement instead and write

String SQL_QUERY = "SELECT * FROM \"Users\" WHERE \"Username\" = ?";

Upvotes: 1

Diego
Diego

Reputation: 18359

Where's the SELECT clause of the query? It needs to be something like this:

String SQL_QUERY= "SELECT * FROM Users WHERE Username='" + request.getParameter("username") + "'";

Or like this:

String SQL_QUERY= "SELECT FirstName, LastName, Email FROM Users WHERE Username='" + request.getParameter("username") + "'";

Here's a very similar example that uses PreparedStatement to avoid a SQL injection security attack: https://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java

Upvotes: 0

Related Questions