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 6
  • 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

Remove security limitations from PDF documents using ghostscript (for Windows)
#4345 also works under windows

Remove a file whose name begins with a dash ( - ) character
Using the redundant ./ directory information prevents the dash from occurring at the beginning of the filename, and being interpreted as an option of the rm command. Also works using: $ rm -- -filename

Find broken symlinks
To understand why this is the equivalent of "find -L /path/to/search -type l, see http://ynform.org/w/Pub/FindBrokenSymbolicLinks or look at http://www.gnu.org/software/findutils/manual/html_mono/find.html

Run TOP in Color, split 4 ways for x seconds - the ultimate ps command. Great for init scripts
One of my favorite ways to impress newbies (and old hats) to the power of the shell, is to give them an incredibly colorful and amazing version of the top command that runs once upon login, just like running fortune on login. It's pretty sweet believe me, just add this one-liner to your ~/.bash_profile -- and of course you can set the height to be anything, from 1 line to 1000! $ G=$(stty -g);stty rows $((${LINES:-50}/2));top -n1; stty $G;unset G Doesn't take more than the below toprc file I've added below, and you get all 4 top windows showing output at the same time.. each with a different color scheme, and each showing different info. Each window would normally take up 1/4th of your screen when run like that - TOP is designed as a full screen program. But here's where you might learn something new today on this great site.. By using the stty command to change the terminals internal understanding of the size of your terminal window, you force top to also think that way as well. # save the correct settings to G var. $ G=$(stty -g) # change the number of rows to half the actual amount, or 50 otherwise $ stty rows $((${LINES:-50}/2)) # run top non-interactively for 1 second, the output stays on the screen (half at least) $ top -n1 # reset the terminal back to the correct values, and clean up after yourself $ stty $G;unset G This trick from my [ http://www.askapache.com/linux-unix/bash_profile-functions-advanced-shell.html bash_profile ], though the online version will be updated soon. Just think what else you could run like this! Note 1: I had to edit the toprc file out due to this site can't handle that (uploads/including code). So you can grab it from [ http://www.askapache.com/linux-unix/bash-power-prompt.html my site ] Note 2: I had to come back and edit again because the links weren't being correctly parsed

Function that outputs dots every second until command completes
Very useful in shell scripts because you can run a task nicely in the background using job-control and output progress until it completes. Here's an example of how I use it in backup scripts to run gpg in the background to encrypt an archive file (which I create in this same way). $! is the process ID of the last run command, which is saved here as the variable PI, then sleeper is called with the process id of the gpg task (PI), and sleeper is also specified to output : instead of the default . every 3 seconds instead of the default 1. So a shorter version would be sleeper $!; The wait is also used here, though it may not be needed on your system. $ echo ">>> ENCRYPTING SQL BACKUP" $ gpg --output archive.tgz.asc --encrypt archive.tgz 1>/dev/null & $ PI=$!; sleeper $PI ":" 3; wait $PI && rm archive.tgz &>/dev/null Previously to get around the $! not always being available, I would instead check for the existance of the process ID by checking if the directory /proc/$PID existed, but not everyone uses proc anymore. That version is currently the one at http://www.askapache.com/linux-unix/bash_profile-functions-advanced-shell.html but I plan on upgrading to this new version soon.

Play 89.3 @TheCurrent and get system notifications on song changes.
Plays the mp3 stream of The Current as a background job. When you are done run: $ fg %1 then to exit Quite possible with Growl for mac I'd guess, although have not tried. Libnotify needed for notification, stream will still work otherwise

Collect a lot of icons from /usr/share/icons (may overwrite some, and complain a bit)
In other way of xargs, only with find -exec

Updated top ten memory utilizing processes (child/instance aggregation) now with percentages of total RAM
Prints the top 10 memory consuming processes (with children and instances aggregated) sorted by total RSS and calculates the percentage of total RAM each uses. Please note that since RSS can include shared libraries it is possible for the percentages to add up to more that the total amount of RAM, but this still gives you a pretty good idea. Also note that this does not work with the mawk version of awk, but it works fine with GNU Awk which is on most Linux systems. It also does not work on OS X.

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"

Changes a User Password via command line without promt
Used to change a password via a winscp faux shell


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: