Identifiers with spaces and/or keywords need to be escapes

[Former user]

Originally reported on Google Code with ID 110

What steps will reproduce the problem?
1. Have a table with a space in name, like "Order Details" (from Nothwind DB) 
2. Access database.

What is the expected output? What do you see instead?
Expected : Database information
Instead : SQLite Syntax error

What version of the product are you using? On what operating system?
PHPLiteAdmin v1.9.2
Phalanger 3.0.0 (PHP 5.2.6)
Windows 7 Pro x64
IIS Express 7.5
Northwind sqlite database of Prado PHP framework sample "Northwind-db"

Please provide any additional information below.
Error was on Database::numRows, for  $this->select("SELECT Count(*) FROM ".$table);
Should be corrected to $result = $this->select("SELECT Count(*) FROM [".$table."]");

And maybe other queries :)

Thanks

Reported by maitredede on 2012-06-04 15:16:49

[Christopher Kramer]

``` Thanks for reporting this issue. It is related to issue #107, but not a duplicate though (table names vs. column names). It is another concrete example of issue #93.

Some of these things have already been fixed in SVN as long as I saw, so please try the current version 1.9.3 from SVN: http://phpliteadmin.googlecode.com/svn/source/1.9.3/phpliteadmin.php

In fact I would consider it bad practice to use spaces in identifiers, but as SQLite allows you to do so, of course we need to support it as well. ```

Reported by `crazy4chrissi` on 2012-06-04 17:29:01 - Status changed: `Accepted`

[Christopher Kramer]

``` I worked a lot on stuff like this in the last days. Check out the version in SVN if you'd like to test it. I only noticed just now that you proposed [table] whereas I used `table` (for newer SQLite) and 'table' (for old SQLite). It seems both approaches work (at least for table names), so it seems to be a matter of taste. Or does anybody know if there is any difference? ```

Reported by `crazy4chrissi` on 2012-10-06 22:28:59 - Status changed: `Started`

[Christopher Kramer]

Reported by `crazy4chrissi` on 2012-10-06 22:43:23

[Christopher Kramer]

``` "It seems both approaches work (at least for table names)" Well, "For table names" is correct, but for column names, [column] works better.

The problem is that SQLiteDatabase does not support ` backticks and ' cannot be used in all situations. Consider this example:

SELECT 'select','group by','comma,space column' FROM 'select comma, space table'

The FROM 'select comma, space table' works - no problem here.

But SELECT 'select' will not select the column 'select', but a column filled with the text 'select' - just as it should in standard SQL.

So using ' to escape columns does not work reliably, ` is not supported by SQLiteDatabase, so I'd say we go for [] which seems to work well. ```

Reported by `crazy4chrissi` on 2012-10-07 19:03:28

[Christopher Kramer]

``` Well, probably I should have read the manual first: https://www.sqlite.org/lang_keywords.html

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.

It works with all SQLite versions / extensions just like the [] syntax. ```

Reported by `crazy4chrissi` on 2012-10-07 21:23:03

[Christopher Kramer]

This seems to work quite reliably in the current SVN version, so I mark this as fixed.

Reported by crazy4chrissi on 2012-10-25 20:32:20 - Status changed: Fixed