Commands tagged sqlite3 (6)

  • Get your Firefox history

    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 9
  • sqlite3 cmd to extract Firefox bookmarks from places.sqlite

    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 9
  • Dump android contacts, sms

    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 21
  • Get your Firefox bookmarks

    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 9
  • Find out the Firefox bookmarks that have "string" in their note (aka 'description')

    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 13
  • Optimize Google Chrome database speedup sqlite3

    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 11

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

Find and copy scattered mp3 files into one directory
No problem with word splitting. That should works on many Unix likes.

Stop Flash from tracking everything you do.
Brute force way to block all LSO cookies on a Linux system with the non-free Flash browser plugin. Works just fine for my needs. Enjoy.

Enter parameter if empty (script becomes interactive when parameters are missing)
Can be used for command line parameters too. If you have a more complicated way of entering values (validation, GUI, ...), then write a function i.e. EnterValue() that echoes the value and then you can write: $ param=${param:-$(EnterValue)}

Change user within ssh session retaining the current MIT cookie for X-forwarding
When you remotely log in like "ssh -X userA:host" and become a different user with "su UserB", X-forwarding will not work anymore since /home/UserB/.Xauthority does not exist. This will use UserA's information stored in .Xauthority for UserB to enable X-forwarding. Watch http://prefetch.net/blog/index.php/2008/04/05/respect-my-xauthority/ for details.

Cut out a piece of film from a file. Choose an arbitrary length and starting time.
With: -vcodec, you choose what video codec the new file should be encoded with. Run ffmpeg -formats E to list all available video and audio encoders and file formats. copy, you choose the video encoder that just copies the file. -acodec, you choose what audio codec the new file should be encoded with. copy, you choose the audio encoder that just copies the file. -i originalfile, you provide the filename of the original file to ffmpeg -ss 00:01:30, you choose the starting time on the original file in this case 1 min and 30 seconds into the film -t 0:0:20, you choose the length of the new film newfile, you choose the name of the file created. Here is more information of how to use ffmpeg: http://www.ffmpeg.org/ffmpeg-doc.html

Validating a file with checksum
Makes sure the contents of "myfile" are the same contents that the author intended given the author's md5 hash of that file ("c84fa6b830e38ee8a551df61172d53d7").

find files ignoring .svn and its decendents

Converts multiple youtube links to mp3 files
Usage: ytmp3 "YTurl" "YTurl2" "YTurl3" "YTurlN" Uses the shift command to let you extract the .mp3 from as many youtube urls as you like (or wherever else youtube-dl is supported) *Requires youtube-dl Orginal chunk of code: youtube-dl -q -t --extract-audio --audio-format mp3 URL taken from here http://www.commandlinefu.com/commands/view/9701/convert-youtube-videos-to-mp3

list files recursively by size

Run a script in parrallel over ssh
Runs a local script over ssh assuming ssh keys are in place. -P argument prints results to stdout. # Uses - https://code.google.com/p/parallel-ssh/

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: