CODEWITHSUNDEEP
regexpostgresql
Cirus Polis
Cirus Polis

Reputation: 35

Matches only at the end of the string

I'd like to protect the email field of the following table:

CREATE TABLE users (
  email VARCHAR(255) NULL CHECK (email ~* '\A[^@]+@[^@]+\Z')
);

What I would like to do is to allow strings such as:

bob@example

But I would like to avoid strings such as:

bob@example\nfuu

I heard that the \Z constraint allows any chars after another line (with \n).

According to best practices in regex, the \z is better than \Z as it allow only one line, but it seems to be not supported by PostgreSQL. And the $ is not better.

Am I true?

Edit:

I tested this:

CREATE TABLE users (
  email VARCHAR(255) NULL CHECK (email ~* '\A[^@\n]+@[^@\n]+\Z')
);

CREATE UNIQUE INDEX users__lower_case__email ON users(lower(email));

--

INSERT INTO users (email) VALUES ('\nfoo\n@\nbar\n');

Apparently the constraint didn't work: the wrong email was added in the table.

Upvotes: 1

Views: 78

Answers (2)

Evan Carroll
Evan Carroll

Reputation: 1

I would highly suggest you review my post for the right way to store an E-Mail address in PostgreSQL.

Here is some example code,

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

SELECT '[email protected]'::email;

For your table,

CREATE TABLE users (
  user_email  email
);

Upvotes: 0

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626699

Note that negated character classes match any characters but those defined in the set. So, [^@] matches any chars but @, including newline symbols. To exclude a newline, just add it to the class.

Use

email ~* '\A[^@\n]+@[^@\n]+\Z'

As \Z only matches only at the end of the string there is no way this regex could allow a newline in the input.

Upvotes: 1

Related Questions