Is it okay to hard-code table and column names in queries?

Question

I've got a backend running on Node that executes queries on a PostgreSQL database. For these queries, table and column names are imported from a .env file, for example:

const ID = process.env.ID_COL;
const USERNAME = process.env.USERNAME_COL;
const USERS = process.env.USERS_TABLE;
...
const query = `SELECT ${USERNAME} FROM ${USERS} WHERE ${ID} = $1;`;
...

This is in case I ever rename a table or column, but so far I haven't had to do that, and I'm wondering whether a simpler query with hard-coded table and column names would still be safe:

const query = 'SELECT username FROM users WHERE id = $1;';

Has having configurable table and column names in your queries ever helped you during refactoring, or am I being paranoid about futureproofing?

Answer 1

am I being paranoid about futureproofing?

tl;dr: My view is: "yes."

having configurable table ... names in your queries

No, that typically is not helpful.

I have worked with lots of projects based on {Oracle, MySQL, MariaDB, Postgres, etc.} and the two motivating use cases for a "foo" table tend to be:

1. prod_foo vs dev_foo (or QA)
2. big_foo vs small_foo

The first case should be dealt with in the connect string. So we connect to a different database server, or at least as a different user -- both of those give us "more namespace" so there can be more than one "foo".

The second case comes up in the edit-run-debug cycle. Maybe a "big" query takes at least an hour and a query against a "small" example table takes closer to one minute. My advice? Wait for such a situation to arise, and then parameterize the table name.

The small (experimental test) table might be constructed from the big table with a restrictive WHERE clause which asks for < 1% of the rows, so we can interactively try out several variations before waiting for an hour-long "real" query to validate the query's correctness.

It's worth noting that, again, creating more namespace via a variation on the connect string might be the appropriate response here.

having configurable ... column names in your queries

No. Column names are fundamental to the schema. During code review I would insist that column names be constant literal text. But I don't recall a code review where that was ever an issue.

Sometimes we see a terrible table organization that originated in a spreadsheet, like columns named sales_jan, sales_feb, ..., sales_dec. Which motivates looping through column names in order to generate a tidy dataset. Your question wouldn't apply to such ETL code, as tidying would necessarily have to loop over such column names.

---

table ownership

Let's say you do rename a table.

It happens. What code will be affected?

There should be exactly one module which writes to the table. So, CREATE TABLE, CREATE INDEX, INSERT, UPDATE, that sort of thing. A global search-and-replace won't be painful in that restricted context. Plus your CI/CD and unit tests will help you to notice anything you've overlooked.

Reigning in the readers might be a more interesting challenge. Search-and-replace, combined with unit tests, can be effective. If you notice that more than a couple of modules are doing direct SELECTs on a table, then define a helper function or an API endpoint and funnel them all through that.

The key is to have just a small amount of code which owns the writing / reading relationship with the table. So if the table changes, only that code will have to change.

---

table rename

Suppose we rename table foo to foo1. Notice that this bit of DDL will make the rename transparent to most queries:

CREATE  OR REPLACE  VIEW foo AS
SELECT *
FROM foo1;

Variations on this theme apply to column renames.

Answer 2

Has having configurable table and column names in your queries ever helped you during refactoring,

No.

or am I being paranoid about futureproofing?

I don't think the concern for future proofing is inappropriate, only the proposed solution.

Analysis

The problem here is that the database is usually the bottom layer of an application.

The structuring and naming of the storage scheme is one of the fundamental items of programming work and the one of the main sources of essential complexity.

If the database schema is overhauled in any way, you'd expect to have to rewrite and recompile any application code, because the application code is closely fitted to, and depends upon, the database schema (i.e. the schema of storage for the data upon which the application operates).

If the database structure is changed, then typically the application logic (not just naming) will have to change to realign with the new structure. Or a thunk will have to be introduced which converts the new structure of the database back to the old structure around which the application was originally written. Such structural change to the database necessarily implies fundamental changes to the application code.

The only time when configurable table and column names would help is when the structure is not changed in the database, but only the naming scheme there.

It's not unusual to want to change the names of things without changing the structure - to incorporate new concepts as the system grows (which requires old concepts to be renamed to properly distinguish them from newer additions), and to ensure that names continue to correspond with how staff think and talk about the system.

This is because the naming scheme (i.e. the internal ones that users never see directly) only exists for the benefit of the staff who work on developing and maintaining the system - the computer could as easily work with unnamed, arbitrarily-numbered tables and columns.

But effectively, your "configurable" names only help you in the simpler case of when renaming occurs without restructuring. It does not help when renaming occurs during restructuring (or when things are restructured but not renamed).

And there's a price to be paid for this "configurability", which is all the design overhead, the boilerplate code that has to be added to support it, the mangling and parameterisation of the SQL code, and an additional layer which links soft-coded parameters to the context of hard-coded SQL text (which, in turn, will probably require a naming scheme, which means you end up with a shadow naming scheme hard-wired into the code again). All this overhead for configuring names may, itself, have to be changed if there were structural changes.

Rather than avoiding maintenance work later, you are making immediate additional work necessary, plus adding significantly to the maintenance burden later (by making the code generally more complex and less obvious).

A category of wrong solutions

This proposal ultimately falls into a known category of error, which is where developers obsess about avoiding any kind of possible "maintenance" which involves direct modification or recompilation of source code, but the solution takes the form of providing auxiliary "configuration data", which is not data in the sense of "business data", but is really just boiled-down source code.

Typically, only a developer with access to and deep familiarity with the application's source code (i.e. someone who could easily edit and recompile the source code directly), could hope to understand what exactly these configuration codes do, and when or how they would be correctly altered.

The right solution

