Database (SQLite)

For a tutorial on how to use SQLite in Corona, please see the Database Access in Corona with SQLite tutorial.

Corona includes support for SQLite databases on all platforms. This is based on the built-in sqlite support on the iPhone, and a compiled version of SQLite on Android. Note that this increases the size of the Android binary by 300K.

SQLite is available in all versions of Android, iPhone, and iPad, as well as in the Corona Simulator.

The Lua API is provided by luasqlite 3 v0.7, source available from luaforge.net/projects/luasqlite/. The documentation for luasqlite3 can be viewed at luasqlite.luaforge.net/lsqlite3.html. This documentation also includes test and sample code. Also see SQLite Language Reference.

NOTE: When providing a file path to open(), make sure to use system.pathForFile(). Providing just a plain file name such as "my.db" will not work consistently across the simulator and devices, especially on Android (see system.ResourceDirectory).

Examples

The following example opens an in-memory database, creates a new table, adds some data, and finally displays the table's contents on the screen.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
require "sqlite3"
local db = sqlite3.open_memory()
 
db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content);
  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]
 
print( "version " .. sqlite3.version() )
 
for row in db:nrows("SELECT * FROM test") do
  local t = display.newText(row.content, 20, 30 * row.id, null, 16)
  t:setTextColor(255,0,255)
end

This example demonstrates how to open a file-based database and to check if the table exists before creating it. It also closes the database upon application exit.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--Include sqlite
require "sqlite3"
--Open data.db.  If the file doesn't exist it will be created
local path = system.pathForFile("data.db", system.DocumentsDirectory)
db = sqlite3.open( path )   
 
--Handle the applicationExit event to close the db
local function onSystemEvent( event )
        if( event.type == "applicationExit" ) then              
            db:close()
        end
end
 
 
--Setup the table if it doesn't exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]
print(tablesetup)
db:exec( tablesetup )
 
--Add rows with a auto index in 'id'. You don't need to specify a set of values because we're populating all of them
local testvalue = {}
testvalue[1] = 'Hello'
testvalue[2] = 'World'
testvalue[3] = 'Lua'
local tablefill =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[2]..[['); ]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[2]..[[',']]..testvalue[1]..[['); ]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[3]..[['); ]]
db:exec( tablefill )
db:exec( tablefill2 )
db:exec( tablefill3 )
 
--print the sqlite version to the terminal
print( "version " .. sqlite3.version() )
 
--print all the table contents
for row in db:nrows("SELECT * FROM test") do
  local text = row.content.." "..row.content2
  local t = display.newText(text, 20, 30 * row.id, null, 16)
  t:setTextColor(255,0,255)
end
 
--setup the system listener to catch applicationExit
Runtime:addEventListener( "system", onSystemEvent )

Replies

Viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
jmp909
User offline. Last seen 44 weeks 1 day ago. Offline
Joined: 14 May 2010

deleted

jhocking
User offline. Last seen 1 year 23 weeks ago. Offline
Joined: 4 Dec 2010

fyi for anyone looking up how to write SQL commands, the site linked above is really hard for a beginner to make any sense of. I just started to learn this stuff and my eyes glazed over pretty much immediately.

Much more useful were these two resources:
http://zetcode.com/databases/sqlitetutorial/
http://www.w3schools.com/sql/default.asp

(note that these were just a couple of the top results when I searched "sqlite tutorial" and there were plenty more)

I got up to speed very quickly by comparing the sample code on this page with the commands described in those tutorials.

alyanm
User offline. Last seen 2 years 48 weeks ago. Offline
Joined: 18 Jan 2011

I sure would have appreciated an example of using an existing read-only file. What I found is that you put your file "mydata.db" into the folder with the rest of your files. Then to open it you use system.ResourceDirectory like so :

1
2
local path = system.pathForFile("mydata.db", system.ResourceDirectory)
db = sqlite3.open( path )   

drkeeling6
User offline. Last seen 24 weeks 2 days ago. Offline
Joined: 5 Apr 2011

alyann - now that is helpful... and makes perfect sense!

kevin.partner
User offline. Last seen 1 week 5 days ago. Offline
Joined: 5 Oct 2011

Thanks Alyanm,
the key bit, for some reason, is to use system.ResourceDirectory and NOT system.DocumentsDirectory. Suddenly, my code now finds the table it couldn't find before.

afonseca
User offline. Last seen 1 year 46 weeks ago. Offline
Joined: 4 Sep 2010

Another resource I found helpful when working with SQLite is a light weight admin tool with a graphical interface called Lita. This will let you open the database and browse the structure of your tables. You can even execute SQL statements from the program to test as you develop. This tool is free and is built in Adobe Air so runs on all platforms.

alyanm
User offline. Last seen 2 years 48 weeks ago. Offline
Joined: 18 Jan 2011

That Lita is really sweet, I've been using the SQLite DB Browser which works but is very primitive. Lita is more like a proper db tool, thanks for the tip!

mark40
User offline. Last seen 1 year 41 weeks ago. Offline
Joined: 22 May 2011

I'm sorry if I'm missing something obvious, but is there a page with a reference of all of the sqlite3 methods available? I looked in the API documentation, and it only links here.

EDIT: I was missing something obvious: the link above (http://luasqlite.luaforge.net/lsqlite3.html) documents all of the methods.

Antheor
User offline. Last seen 4 days 18 hours ago. Offline
Joined: 22 Sep 2010

Thx for the lita link, it's indeed very cool.

But, trying to try the default code I get with a lita (database.db) database, I get the following error :

"Runtime error: file is encrypted or is not a database"

I also use the

1
2
local path = system.pathForFile("mydata.db", system.ResourceDirectory)
db = sqlite3.open( path ) 
hint.

Could you help ?

EDIT : SOLVED, just for people who faced the same issue : don't use export button on lita and everything will be ok.

ashnor
User offline. Last seen 41 weeks 5 days ago. Offline
Joined: 1 Sep 2011

How do you use parameterized query in SQLite?

I've tried understanding the documentations at:

http://luasqlite.luaforge.net/lsqlite3.html#methods for prepared statements

and looked at the example here:

http://stackoverflow.com/questions/1224806/how-to-quote-values-for-luasql

but I can't get this to work:

1
2
3
4
5
6
7
8
getTable = function( _key)
        local path = system.pathForFile("database.db", system.ResourceDirectory)
        local dc = sqlite3.open( path )
 
        local stmt = dc:prepare[[ SELECT * FROM Table WHERE Key = :Key ]]
        local sql = stmt:bind({key=_weaponType})
        return dc:nrows(sql)
end

I get the error:
attempt to index 'stmt' (a nil value)

on the line:
local sql = stmt:bind({key=_weaponType})

Anyone?

kevin.partner
User offline. Last seen 1 week 5 days ago. Offline
Joined: 5 Oct 2011

I'm not sure why you're using the prepared statement functionality when your question simply asks about passing parameters (as I understand it)

If you just want to get the whole of a table with a simple key match, this would do it:

1
2
3
4
5
6
7
8
local function db_fetchTable(database, dbtable, key, keyvalue)
   local SQL="SELECT * FROM "..dbtable.." WHERE "..key.."="..keyvalue
   local results={}
   for row in database:nrows(SQL) do
      table.insert(results,row)
   end
   return results
end

I appreciate I might be misunderstanding your requirement though!

ashnor
User offline. Last seen 41 weeks 5 days ago. Offline
Joined: 1 Sep 2011

Hi Kevin,

Two reasons:

1) I don't want to create a SQL string from concatenating inputs, as that isn't a good practice since it can potentially allow SQL injection attack when it's based on user input. (i.e. when a player enters their name)

2) I want to learn how to use a prepared statement in SQLite =)

