iPhone SQLite Tutorial

Create a new Xcode project and chose a simple View based application. It should create an App Delegate and a View Controller.

So firstly we want to create a sqlite database file. Open up the Terminal and get into your project directory. Now type:

> sqlite3 names.db

This will create the file ‘names.db’ at the path your at in the Terminal. Now to check the database contents and close, type:

> .tables
> .quit

For a full set of sqlite3 commands check out this site.

Add this file, names.db, to your Xcode project (I created a group called DB and added it there). Lets create a new simple table in this database now:

> create table highscores(time DECIMAL);

This is a very simple table that stores highscores(useful for games!) which are time based.

Ok now lets get down to business. Lets read from the database first. In the view controller class, add some global variables for the database name:

#define DATABASE_NAME @"names.db"
#define DATABASE_TITLE @"names"

Now to add the methods to read from the database. The ‘getWritableDBPath’ method simple returns the database path in the App document bundle.

- (NSString *) getWritableDBPath {
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths objectAtIndex:0];
return [documentsDir stringByAppendingPathComponent:DATABASE_NAME];
}

The ‘loadTimesFromDatabase’ method uses the sqlite commands to fire a sqlite request and uses the ‘loadTimesCallback’ callback to handle the result. It also passes a mutable array into the callback so the response from the database can be stored and manipulated. Remember to add the ‘loadTimesCallback’ above the implementation of the view controller. Here’s the code:

static int loadTimesCallback(void *context, int count, char **values, char **columns){
NSMutableArray *times = (NSMutableArray *)context;
for (int i=0; i < count; i++) {
const char *nameCString = values[i];
[times addObject:[NSString stringWithUTF8String:nameCString]];
return SQLITE_OK;
}
- (void)loadTimesFromDatabase{
NSString *file = [self getWritableDBPath];
NSFileManager *fileManager = [NSFileManager defaultManager];
BOOL success = [fileManager fileExistsAtPath:file];
// If its not a local copy set it to the bundle copy
if(!success) {
//file = [[NSBundle mainBundle] pathForResource:DATABASE_TITLE ofType:@"db"];
[self createEditableCopyOfDatabaseIfNeeded];
}
sqlite3 *database = NULL;
if (sqlite3_open([file UTF8String], &database) == SQLITE_OK) {
sqlite3_exec(database, "select time from highscores order by time limit 10", loadTimesCallback, _scoresArray, NULL);
}
sqlite3_close(database);
}
- (void)saveTimeInDatabase:(NSString *)theTime {
// Copy the database if needed
[self createEditableCopyOfDatabaseIfNeeded];
NSString *filePath = [self getWritableDBPath];
sqlite3 *database;
if(sqlite3_open([filePath UTF8String], &database) == SQLITE_OK) {
const char *sqlStatement = "insert into highscores (time) VALUES (?)";
sqlite3_stmt *compiledStatement;
if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)    {

sqlite3_bind_text( compiledStatement, 1, [theTime UTF8String], -1, SQLITE_TRANSIENT);
}

if(sqlite3_step(compiledStatement) != SQLITE_DONE ) {
NSLog( @"Save Error: %s", sqlite3_errmsg(database) );
}

sqlite3_finalize(compiledStatement);

}

sqlite3_close(database);
}

-(void)createEditableCopyOfDatabaseIfNeeded{
// Testing for existence
BOOL success;

NSFileManager *fileManager = [NSFileManager defaultManager];

NSError *error;

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);

NSString *documentsDirectory = [paths objectAtIndex:0];

NSString *writableDBPath = [documentsDirectorystringByAppendingPathComponent:DATABASE_NAME];

success = [fileManager fileExistsAtPath:writableDBPath];

if (success)
return;
// The writable database does not exist, so copy the default to

// the appropriate location.

NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath]

stringByAppendingPathComponent:DATABASE_NAME];

success = [fileManager copyItemAtPath:defaultDBPath

toPath:writableDBPath

error:&error];

if(!success)   {

NSAssert1(0,@"Failed to create writable database file with Message : '%@'.",[error localizedDescription]);
}
}

