sqlite read Safari database

Scripting Forums AppleScript | Mac OS X
#1

Hi,

I downloaded this from the internet:

It’s supposed to read the Safari cache database or something like that. The sqlite part looks hard to understand. How would you make this run from AppleScript.

THanks,
kel

#2

Hey kel,

That’s interesting. I’ve been wanting to learn a bit of SQL to be able to do more sophisticated queries of Safari and Mail.

Have you tried running that shell script from the Terminal, BBEdit, or TextWrangler?

To run it from Applescript all you have to do is quote it properly and use do shell script. Of course quoting can get a little hairy, so another option is to save the shell script as a text file and run it from Applescript:

set shellScript to "~/test_directory/shell_experiments/sql.sh"
do shell script shellScript

set _cmd to "#!/bin/sh
echo \"<!DOCTYPE html PUBLIC \\\"-//W3C//DTD HTML 4.01//EN\\\"\" > ~/Desktop/cache.html
echo \"    \\\"http://www.w3.org/TR/html4/strict.dtd\\\">\" >> ~/Desktop/cache.html
echo \"<html lang=\\\"en\\\">\" >> ~/Desktop/cache.html
echo \"  <head>\" >> ~/Desktop/cache.html
echo \"    <meta http-equiv=\\\"content-type\\\" content=\\\"text/html; charset=utf-8\\\">\" >> ~/Desktop/cache.html
echo \"    <title>Cached Images</title>\" >> ~/Desktop/cache.html
echo \"  </head>\" >> ~/Desktop/cache.html
echo \"  <body>\" >> ~/Desktop/cache.html
sqlite3 ~/Library/Caches/com.apple.Safari/Cache.db \"SELECT request_key, time_stamp FROM cfurl_cache_response WHERE request_key LIKE '%.jpg' OR request_key LIKE '%.jpeg' OR request_key LIKE '%.gif' OR request_key LIKE '%.png' ORDER BY time_stamp DESC;\" | perl -ne 'chomp; ($url, $time) = split(/\\|/); print \"<a href=\\\"$url\\\"><img src=\\\"$url\\\" alt=\\\"Downloaded at $time\\\"></a> <a href=\\\"$url\\\">$url</a> $time<br>\\n\";' >> ~/Desktop/cache.html
echo \"  </body>\" >> ~/Desktop/cache.html
echo \"</html>\" >> ~/Desktop/cache.html
open -a Safari ~/Desktop/cache.html
"

do shell script _cmd

Be aware that this script will open a monstrous page in Safari (if there’s much in your cache).

#3

You can make a query with something like this:

set databasePath to "~/Library/Caches/com.apple.Safari/Cache.db"
set tableName to "cfurl_cache_response"

set databaseQuery to do shell script "sqlite3 " & databasePath & " \"select request_key, time_stamp from " & tableName & " WHERE request_key LIKE '%.jpg' OR request_key LIKE '%.jpeg' OR request_key LIKE '%.gif' OR request_key LIKE '%.png' ORDER BY time_stamp DESC; \""
#4

Hi adayzdone,

So the query will tell you how much is in there?

THanks,
kel

#5

You quoted it.

Thanks Chris.

Is it safe. :slight_smile:

#6

That query will get every row whose data ends in .jpeg, .jpg etc. from the request_key and time_stamp columns of the cfurl_cache_response table. If you want to see all of the data from that table, the query would be:

set databaseQuery to do shell script "sqlite3 " & databasePath & " \"select request_key, time_stamp from " & tableName & " ORDER BY time_stamp DESC; \""
#7

It didn’t crash Safari on my machine, but it did open a massive page with about 4500 images from the cache. I looked briefly and then closed the page before Safari started hiccoughing too much.

I had taken the precaution of commenting out the ‘rm’ line at the end of the script, so I could look through the generated file with BBEdit.

Nothing untoward - just massive.

#8

SQLLite uses an full blocking protection. Which means when one instance is writing to the file all other instances will be blocked. So when reading out this database file won’t harm your database in any way, at worse when safari is writing excessively it will block your reading.

Edit: Keep in mind that latest Apple software loves to cache data an that files are relative incorrect. When you want to write data, It’s always better to close the application(s) that’s using the same files as you’re going to change.

The queries are quite simple and are general/standard sql queries. Basically you’re tell SQLLite what you want to select, from which tables and their relations (join), whose (where) matching an criteria, in which order and it limits/boudaries
SELECT column1, column2, etc
FROM table1 (LEFT JOIN table2 ON table1.column1 = table2.column1)
WHERE column = ‘matching a string’
ORDER BY column1 i[/i]
LIMIT begin,length

  • limit starts with 0 so to show the first row it should be 0,1 (starting at the first position and 1 row).

Or in this case the query looks like (all the same but better readable):

set theQuery to "SELECT request_key, time_stamp 
FROM cfurl_cache_response 
WHERE request_key LIKE '%.jpg' 
	OR request_key LIKE '%.jpeg' 
	OR request_key LIKE '%.gif'
	OR request_key LIKE '%.png' 
ORDER BY time_stamp DESC"

do shell script "sqlite3 ~/Library/Caches/com.apple.Safari/Cache.db " & quoted form of theQuery
#9

Hi everybody,

Been pretty busy lately. Thanks for the confirmation on the safety. The explanation of how sqlite3 works is exactly what I was wondering about. Learned a lot from everyone. So far I’ve had empty strings returned, but I think I ran it after closing Safari and by habit I always clear the caches with the Developer menu.

One more day of hard labor tomorrow. I’ll be looking into everything deeper.

Thanks a lot,
kel