Thank you for your feedback!

kevin.partner
User offline. Last seen 1 week 5 days ago. Offline
Joined: 5 Oct 2011

1) Hmmm. Firstly, you can avoid any possibility of SQL injection by sanitising your user input thoroughly. Secondly, I'm racking my brain to try to work out what effect an injection attack would have on a database stored on the user's mobile - other than to break their app of course.

2) Fair enough but I'm not sure how useful they are in mobile development.

Each to his own I suppose!

ashnor
User offline. Last seen 41 weeks 5 days ago. Offline
Joined: 1 Sep 2011

Hm should my concern about SQL-injection in a web-development background not extend to here? =)

kevin.partner
User offline. Last seen 1 week 5 days ago. Offline
Joined: 5 Oct 2011

SQL Injection is a serious problem for web apps where the attack can infect the entire database for all users and bring the entire app down. User input sanitising is the most commonly recommended method of preventing the success of such attacks.

In a mobile app, the SQLite database is a standalone file on a single mobile device so even if they could get past your protection methods they'd only break their copy of the app.

Just my opinion of course - it's certainly interesting to work out the capabilities of SQLite for its own sake. I just think SQL injection is pretty easy to prevent by simple means that still allow access to the core SQL functionality.

daveclarke
User offline. Last seen 2 years 15 weeks ago. Offline
Joined: 5 Jan 2012

Actually sanitizing user input is not the recommended approach to mitigate SQL injection. The recommended approach is to use prepared statements. Correctly sanitizing user input is notoriously difficult to get right. I take your point that there is no obvious risk associated with SQL injection on a mobile device database but then how many exploits are associated with obvious risks?

kevin.partner
User offline. Last seen 1 week 5 days ago. Offline
Joined: 5 Oct 2011

As long as you're careful to escape every string (or, as in my case, build database objects that do it automatically) then I think sanitizing is a perfectly acceptable approach in most circumstances.

My point is that this is irrelevant to the case in point - SQL injection attacks are intended to break into (or just break) a web-based SQL database. What we're talking about here is a database on the phone so even if they were able to break into it, all they'd see is data pertaining to themselves. Unless, of course, the developer is stupid enough to load sensitive data onto the phone - in which case SQL injection is likely to be the least of their problems!

Antheor
User offline. Last seen 4 days 18 hours ago. Offline
Joined: 22 Sep 2010

I'm following this thread this lot of interest, since I'm using sqlite3 for Corona.

I must admit I don't clearly understand :
1. "As long as you're careful to escape every string"
and
2. (or, as in my case, build database objects that do it automatically)

Any help or concrete example (with sqlupdate) would be cool :)

daveclarke
User offline. Last seen 2 years 15 weeks ago. Offline
Joined: 5 Jan 2012

Thanks for your prompt reply Kevin. I'm not criticising your personal approach - apart from your input to this thread I've no knowledge of your coding ability or practices. I'm just trying to correct the statement input sanitising is the most commonly recommended method of preventing the success of such attacks. As documented in the link I posted, the recommended approach to mitigate SQL Injection attacks is to use prepared statements. Input sanitising is a useful additional layer of protection per the principle of Defense in Depth.

With respect to the applicability of SQL Injection to the mobile space, you are correct - having access to the device itself means attempting to protect against SQL Injection is probably not relevant. It might be more relevant though if the local SQLite database is a cache for a cloud or server database. As with everything, it's about context.

PaulTech
User offline. Last seen 27 weeks 2 days ago. Offline
Joined: 21 Dec 2011

Antheor:

Dave Clarke's earlier link to wikipedia provides a good explanation of SQL injection attacks:

en.wikipedia.org/wiki/SQL_injection

On a very basic level, consider you had a high score table named HIGH_SCORE with the fields name and score. Say you wanted to insert the users name and score. After getting their name through an input box, you might run some SQL that looks like this:

sql = "Insert into HIGH_SCORE values ('" .. name .. "','" .. score .. "')"

So if the input were "Paul" and the score was 1000 it would go to the SQL engine as:

Insert into HIGH_SCORE values ('Paul', '1000')

But say the user put in the following input:

Paul', '99999999') --

Your SQL statement then would end up looking like:

Insert into HIGH_SCORE values ('Paul', '99999999') --1000')

The double dash in SQL is a comment, so the last part of your statement would be commented out.

To defend against this when doing web development, I typically use parametrized queries. The wikipedia article has good examples. You can also try to parse for dangerous characters. Some developers do both.

How much it matters in a local sqlite database is up for debate. I agree with kevin.partner that it is probably not that important.

It is however critically important in web programming, so if you ever find yourself writing an app that accesses a mutliuser database, make sure you know about SQL injection.

Antheor
User offline. Last seen 4 days 18 hours ago. Offline
Joined: 22 Sep 2010

Thx for your explanation, it's really cool :))

andetodd
User offline. Last seen 18 weeks 1 day ago. Offline
Joined: 24 Jun 2011

If you get an error about attempting to close database that is already closed, and you're using the sample code above, add a check to see if the database is already closed:

--Handle the applicationExit event to close the db
local function onSystemEvent( event )
if( event.type == "applicationExit" ) then
if db:isopen() then
db:close()
end
end
end

richiesgm
User offline. Last seen 1 week 2 days ago. Offline
Joined: 5 Mar 2012

Hi! Is there a way that I can open the SQLite database that is in a remote server?

duncanys8Boudreaux
User offline. Last seen 1 year 40 weeks ago. Offline
Joined: 16 Jul 2012

Thanks a lot for the scripts posted here and for the whole discussion. First, it was interesting for me to read this info, second it was very helpful for my work now. This is awesome!
playscrabble.net

l.kallert
User offline. Last seen 34 weeks 19 hours ago. Offline
Joined: 14 May 2012

I used this code from above in the main lua and no other code but in the corona simulator it works and on the iPhone it does´t work could anybody help and give me some advise where the problem could be?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--Include sqlite
require "sqlite3"
--Open data.db.  If the file doesn't exist it will be created
local path = system.pathForFile("data.db", system.DocumentsDirectory)
db = sqlite3.open( path )   
 
--Handle the applicationExit event to close the db
local function onSystemEvent( event )
        if( event.type == "applicationExit" ) then              
            db:close()
        end
end
 
 
--Setup the table if it doesn't exist
local tablesetup = [[CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content, content2);]]
print(tablesetup)
db:exec( tablesetup )
 
--Add rows with a auto index in 'id'. You don't need to specify a set of values because we're populating all of them
local testvalue = {}
testvalue[1] = 'Hello'
testvalue[2] = 'World'
testvalue[3] = 'Lua'
local tablefill =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[2]..[['); ]]
local tablefill2 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[2]..[[',']]..testvalue[1]..[['); ]]
local tablefill3 =[[INSERT INTO test VALUES (NULL, ']]..testvalue[1]..[[',']]..testvalue[3]..[['); ]]
db:exec( tablefill )
db:exec( tablefill2 )
db:exec( tablefill3 )
 
--print the sqlite version to the terminal
print( "version " .. sqlite3.version() )
 
--print all the table contents
for row in db:nrows("SELECT * FROM test") do
  local text = row.content.." "..row.content2
  local t = display.newText(text, 20, 30 * row.id, null, 16)
  t:setTextColor(255,0,255)
end
 
--setup the system listener to catch applicationExit
Runtime:addEventListener( "system", onSystemEvent )

I would be very pleased if you could help maybe it is a built failure or something else because in corona simulator it works very fine so i can´t understand why it does´t work on iPhone

Lenny