DeutschEnglish

Submenu

- - - By CrazyStat - - -

7. October 2012

SQLite: escaping table and column names correctly

Filed under: DBMS,PHP,phpLiteAdmin — Tags: , , , , , , , , , , — Christopher Kramer @ 21:44

Summary:

Use double quotes around column names, table names, trigger names etc.

SELECT "column name", "my column", FROM "table name"

Use single quotes around string values

SELECT * FROM "table name" WHERE "column name" = 'string value'

Use two double quotes if e.g. your column name contains a double quote (e.g. my”column)

SELECT "my""column" FROM "my""table"

Details:

I joined the development team of phpliteadmin some months ago and am currently fixing several issues. One problem is caused by SQLite’s flexibility of column- and table names. A column named “SELECT”, “my column”, “this, that”, or even “this contains ‘single quotes'” is completely legal in SQLite. And what is legal is being used. So phpliteadmin needs to be able to handle these types of names as well, which is not really the case at the moment.

Let’s assume you query a column called “select”, you are likely to get errors like that in PHP:

Warning: SQLiteDatabase::query() [sqlitedatabase.query]: near “select”: syntax error in …

Or using PDO:

HY000 / 1 / near “select”: syntax error

From MySQL I knew backticks around table or column identifiers like in this example:

SELECT `select`, `my column`, `this, that` FROM `group by table`

(By the way, you see my syntax highlighter has problems with reserved words as column names as well ;-))

I tried this in SQLite and it worked, so I went that way. But there are different versions of SQLite and for the old version that, in PHP, is implemented by SQLiteDatabase, would give the following error:

Warning: SQLiteDatabase::query() [sqlitedatabase.query]: unrecognized token: “`” in …

So I found out that instead of backticks, normal single quotes work:

SELECT * FROM 'group by table'

This works well, but there is a problem here with column names:

SELECT 'select' FROM 'group by table'

This will not work as expected. It will not return the values of the column ‘select’, but create a column filled with the text ‘select’. That is of course standard SQL behaviour and shows that it is a bad idea to use single quotes around column or table names. The bug report by maitredede on the phpliteadmin issue tracker made me aware of another way to escape column and table names:

SELECT [select], [my column], [this, that] FROM [group by table]

This works very well, both with SQLiteDatabase, SQLite3 and PDO when accessing SQLite from PHP. I have not tried it using the SQLite C library directly, but it should work as well.

So I’d say this is the best way to do this as it works the same way with all versions of SQLite / all PHP SQLite extensions.

 

Update:

Well, probably I should have read the manual first:

SQlite supports ` for compatibility with MySQL and [] for compatibility with MS Access and SQL Server. But it also supports double quotes, which is standard SQL. So I’d say this is the best way to do it:

SELECT "select", "my column", "this, that" FROM "group by table"

It works with all SQLite versions / extensions just like the [] syntax. (And my syntax highlighter supports it as well ;-).)

Update 2:

Another thing that might be of interest: What to do, if a column/table name contains double quotes? Well, in this case duplicate them and you are done. Let’s assume you have a column called like this:

my “column

Then you query it like this:

SELECT "my""column" FROM "my_table"

Recommendation

Try my Open Source PHP visitor analytics script CrazyStat.

14. August 2012

MySQL: How to do a (simple/complex) CHECK CONSTRAINT?

Filed under: DBMS — Tags: , , , , , , , , , — Christopher Kramer @ 02:27

Another “badly-missing-feature”-post. I should create a category for that I guess…

Usually, you can add a constraint to a table in SQL with something like:

ALTER TABLE foo ADD CONSTRAINT my_constraint CHECK (bla<>0)

Some sites like w3school even tell you this is the syntax for it in MySQL. Well, I guess they have never tried this and not even read the manual. Well, you can execute it in MySQL without an error. But also without any effect whatsoever. I tried it in InnoDB (MySQL 5.5) and it did not check the constraint at all.

The MySQL manual says:

The CHECK clause is parsed but ignored by all storage engines

Hallelujah, MySQL is great. A bug request for that exists since 2004, so don’t expect it to be fixed soon…

So we go with a trigger instead. For constraints, we use a BEFORE trigger. So next thing I tried looked something like this:

CREATE TRIGGER check_my_constraint BEFORE UPDATE ON `foo`
     FOR EACH ROW 
     BEGIN 
     IF (NEW.bla=0)
     THEN 
     ROLLBACK;
     END IF; 
     END

Should work, but doesn’t. Problem is that MySQL doesn’t like ROLLBACK here. Hallelujah, MySQL is great. So what worked in the end is sending an error signal:

CREATE TRIGGER check_my_constraint BEFORE UPDATE ON `foo`
     FOR EACH ROW 
     BEGIN 
     DECLARE msg varchar(255);
     IF (NEW.bla=0)
     THEN 
        SET msg = concat('Constraint my_constraint violated: bla must not be zero ', cast(new.bla as char));
        SIGNAL sqlstate '45000' SET message_text = msg;
     END IF; 
     END

Thanks to Rui Da-Costa for the solution with the SIGNAL.

The cool thing about triggers is that you can do complex checks here. So I just used it for something like:

 IF (NEW.ref_id IS NOT NULL AND
     NEW.ref2_id<>(SELECT `ref2_id` FROM `ref` WHERE `ref`.id=NEW.`ref_id`))

So that makes sure that ref_id is either null or it refers to a row in table `ref` with this id that has the same ref2_id as the table we are checking. So a more complex type of foreign key relationship.

Hope this helped somebody to get things solved more quickly. Don’t forget to add an INSERT trigger as well.

Another remark: Make sure you do not use semicolon as delimiter when using SQL/PL syntax. phpMyAdmin has an extra field to change the delimiter (below the textarea where you enter your SQL) for example.