The appropriate way to manage an interface between application code and a database, is to have a data access layer in the application - a concentration point through which all calls to the database pass, and which contains database-specific code like SQL code which incorporates the names of things as they are in the database.

The idea is that if anything changes in the database, you know where in the application you need to look to start updating names or tracing further dependencies (or assessing what impact a proposed change in the database would have).

Answer 3

Code Maintainence

The environment file and SQL schema must be kept in synch. How do you guarantee they are consistent? Off-hand this should be part of the build process.

Environment file entries are ambiguous. process.env.ID_COL; - which table might this be from?

The queries are so trivial that the overhead does not seem justified. To claim SELECT {} FROM {} WHERE {} = $1 is reusable, is farcical. This basic syntax will have so many one-off instances that worst case, will make the environment file evolve its own variable naming conventions to map every unique query's parameter set. I can envision "comment variable names" - a name so long that it should be a comment instead.

Code readability and understandability will be difficult for reasonably complex queries.

---

This is in case I ever rename a table or column

Well, I'd insist that column names not have "column" in them - process.env.ID_COL - but this should not get past the review process in the first place.

The application is in use? Why would this need to happen after requirement analysis, design, design review, coding, code review, etc? Is the code base so perfect that y'all just need some excitement? How can you justify the cost in time and $$$ for the exhaustive regression testing this would require. What about other systems that use that DB or share its data?

---

simpler query with hard-coded table and column names would still be safe?

A string concatenation query built with user input is inherently unsafe. I think SQL injection risk is not mitigated by variable-izing table and column names.

Avoid string concatenation to create queries

Little Bobby Tables

Little Bobby Tables Explained

PostgreSQL SQL Injection

Answer 4

There are a few "unusual" use cases where an application dynamically creates tables - for example creating summary/rollup data in tables that are prefixed with a date - for those special cases there probably isn't a choice - having a prefix is the only practical way to implement that logic.

However it's usually the case that if you are hand coding SQL's you accept that if the table changes you will need to manually update all the SQL with the new table name. That said there are several ways to minimize and/or eliminate the need to do such refactoring:

• Group your SQL's logically - instead of scattering SQL for table X throughout your code base - group it into a package / DAL so that you can easily find all the SQL that touches a particular table.
• Use an ORM (Object Relational Mapping) framework - typically they generate the SQL for you and provide an easy way to specify the table name in one place for all the SQL's they generate.
• Database views can be used to mitigate the risk of renaming a table - incase you miss a SQL.

I suspect most developers would feel that over the duration of a project, choosing one or more of the mitigations above will give you better future proofing than anything custom you could develop.

Answer 5

How to split your software up into multiple objects is a fundamental question of computer programming. Generically, the factors to consider are Coupling and Cohesion.

Having files that are ".env" and ".js" hides the fact that its all software. Changing one text file isn't fundamentally any different than changing any other text file.

Your example is practically an iconic ('inner platform') example of tight coupling and poor cohesion: you've got externally defined variable names for your variables.

And your specific example doesn't even properly address the issue you've identified: if your column names change, the column names in the recordset change, and the real work is just beginning.

I regard software partitioning as one of the "hard" problems of computer science. Every time you look at it, you get a different answer. Everyone who looks at it gets a different answer.

There is no "best" answer, and it's easy to imagine one specific situation where layering the variable name definitions in multiple files would be the best structure: an abstraction of an abstraction of an abstraction. As long as you know why you're doing it. The column names are already an abstraction of the data. There is typically another abstraction for the GUI, and often another abstraction for the middle-layer. If you need to insert a fourth abstraction, that's a warning flag that there may be something else wrong.

Answer 6

Is it okay to hard-code table and column names in queries?

Yes, and unless there is an overwhelming reason not to do so then you should as it will help prevent SQL injection attacks.

If someone gains access to your .env file and changes the code from

ID_COL = id
USERNAME_COL = username
USERS_TABLE = users

To:

ID_COL = id
USERNAME_COL = username
USERS_TABLE = users CROSS JOIN (SELECT 1 FROM secret_table WHERE username = 'Admin' AND password_hash = '1234567890abcdef')

Then suddenly your code is performing SQL injection attacks and the attacker can determine that there is: a table called secret_table; which has the columns username and password_hash; with a row with a given username and password hash.

By using external imports you have increased the attack surface and made your application less secure and since your values were effectively static data (since you have never changed them and are questioning whether you will ever change then) then you are only gaining vulnerabilities for no benefit.

Answer 7

Use ORM.

ORM stands for ObjectRelationalMapping. It is a piece of software that makes database look and behave like regular code.
It brings couple benefits:

1. You don't have to worry about mistyping table names, keywords or commas. ORM does not make mistakes.
2. Have you remembered to strip special characters, newlines, dashes and so firth from that variable you are concatenating into sql query string? ORM got you covered and will not let anything malicious slip by.
3. Have to change database type? You don't have to rewrite each and every query, most likely your ORM supports different databases already.
4. And you can specify a table mapping in single place, so if anything changes, you just change one spot and done.

The only time you should have to write SQL in plaintext is when you're performing maintenance on the database, and not in your application code.

Answer 8

It's a big ol' "meh" from me. Yeah, it's safe assuming you use it safely.

If you find yourself renaming tables or columns after something is launched to production then you're doing something wrong. Don't forget about field aliases as you build your SQL arsenal.

What happens when you decide to connect reporting software like Crystal Reports or Tableau to your data? You're Node code falsifies the feeling of safety in renaming database stuff on a whim.

As soon as you do anything outside of simplistic queries, such as joins, you will quickly find yourself with garbage like tbl1_id_col and tbl2_id_col. What happens when tbl1 is renamed to tbl_real? Now you have to search+replace your whole codebase anyways.