Don’t use string constants when writing SQL statements in code

By Adrian Smith14 Jun 2013600 words3 mins to read

I was always in two minds about using constants for table and column names when writing SELECT queries. Now I've concluded that constants are definitely bad, and should not be used. Here's why.

The topic of discussion is difference is between writing

sql = "SELECT * FROM " + TABLE_NAME + " WHERE ..."

and

sql = "SELECT * FROM my_table WHERE ..."

There are the following consequences from this choice as far as I can see:

To avoid having this choice in the first place:

Compare the following two pieces of code and I think the choice will become obvious. Both pieces of code come from the current code-base I'm working on, neither have I written myself.

sb.append("SELECT ").append(RecruiterRole.TABLE_NAME).append(".*,");
sb.append(Login.TABLE_NAME).append(".*");
sb.append(" FROM ").append(RecruiterRole.TABLE_NAME);
sb.append(',').append(Login.TABLE_NAME);
sb.append(" WHERE ");
sb.append(RecruiterRole.COLUMN_COMPANY_ID).append(" = ?");
sb.append(" AND ");
sb.append(RecruiterRole.TABLE_NAME).append('.').
sb.append(RecruiterRole.COLUMN_LOGIN_ID).append('=?');

vs

sql.append(" SELECT * ");
sql.append(" FROM application");
sql.append(" WHERE job_advert_id IN (");
sql.append("   SELECT job_advert_id");
sql.append("   FROM share");
sql.append("   WHERE talent_scout_login_id = ?)");
sql.append(" AND potential_applicant_identity_id NOT IN (");
sql.append("   SELECT potential_applicant_identity_id");
sql.append("   FROM positive_endorsement");
sql.append("   WHERE talent_scout_login_id = ?)");
sql.append(" AND company_id = ?");
sql.append(" AND share_talent_scout_login_id = ?");
sql.append(" ORDER BY datetime_utc DESC");

Here are the reasons why the second code is more readable:

The danger of the above code is less that errors in spelling will only be detected at run-time and not at compile-time, but that the query does the wrong thing (while appearing to do the right thing). For example, an error I saw recently (which obviously did not make the live system!) was that users could see data not only from themselves but from all users because the "WHERE login_id=?" had been forgotten. But to the untrained eye, or a user on the test system with only a few users, the query appeared to work.

In this case, it's a clear win for readability, over compile-time checking of a mistake which will is unlikely to happen and will be identified at run-time.

This article was written by Adrian Smith on 14 Jun 2013

Follow me: Facebook | Twitter | Email

More on: Coding | Databases