SQL, Python
& security issues

But before that:

-- here's how we can put comments in our SQL queries

SELECT id, name, password -- we're selecting all the passwords from the database 
  FROM users
  WHERE age > 20; -- for the users older that 20

We'll see why is this important later...

SQL Transactions

BEGIN TRANSACTION;

...queries

COMMIT; / ROLLBACK;

Why ? 🤷

How ? 🤔

DB Connection

A socket connection between your program and the DBMS

DB Cursor

 

https://en.wikipedia.org/wiki/Cursor_%28databases%29

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

# Create table
cursor.execute(
    '''
    CREATE TABLE stocks
         (date text, trans text, symbol text, qty real, price real);
    '''
)

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14);")

# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

Docs - https://docs.python.org/3.6/library/sqlite3.html

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

# Create table
cursor.execute(
    '''
    CREATE TABLE stocks
         (date text, trans text, symbol text, qty real, price real);
    '''
)

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14);")

# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

Docs - https://docs.python.org/3.6/library/sqlite3.html

Docs - https://docs.python.org/3.6/library/sqlite3.html

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

# Create table
cursor.execute(
    '''
    CREATE TABLE stocks
         (date text, trans text, symbol text, qty real, price real);
    '''
)

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14);")

# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

Docs - https://docs.python.org/3.6/library/sqlite3.html

Docs - https://docs.python.org/3.6/library/sqlite3.html

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

# Create table
cursor.execute(
    '''
    CREATE TABLE stocks
         (date text, trans text, symbol text, qty real, price real);
    '''
)

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14);")

# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

Docs - https://docs.python.org/3.6/library/sqlite3.html

Docs - https://docs.python.org/3.6/library/sqlite3.html

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

# Create table
cursor.execute(
    '''
    CREATE TABLE stocks
         (date text, trans text, symbol text, qty real, price real);
    '''
)

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14);")

# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

Docs - https://docs.python.org/3.6/library/sqlite3.html

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

# Create table
cursor.execute(
    '''
    CREATE TABLE stocks
         (date text, trans text, symbol text, qty real, price real);
    '''
)

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14);")

# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

Docs - https://docs.python.org/3.6/library/sqlite3.html

Docs - https://docs.python.org/3.6/library/sqlite3.html

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

# Create table
cursor.execute(
    '''
    CREATE TABLE stocks
         (date text, trans text, symbol text, qty real, price real);
    '''
)

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14);")

# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

Docs - https://docs.python.org/3.6/library/sqlite3.html

Fetch Data

import sqlite3
connection = sqlite3.connect('example.db')

cursor = connection.cursor()

cursor.execute(
    """
    CREATE TABLE users
        (id integer primary key autoincrement, full_name varchar(100))
    """
)

cursor.execute(
    """
    INSERT INTO users VALUES (1, 'ivo');
    """
)

cursor.execute("SELECT id, full_name FROM users;")
users_data = cursor.fetchall()


# Save (commit) the changes
connection.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
connection.close()

DEMO

Security

Backups!!

SQL injection

some_var = input()

update_sql = "SELECT * WHERE username = " + some_var
cursor.executescript(update_sql)

Must watch - https://youtu.be/_jKylhJtPmI

Users passwords !

Hashing

Algorithms:

 

- MD5

- SHA128

- SHA256

- ...

 

Must watch:

https://www.youtube.com/watch?v=b4b8ktEV4Bg

 

https://www.youtube.com/watch?v=DMtFhACPnTY

 

 

 

Even better -"salt" hashing

Must watch - https://youtu.be/8ZtInClXe1Q

Tasks!

Python 101 9th SQL & Python + Security

By Hack Bulgaria

Made with Slides.com

Python 101 9th SQL & Python + Security

  • 1,025

More from Hack Bulgaria