- - - By CrazyStat - - -

9. October 2012

Portuguese language file for CrazyStat

Filed under: CrazyStat,PHP — Tags: , , , , , — Christopher Kramer @ 14:42

I just noticed that I have not announced yet that CrazyStat has been translated into Portoguese by Pedro Cruz. Thanks a lot!

You can already download the file from SVN (click “Download this file”). Of course the file will be included in the next release.

Just upload it into your stat/src/lang-folder and select the language when logging in. You can also change the default language.

If you find any mistakes in this or any other language file, just let me know.

I hope you enjoy CrazyStat!


Try my Open Source PHP visitor analytics script CrazyStat.

7. October 2012

SQLite: escaping table and column names correctly

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


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"


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.



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"

6. October 2012

Raspberry Pi as a digital webradio

Filed under: Linux,Raspberry Pi — Tags: , , , , , , , — Christopher Kramer @ 19:41

As my FM radio just sounded painfully because of so much noise, I looked at my Raspberry Pi lying next to it and thought “would it work as a digital webradio?”.

So I went ahead and installed VLC player:

apt-get install vlc

Then I went to the website of the local radio station and downloaded the m3u-file to the 128 kbit/s MP3-stream. Then I opened the file in VLC and clicked Play.

And it works great! CPU usage is at about 40%, RAM at 70MB. Sound can be digitally transferred using HDMI or using the stereo jack.

By the way, you can change the audio output to the stereo jack like this:

sudo amixer cset numid=3 1

And to HDMI:

sudo amixer cset numid=3 2

Compared to normal standalone webradios, this is not even an expensive solution. Using the IO pins, one could add buttons to control it, add an IR receiver via USB to add an IR remote control. You could even control it over the web using your smartphone.

Power consumption of the raspberry pi is also okay for a webradio, even if you do not shut it down because booting takes too long. Maybe the XBMC distribution for the raspberry pi is even better for a usage like this.

Oh, there are so many cool usages for this board. I really need to build something cool out of it.

4. October 2012

PDO / sqlite: database table is locked

Filed under: DBMS,PHP,phpLiteAdmin — Tags: , , , , , , , — Christopher Kramer @ 22:06

At the moment I am working again on phpliteadmin, a  php-based web GUI for database administration of sqlite databases. While debugging, I stumbled across a problem that only occurred with the PDO extension (not with SQLiteDatabase or SQLite3). I got the following error message while trying to drop a table:

HY000 / 6 / database table is locked

By the way, you can use PDO::errorInfo() to get these error messages. So as the error correctly explains, the table I tried to drop seemed to be in use. PDO documentation for PDO::query() also explains the problem (even though DROP TABLE statements are fired using PDO::exec()):

If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

So I hunted for the open cursor on a resultset of the table in question and could not find any. Finally, I found the SQL statement which still had an open cursor:

SELECT * FROM sqlite_master

Thinking about this, it is obvious: When querying sqlite_master, you request information about database tables. If one of the table gets altered or dropped, this might change the data listed in the resultset on which I still have a cursor.

Maybe this is a special case as usually you do not query sqlite_master a lot. But in case you do, this might be useful information.

To solve the problem, as the manual says, simply release the cursor using PDOStatement::closeCursor() before dropping/altering tables.