CODEWITHSUNDEEP
sqlitecheck-constraints
sqlearner
sqlearner

Reputation: 107

SQLite, ASCII Characters 0x20 to 0x7E Check Constraint

I have the following table where I'm trying to limit the column "name" to ASCII characters ranging from 0x20 to 0x7E (all printable ASCII characters). Why is my check constraint not working?

CREATE TABLE test
( 
    id INTEGER NOT NULL,
    name TEXT NOT NULL CHECK(name NOT GLOB '*[^\x20-\x7E]*'),
    PRIMARY KEY(id)
)

For instance: 

INSERT INTO test (name) VALUES("Tom");

Expected: Should add a touple with name: "Tom" to the table (as the inserted name only consists of printable ASCII characters). Result: CHECK constaint failed: test

Upvotes: 1

Views: 677

Answers (2)

Pruzo
Pruzo

Reputation: 97

Solution
name NOT GLOB cast(x'2a5b5e202d7f5d2a' as TEXT)
Explanation

To assure all characters in a string are printable ASCII, we try to look for characters that aren't. If no such character is found, the string is printable ASCII. Conceptually the query could look like this:

name NOT GLOB '*[^\x20-\x7E]*'   -- (doesn't work in SQLite)

Here the GLOB operator matches any character not in 0x20-0x7E and since we negate the expression with NOT, the query resolves to logical true if no characters outside the range are found. However, SQLite does not let you escape hex characters in string literal, e.g., '\x20' is not a hex-encoded space character. As a work-around, we provide the same GLOB pattern as a byte sequence x'2a5b5e202d7f5d2a' and cast it to TEXT, leaving us with:

name NOT GLOB cast(x'2a5b5e202d7f5d2a' as TEXT)

Now this may be too ugly for legit developers, but for us naughty rascals messing with SQL Injection this is just perfect.

PS: I implemented a similar logic in my Blind SQL Injection framework. Interested readers can find it here.

Upvotes: 0

Shawn
Shawn

Reputation: 52354

Backslashes are not special in SQL strings. '\x20' is 4 individual characters, not an escaped single character, for example. So your GLOB pattern always matches (Because T is not a character in the set), and the NOT makes it, and thus the constraint, fail.

Upvotes: 1

Related Questions