Sqlite Intro for Complete Beginners and others

Repository

e.g. https://github.com/timogoosen/dirsearchreporter

What Will I Learn?

This tutorial will be covering how to use SQLite and will be a brief intro to SQLite, why and when to use it.
Then above mentioned repo contains some examples of using python3+ sqlite. Just take in mind that the code is not
perfect or production ready, this code is just to teach you by using some examples.

• You will learn basics of installing sqlite and how to use the SQLite shell.
• You will learn about turning on foreignkey support in Sqlite.
• You will get a brief interaction to interacting with sqlite using Python3.
• You will get a very brief overview of what SQLInjection is, how to avoid it and how it works and for those interested I will give an extensive
  reading list on SQLInjection for anyone who is possibly interested in bug bounty hunting for security bugs on sites like hackerone.com or bugcrowd.com.

Requirements

State the requirements the user needs in order to follow this tutorial.

• We assume you are on either BSD,Linux or a Mac.
• We assume basic knowledge of relational databases.
• We assume a basic knowledge of SQL, scripting and basic programming in Python.

Difficulty

Choose one of the following options:

• Intermediate: This guide is on intermediate level, if you struggle to understand it then I would highly suggest that you take the following coursera
  course which is on python + Databases with a focus on SQLite specifically. This is a great course and I would highly reccomend that you complete this course:
  https://www.coursera.org/learn/python-databases . I've done quite a few coursera and Udemy courses and out of all the courses I've done I would highly reccomend this specific course,
  especially for anyone who is aspiring to become a backend developer. If you are more of a reading person and you don't enjoy following video tutorials, then
  I would suggest that you have a look at this excellent courser material by Zed Shaw:
  https://learncodethehardway.org/sql/

Tutorial Contents

A full description of the topics covered in this tutorial, plus the contents of the tutorial itself.

Description:

Article:

First of all let us cover how to install sqlite on your machine, perhaps your machine already has SQLite installed. You can check whether or not it has been installed with:

$ which sqlite3

If you don't have it installed then do the following:

Ubuntu:

$ sudo apt-get update && sudo apt-get install sqlite3

On OSX:

$ brew install sqlite

On FreeBSD:

$ sudo pkg install sqlite3-3.22.0_2

You might need to install this to get Python3.6 interacting with sqlite3 on FreeBSD:

$ sudo pkg install databases/py36-sqlite3

Now that we have sqlite installed we can start getting to work. Before we get to using sqlite, let's see where SQLite is used and what is it used for?

• Your browser uses sqlite for local storage in Javascript.
• On both Android and IOS local storage is used for different purposes, sqlite is used for local storage.
• Sqlite is often used in Django for development purposes for Dev's who are too lazy to configure Postgreql or MariaDB.
• If you've ever wanted to get into digital forensics or mobile phone app hacking, then you should seriously consider learning SQlite inside out.
  People really have a great deal to say about use cases for sqlite and which of these use cases are "good" use cases, here is some reading material if this topic
  interests you:
  https://www.sqlite.org/whentouse.html
  https://news.ycombinator.com/item?id=12578028
  https://news.ycombinator.com/item?id=16356942

Now we know where SQLite is actively used, let's just have a brief overview of the very interesting history behind SQLite:

Here is the citation for that piece of information from the sqlite wikipedia entry:
https://en.wikipedia.org/wiki/SQLite#cite_note-Owens06-12

We are going to go over very basics of sqlite, first we will go over using the CLI and then once we've covered that we will go into some
more in depth examples. First let's open sqlite3 in , in-memory mode:

$ sqlite3

What this means is that once we close the sqlite3, then nothing will be written to the hard disk, since we didn't specify a file
to safe the database to, so in other words everything was just happening in memory, but no file was created. So how do you create a database where
the changes won't only be in memory, but would also be written to disk?

$ sqlite3 /directory/to/save/to/dbname.db

So for example:(You might test want to save your db to something less temporary than /tmp)

$  sqlite3 /tmp/dbname.db

Now we are gonna create a table and have a look at the schema of the table:

sqlite> CREATE TABLE presidents (id integer, surname varchar(60), name varchar(60), age integer, country varchar(60));

Now you can view the schema with:

sqlite> .schema

Insert some data into our tables:

sqlite> INSERT INTO presidents VALUES (1, "Trump", "Donald", 65,"US and A");

Turning on foreign key support:

sqlite> PRAGMA foreign_keys = ON;

This is just something I've figured out from using SQLite, I'm not going into detail, but you can try that out.

Some tooling to look at:

• https://sqlitebrowser.org
• https://sqlitestudio.pl/index.rvt

SQL Injection reading list:

• http://www.infosectoughguy.com/2017/06/how-to-learn-sql-injection.html
• https://steemit.com/infosec/@netscape101/how-to-learn-sql-injection

Curriculum

I don't have any utopian-io submissions yet, this is my first submission so please follow me if you enjoyed this submission.
I really enjoy technical writing and you can expect many more articles like this one.

Proof of Work Done

Here is some of the code used in the tutorial:
https://github.com/timogoosen/dirsearchreporter/blob/master/sqlitelogger.py

Thanks for reading. Please follow me if you found this interesting.