Data normalization on insertion

Question

Please excuse the noob question but SQL is not where my strength lies.

Let's say that I have a table where I am storing users' data. Typically there are columns of type TEXT such as name, email...

e.g.

CREATE TABLE users (
    full_name text NOT NULL,
    email     text NOT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (email)
);

Since this data is coming from user input(web application), the data can be inconsistent( mix case, with additional space....)

My question is, how are usually these scenarios handled? Do you lowercase and trim spaces on insertion? Or is there a better approach to handle this?

Let's say all data is lowercased on insertion, does this mean that when querying this table, do you also have to lowercase the condition value in the WHERE clause so that you get a match?

Thanks

Answer 1

If you are being properly paranoid, you should treat data read from the database with suspicion, just as you do data directly from users. A bug in some other piece of code could have allowed off-spec data to be added. In your example, if you want the values of certain columns to always be lower-case with no trailing or leading spaces, then apply the relevant functions to the values in your query instead of trusting that the data will be stored in that format. Whether you do this cleaning in your SQL query or in other application code is up to you.

I would also clean up data on insert (trimming off trailing & leading white space that could have accidentally been added by copy+paste and not noticed by the user, etc.) but I would not rely on all code that ever interacts with the DB will correctly do so.

This may seem overly paranoid for apparently innocuous things like trailing whitespace, but it can be vital for security reasons. If you output a value into code after reading it from the database you need to properly escape it at point-of-use to avoid injection issues, rather than trusting the data has been cleansed on insert, lest you be visited by Little Bobby Tables (https://xkcd.com/327/)! Also note that bugs in code leading to bad data in the DB is not your only worry, there is the more rare but still common enough possibility of data corruption due to filesystem or hardware issues or bugs in the DB itself.

As a side note, “apparently innocuous things like trailing whitespace” may not be an innocuous as you imagine. In many SQL DBs trailing space is not considered when comparing strings, but it is in most application languages: in MS TSQL SELECT CASE WHEN 'test'='test ' THEN 'Equal' ELSE 'Not' END will show the values to be considered equal despite the trailing space, but in JS if('test'=='test ') console.log('Equal'); else console.log('Not'); will show the two values are not considered equal. This can lead to subtle errors and related confusions.

do you also have to lowercase the condition value in the WHERE clause so that you get a match?

This complicates things somewhat because of performance issues. Applying data cleaning functions to comparisons in WHERE and JOIN … ON clauses will likely block the database engine's ability to use indexes to speed the query as it will make the terms un-sargable (see What does the word "SARGable" really mean?). This is why cleaning data on the way into the database can be vital too.

For upper/lower case issues using a case-insensitive collation is the usual solution and some databases (MS SQL Server for instance) generally do so by default, though this is not a silver bullet as there are other considerations for non-english text (for instance the question of whether you want accent sensitivity or not, and more complex case rules found in other languages).

tl;dr:

Clean data read from the DB as you would data read from the user, just in case malformed data has got in there.

Clean data going into the DB too, especially if later performing string comparisons in the DB and needing to do so efficiently.

For efficient handling of string comparisons in the DB, consider using a case-insensitive collation if your DB does not by default.