PostgreSQL Pattern Matching – Find Rows Without Spaces

pattern matchingpostgresqlpostgresql-9.5

I'm using Postgres 9.5. I want to search for rows in which my name column does not contain a space. I'm a little murky on how to define a space to you, though. I thought it would just be the space bar on my keyboard, so I ran:

.... where name not like '% %';

but then I got some results like this:

 | JASON FALKNER

That sure looks like a space to me, but there are probably some other things going on. Is there a better way I can scan for rows in which my name column doesn't contain a space?

Using a regexp, not (name ~ '\s') still returned columns that looked like they had a space.

Using:

select cast(name as bytea) ... where name not like like '% %';

returned:

\x4a41534f4ec2a0424c414b45

However, I'm still a little unclear how I use that data to figure out how to screen spaces from my results.

I tried where not (name ~ '[[:space:]]')' and it's returning "JASON BLAKE" with the same byte sequence above, \x4a41534f4ec2a0424c414b45.

Best Answer

I suggest you provide explicitly the characters you want to be considered as "white space" and excluded to a regex:

where name !~ '[ \t\v\b\r\n\u00a0]'

Characters:

\s         white space (space, \r, \n, \t, \v, \f)
' '        space
\t         (horizontal) tab
\v         vertical tab
\b         backspace
\r         carriage return
\n         newline
\f         form feed
\u00a0     non-breaking space
---

See the docs for pattern matching.

In your example, note that \xC2A0 is the UTF-8 representation of Non breaking space (00A0).

Related Solutions

How to Use AES Encryption in PostgreSQL

\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs):

                                List of functions
 Schema |      Name       | Result data type |   Argument data types    |  Type  
--------+-----------------+------------------+--------------------------+--------
 ...
 public | decrypt         | bytea            | bytea, bytea, text       | normal
 public | encrypt         | bytea            | bytea, bytea, text       | normal
 ...

so both the encrypt and decrypt functions expect the key to be bytea. As per the error message, "you might need to add explicit type casts".

However, it works fine here on Pg 9.1, so I suspect there's more to it than you've shown. Perhaps you have another function also named encrypt with three arguments?

Here's how it works on a clean Pg 9.1:

regress=# create table demo(pw bytea);
CREATE TABLE
regress=# insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
INSERT 0 1
regress=# select decrypt(pw, 'key', 'aes') FROM demo;
  decrypt   
------------
 \x64617461
(1 row)

regress=# select convert_from(decrypt(pw, 'key', 'aes'), 'utf-8') FROM demo;
 convert_from 
--------------
 data
(1 row)

Awooga! Awooga! Key exposure risk, extreme admin caution required!

BTW, please think carefully about whether PgCrypto is really the right choice. Keys in your queries can be revealed in pg_stat_activity and the system logs via log_statement or via crypto statements that fail with an error. IMO it's frequently better to do crypto in the application.

Witness this session, with client_min_messages enabled so you can see what'd appear in the logs:

regress# SET client_min_messages = 'DEBUG'; SET log_statement = 'all'; 
regress=# select decrypt(pw, 'key', 'aes') from demo;
LOG:  statement: select decrypt(pw, 'key', 'aes') from demo;
LOG:  duration: 0.710 ms
  decrypt   
------------
 \x64617461
(1 row)

Whoops, key possibly exposed in the logs if log_min_messages is low enough. It's now on the server's storage, along with the encrypted data. Fail. Same issue without log_statement if an error occurs to cause the statement to get logged, or possibly if auto_explain is enabled.

Exposure via pg_stat_activity is also possible.. Open two sessions, and:

S1: BEGIN;
S1: LOCK TABLE demo;
S2: select decrypt(pw, 'key', 'aes') from demo;
S1: select * from pg_stat_activity where current_query ILIKE '%decrypt%' AND procpid <> pg_backend_pid();

Whoops! There goes the key again. It can be reproduced without the LOCK TABLE by an unprivileged attacker, it's just harder to time it right. The attack via pg_stat_activity can be avoided by revoking access to pg_stat_activity from public, but it just goes to show that it might not be best to send your key to the DB unless you know your app is the only thing ever accessing it. Even then, I don't like to.

If it's passwords, should you store them at all?

Furthermore, if you're storing passwords, don't two-way encrypt them; if at all possible salt passwords then hash them and store the result. You usually don't need to be able to recover the password cleartext, only confirm that the stored hash matches the password the user sends you to log in when it's hashed with the same salt.

If it's auth, let someone else do it for you

Even better, don't store the password at all, authenticate against LDAP, SASL, Active Directory, an OAuth or OpenID provider, or some other external system that's already designed and working.

Resources

and lots more.

MySQL – SQL MATCH AGAINST Incomplete String with Space

If you want to use Full-Text Search, then in order to get an exact phrase match it would seem that doing a Boolean Full-Text Search would be the way to go, enclosing the term New York in double-quotes, hence:

SELECT * FROM places WHERE MATCH (field) AGAINST ('"New York"' IN BOOLEAN MODE);

But, if you are looking to do a "contains" type of search where the user-entered value is a literal fragment (i.e. what they entered needs to match as-is, but can also be a part of a larger string and is not necessarily the entire match), then perhaps doing a regular LIKE '%{user_input}%' would be best.