Storing SQL queries in Java

Question

I am having issues with how we are currently storing our SQL queries, we currently have a java class that stores the SQL as a public static final, some of these SQL queries require a global string object to be injected into the where clause such as a system name or a user name, however because we store the queries as a static final, when another test runs and the system name or user name has the value reassigned the SQL query still holds the value from the previous test, I need to find a more efficient and stable way to store the SQL queries. can anyone help?

Example of how we store our SQL queries:

public static final String GET_USER_ACCESS_VALUES =
"SELECT \n" +
                        "[Company].[dbo].[TUsers].[PINExpiryDate]\n" +
                        ",[Company].[dbo].[TUsers].[PINForceChange]\n" +
                        ",[Company].[dbo].[TUsers].[Active]\n" +
                        ",[Company].[dbo].[TUsers].[ContractExpiry]\n" +
                        ",[Company].[dbo].[TUserGroups].[Name]\n" +
                        ",[Company].[dbo].[TUsers].[ActiveDate]\n" +
                        ",[Company].[dbo].[TUsers].[ExpiryDate]\n" +
                        ",[Company].[dbo].[TUsers].[AllowanceAcrossSystemsOverride]\n" +
                        "FROM [Company].[dbo].[TUsers] \n" +
                        "INNER JOIN [Company].[dbo].[TUserGroups] \n" +
                        "ON [Company].[dbo].[TUsers].[UserGroupID] = [Company].[dbo].[TUserGroups].[UserGroupID]\n" +
                        "WHERE [Company].[dbo].[TUsers].[DisplayName] = '" + userNameValue +"'";

Sorry missed this at the start of the SQL query: public static final String GET_USER_ACCESS_VALUES = — D. Barnett
– D. Barnett, Commented Aug 5, 2022 at 9:39

greg-449 · Accepted Answer · 2022-08-05 09:58:07Z

5

Don't concatenate strings like that. Use PreparedStatement with the setString method to set the user name.

PreparedStatement prep = connection.prepareStatement(GET_USER_ACCESS_VALUES);

prep.setString(1, userNameValue);

GET_USER_ACCESS_VALUES now just has a ? for the name:

public static final String GET_USER_ACCESS_VALUES = """
      SELECT
          [Company].[dbo].[TUsers].[PINExpiryDate]
          ,[Company].[dbo].[TUsers].[PINForceChange]
          ,[Company].[dbo].[TUsers].[Active]
          ,[Company].[dbo].[TUsers].[ContractExpiry]
          ,[Company].[dbo].[TUserGroups].[Name]
          ,[Company].[dbo].[TUsers].[ActiveDate]
          ,[Company].[dbo].[TUsers].[ExpiryDate]
          ,[Company].[dbo].[TUsers].[AllowanceAcrossSystemsOverride]
          FROM [Company].[dbo].[TUsers]
          INNER JOIN [Company].[dbo].[TUserGroups]
          ON [Company].[dbo].[TUsers].[UserGroupID] = [Company].[dbo].[TUserGroups].[UserGroupID]
          WHERE [Company].[dbo].[TUsers].[DisplayName] = ?
      """;

I have used a Java 15 text block here to simplify the string

answered Aug 5, 2022 at 9:58

greg-449

112k235 gold badges112 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D. Barnett Over a year ago

Excellent thank you so much, I am quite new to development and I have been left with an existing framework that I am trying to improve and make more dynamic, you have been a great help thank you!

Mahdi · Accepted Answer · 2022-08-05 09:49:12Z

-1

You should avoid "global" variables as much as you can in your code. What @greg-449 mentioned is the best way to go. Use prepared statement.

If you have to use Strings for your SQL statements I would use something like this:

public static final String cmd = "SELECT * FROM Users WHERE NAME={0}"

And you would use it like this:

var sqlCommand = MessageData.format(cmd, currentUserName)

See https://docs.oracle.com/javase/7/docs/api/java/text/MessageFormat.html

answered Aug 5, 2022 at 9:49

Mahdi

2,3553 gold badges28 silver badges53 bronze badges

2 Comments

D. Barnett Over a year ago

Thank you for the information, I am trying to improve our current framework and I am the only one on the team with about 6 months experience in development so I am having to ask questions like this, I really appreciate your help!

DuncG Over a year ago

This is not a good way to set parameters as it does not handle punctuation (quotes around string literal) and could be used to inject malicious actions or side effects (eg some jdbc implementations would allow addition of currentUserName = "'xyz'; drop table Users"). Always use PreparedStatement and placeholder ? with bind parameters.

Collectives™ on Stack Overflow

Storing SQL queries in Java

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related