The ‘saveTimeInDatabase’ method does an insert into the database using the sqlite commands.
This will NOT work on the emulator, but will work on the device! I have similar code in a recent game and it works well.
Heres the SqliteDB project with the code and i added some UI to the scene to add items to the database.
It took some time looking around to find example code to do this writable database, so hope this helps you out!

To create/open a database in Corona.

There are two ways you can go about creating a database. You can create a database in-memory, in which the life of your database only extends as far as the runtime of your app, or you can create a database file so it can be loaded at any time. Since you most-likely want to keep your data, this tutorial is not going to cover in-memory databases.

The example below is how you would open a saved database file, and/or create a database file (that does not yet exist):

require "sqlite3"

local path = system.pathForFile( "data.db", system.DocumentsDirectory )

local db = sqlite3.open( path )

Closing the database

When you’re finished with you’re database, it’s important to close the “connection” to it by using the close() method (of your database object, so it would something like: db:close()). You can do this at any time (as long as you don’t foresee yourself needing the database anymore), but I recommend calling this function in conjunction with an “applicationExit” system event. This will ensure the database connection is closed when your app is closed.

local function onSystemEvent( event )

if event.type == "applicationExit" then

if db and db:isopen() then

db:close()

end

end

end

Runtime:addEventListener( "system", onSystemEvent )

 

Sqlitespy for Sqlite Database analysis

Sqlite is the ubiquitous database for mobile applications on iPad, iPhone and Android. It is also used by certain internet browsers, web application frameworks and software products for their local storage needs. While doing penetration tests, we often see sensitive information like usernames, passwords, account numbers, SSN etc  insecurely stored in these databases. Thus, every penetration test requires comprehensive analysis of the local databases being used.

While analyzing databases, a penetration tester repeatedly does the following:

  • Opens the database in sqlite reader (sqlite3 or other readers)
  • Views various tables and columns to understand database layout and schema.
  • Analyzes the storage for sensitive information.

As the number and size of database increases, the analysis time increases exponentially. To escape the recurring pain, the following ruby script helps. The script achieves the following:

  • Analyzes multiple databases in a single run.
  • Queries and displays database schema.
  • Provides an option to run search on Table and Column Names for quick analysis.
  • Performs case-insensitive regular expression search (default).
  • Displays Database, Tables and Row Number reference for every successful match.
  • Dumps database rows on a successful match.
  • Looks for search strings in the following:
    • Table Name
    • Column Names
    • Actual Data

 

Sqlitespy Code:

require 'rubygems'

require 'optparse'

require 'ostruct'

require 'sequel'

class CmdLineOptions

def self.parse(args)

options = OpenStruct.new

options.dbs = []

options.sstrings = []

options.show_schema = false

options.case_sensitive = false

options.exact = false

options.verbose = false

options.rowdump = false

options.metadata = false

opts = OptionParser.new do |opts|

opts.banner = "Usage: sqlitespy.rb [options]\n\nSpecific Options:"

opts.on("-d", "--database DATABASE_PATH",

"Sqlite database to analyze.") do |db|

options.dbs << db

end

opts.on("-s", "--show-schema", "Show database schema") do |show|

options.show_schema = show;

end

opts.on("--find x,y,z", Array, "Strings to search") do |list|

options.sstrings = list

end

opts.on("-c", "--case-sensitive", "Perform case sensitive search. Default is case insensitive.") do |case_sensitive|

options.case_sensitive = case_sensitive;

end

opts.on("-e", "--exact--match", "Perform exact match for the search strings") do |v|

options.exact = v;

end

opts.on("-r", "--row-dump", "Dump Database Row when a match is found") do |v|

options.rowdump = v;

end

opts.on("-m", "--metadata", "Look for search strings only in DB metadata (table and column names)") do |v|

options.metadata = v;

end

opts.on("-v", "--verbose", "Verbose output") do |v|

options.verbose = v;

end

opts.on_tail("-h", "--help", "Show this message") do

puts opts

exit

end

end

opts.parse!(args)

options

end# parse()

end# class CmdLineOptions

options = nil

begin

options = CmdLineOptions.parse(ARGV)

rescue (OptionParser::InvalidOption)

$stderr.puts "[-] Invalid option "

options = CmdLineOptions.parse(ARGV+["-h"])

end

if(options.dbs.length == 0)

$stderr.puts "[-] No Database available. Exiting !!"

exit

end

dbs = []

options.dbs.uniq!

dbs = options.dbs.collect do |db|

begin

throw Errno::ENOENT unless(File.file?(db))

Sequel.sqlite(db).tables

db

rescue

$stderr.puts "[-] \"#{db}\" is not a sqlite database"

nil

end

end

options.dbs = dbs.compact

if(options.dbs.length == 0)

$stderr.puts "[-] No Database available. Exiting."

exit

end

options.sstrings.uniq!

if(options.show_schema)

puts

puts "+"*80

puts "Database Schemas"

puts "+"*80

options.dbs.each do |db|

puts

puts "[DATABASE] #{db}"

Sequel.sqlite(db) do |dbhandle|

dbhandle.tables.each do |table|

puts "\t[TABLE] #{table}"

puts "\t\t[COLUMNS] #{dbhandle[table.to_sym].columns.join(', ')}"

end

end

end

puts "-"*80

end

regex_strings = []

regex_strings = options.sstrings.collect do |search|

regexstr = ""

regex = nil

if(options.exact)

regexstr = "^#{search}$"

else

regexstr = "#{search}"

end

if(options.case_sensitive)

regex = Regexp.new("#{regexstr}")

else

regex = Regexp.new("#{regexstr}", Regexp::IGNORECASE)

end

regex

end

 

options.sstrings = regex_strings

options.dbs.each do |database|

if(options.verbose)

puts

puts "+"*80

puts "Analyzing Database '#{database}'"

puts "+"*80

end

Sequel.sqlite(database) do |databasehandle|

databasehandle.tables.each do |table|

if(options.verbose)

puts

puts "-"*80

puts "Analyzing Table '#{table}'"

puts "-"*80

end

options.sstrings.each do |regex|

if(regex.match(table.to_s))

puts "[+] Table Name Match Found -> Database '#{database}' -> TABLE '#{table}'"

end

end

#Column Name Search

databasehandle[table.to_sym].columns.each do |column_name|

options.sstrings.each do |regex|

if(regex.match(column_name.to_s))

puts "[+] Column Name Match Found -> Database '#{database}' -> TABLE '#{table}' -> COLUMN '#{column_name}'"

end

end

end

#Data Search

if(options.sstrings.length > 0 && !options.metadata)

row = 0

databasehandle[table].each do |rowHash|

row = row + 1

rowHash.each do |key, value|

options.sstrings.each do |regex|

if(regex.match(value.to_s))

puts "[+] Data Match Found -> Database '#{database}' -> TABLE '#{table}', COLUMN '#{key}' -> ROW '#{row}'"

puts "\t[*] Row Dump\t=>\t#{rowHash.values.join('|')}" if(options.rowdump)

end

end

end

end

end

end

end

end

SQL

The CONVERT() function allows you to convert dates in the same way as the CAST() function

mysql>
CONVERT(<expression>, <type>)
mysql>
mysql> SELECT CONVERT(20041031, DATE);
+————————-+
| CONVERT(20041031, DATE|
+————————-+
20041031              |
+————————-+
row in set (0.00 sec)

mysql>

PHP function to convert date from MM/DD/YYYY to YYYY-MM-DD

//Convert MM/DD/YYYY to YYYY-MM-DD. This format is useful if you want to insert into a MySQL date field.

function date_to_mysql_date($date){
$date_year=substr($date,6,4);
$date_month=substr($date,0,2);
$date_day=substr($date,3,2);


$date=date("Y-m-d", mktime(0,0,0,$date_month,$date_day,$date_year));
return $date;
}

Escaping single quotes while using SQLite in xcode

In SQLite, the escaping character that is used is single quotes(‘). So if you want to escape ” ‘ “, then you have to use like this ” ” “.

In xcode, this is what you need to do.

If you want to insert the string, xcode’s into the database with the single quotes, then this is what you have to do.

NSString *theStr = [NSString stringWithString:@”xcode’s”];

NSString *escapedStr = [thestr stringByReplacingOccurrencesOfString:@”‘” withString:@”””];

Now add the escapedStr into the database instead of theStr.