Commands tagged sqlite3 (6)

  • This is the way to get access to your Firefox history...


    8
    sqlite3 ~/.mozilla/firefox/*.[dD]efault/places.sqlite "SELECT strftime('%d.%m.%Y %H:%M:%S', visit_date/1000000, 'unixepoch', 'localtime'),url FROM moz_places, moz_historyvisits WHERE moz_places.id = moz_historyvisits.place_id ORDER BY visit_date;"
    return13 · 2015-02-24 21:51:14 1
  • Found this useful query at http://id.motd.org/pivot/entry.php?id=22. The b.parent=2 in the command refers to the bookmarks folder to extract. See the source webpage for additional info.


    5
    sqlite3 ~/.mozilla/firefox/*default/places.sqlite "select a.url, a.title from moz_places a, moz_bookmarks b where a.id=b.fk and b.parent=2;"
    jrdbz · 2013-04-12 17:41:34 1
  • Crude, but works. Note for security, /data/ will be inaccessible unless your device has been *rooted*. On the other hand, if a device has been rooted, its data is now wide open to anyone with a USB cable and the above "one-liner". `adb` is one of the platform tools in the android SDK. To get SMS messages: adb pull /data/data/com.android.providers.telephony/databases/mmssms.db ; sqlite3 -batch <<EOF contacts2.db <CR> .header on <CR> .mode tabs <CR> select * from sms; <CR> EOF


    2
    adb pull /data/data/com.android.providers.contacts/databases/contacts2.db ; sqlite3 -batch <<EOF contacts2.db <CR> .header on <CR> .mode tabs <CR> select * from data; <CR> EOF
    mhs · 2012-12-26 11:35:23 5
  • Extracts yours bookmarks out of sqlite with the format: dateAdded|url Show Sample Output


    2
    sqlite3 ~/.mozilla/firefox/*.[dD]efault/places.sqlite "SELECT strftime('%d.%m.%Y %H:%M:%S', dateAdded/1000000, 'unixepoch', 'localtime'),url FROM moz_places, moz_bookmarks WHERE moz_places.id = moz_bookmarks.fk ORDER BY dateAdded;"
    return13 · 2015-03-08 19:26:16 1
  • 1. First we get the `item_id` for that `comment`. Adapt the -C[N] parameter for your use. 2. Then we show the bookmark's `title` (or `url`). With that in your hand it's a matter of seconds to open Firefox's library and find the bookmark. Handy for eg. forensics or better sanitize of a place.sqlite before sharing it (on the cloud). It sure has room for improvement. Show Sample Output


    0
    sqlite3 -list places.sqlite 'SELECT item_id, content FROM moz_items_annos ;' | grep -A9 "string" ; sqlite3 places.sqlite 'SELECT title FROM moz_bookmarks WHERE .fk = <item_id number> ;'
    datruche · 2015-10-31 19:32:52 2
  • Speed up Google Chrome like speedyfox does for MAC USER. This script will detect all SQLITE DATABASE on the Google directory


    0
    cd "/Users/$USER/Library/Application Support/Google" && find . -print|while read line;do (file "$line"|grep SQLite) && (sqlite3 "$line" "VACUUM;";echo "Compress");done;
    c0derz · 2016-04-03 13:46:40 0

What's this?

commandlinefu.com is the place to record those command-line gems that you return to again and again. That way others can gain from your CLI wisdom and you from theirs too. All commands can be commented on, discussed and voted up or down.

Share Your Commands


Check These Out

Re-read partition table on specified device without rebooting system (here /dev/sda).

Ask user to confirm
Returns true if user presses the key. Use it like $ Confirm "Continue" && do action

copy file to clipboard
Loads file content on clipboard. Very useful when text selection size is higher than console size.

Get AWS temporary credentials ready to export based on a MFA virtual appliance
You might want to secure your AWS operations requiring to use a MFA token. But then to use API or tools, you need to pass credentials generated with a MFA token. This commands asks you for the MFA code and retrieves these credentials using AWS Cli. To print the exports, you can use: `awk '{ print "export AWS_ACCESS_KEY_ID=\"" $1 "\"\n" "export AWS_SECRET_ACCESS_KEY=\"" $2 "\"\n" "export AWS_SESSION_TOKEN=\"" $3 "\"" }'` You must adapt the command line to include: * $MFA_IDis ARN of the virtual MFA or serial number of the physical one * TTL for the credentials

Find files and calculate size of result in shell
Using find's internal stat to get the file size is about 50 times faster than using -exec stat.

Get AWS temporary credentials ready to export based on a MFA virtual appliance
You might want to secure your AWS operations requiring to use a MFA token. But then to use API or tools, you need to pass credentials generated with a MFA token. This commands asks you for the MFA code and retrieves these credentials using AWS Cli. To print the exports, you can use: `awk '{ print "export AWS_ACCESS_KEY_ID=\"" $1 "\"\n" "export AWS_SECRET_ACCESS_KEY=\"" $2 "\"\n" "export AWS_SESSION_TOKEN=\"" $3 "\"" }'` You must adapt the command line to include: * $MFA_IDis ARN of the virtual MFA or serial number of the physical one * TTL for the credentials

Move all files between to date
In a folder with many files and folders, you want to move all files where the date is >= the file olderFilesNameToMove and

Which processes are listening on a specific port (e.g. port 80)
swap out "80" for your port of interest. Can use port number or named ports e.g. "http"

get time in other timezones
On Ubuntu, if tzwatch is installed, then you can call up in terminal the output for every time zone configured in gWorldClock.

View an info page on a nice interface
Nice interface for an info page.


Stay in the loop…

Follow the Tweets.

Every new command is wrapped in a tweet and posted to Twitter. Following the stream is a great way of staying abreast of the latest commands. For the more discerning, there are Twitter accounts for commands that get a minimum of 3 and 10 votes - that way only the great commands get tweeted.

» http://twitter.com/commandlinefu
» http://twitter.com/commandlinefu3
» http://twitter.com/commandlinefu10

Subscribe to the feeds.

Use your favourite RSS aggregator to stay in touch with the latest commands. There are feeds mirroring the 3 Twitter streams as well as for virtually every other subset (users, tags, functions,…):

Subscribe to the feed for: