Neutralizing SQL Injection in PostgreSQL

  • View
    882

  • Download
    0

Embed Size (px)

Text of Neutralizing SQL Injection in PostgreSQL

  • Juliano Atanazio

    Neutralizando SQL Injection no PostgreSQLNeutralizando SQL Injection no PostgreSQL

    Neutralizing SQL Injection in PostgreSQLNeutralizing SQL Injection in PostgreSQL

  • 2/59

    About meJuliano Atanazio

    Graduated in Computer Science for Business Management (Informtica para Gesto de Negcios), FATEC Zona Sul, So Paulo SP;

    PostgreSQL DBA;

    Linux admin;

    Instructor (PostgreSQL);

    LPIC-1, LPIC-2 Certified;

    Linux user since 2000;

    Free Software enthusiast;

    Favorite technologies: PostgreSQL, Linux, Python, Shell Script, FreeBSD, etc...;

    Headbanger :) \m/

  • 3/59

    SQL InjectionDefinition

    SQL Injection is a method to introducing malicious SQL code to get unauthorized access or even damage a system.

    Definio

    SQL Injection um mtodo para introduzir cdigo SQL maligno para obter acesso indevido ou mesmo danificar um sistema.

  • 4/59

    SQL Injection: Practice

    $DBHOST enviroment variable to database server address:

    Varivel de ambiente $DBHOST para o endereo do servidor de banco de dados:

    $ read -p 'Type the database host address: ' DBHOST

    Type the database host address:

    Type the server address.

    Digite o endereo do servidor.

  • 5/59

    SQL Injection: Practice

    Database user with encrypted stored password, login permission, no superuser:

    Usurio de banco de dados com senha armazenada criptografada, permisso de login, no superuser:

    $ psql -U postgres -h ${DBHOST} -c \"CREATE ROLE u_sql_injection \ENCRYPTED PASSWORD 'secret' LOGIN NOSUPERUSER;"

  • 6/59

    SQL Injection: PracticeDatabase creation "db_sql_injection" with user "u_sql_injection" as owner:

    Criao de banco de dados "db_sql_injection" com o usurio "u_sql_injection" como proprietrio:

    $ psql -U postgres -h ${DBHOST} -c \"CREATE DATABASE db_sql_injection OWNER u_sql_injection;"

  • 7/59

    SQL Injection: PracticeAccessing the database via psql:

    Acessando a base de dados via psql: $ psql -U u_sql_injection db_sql_injection -h ${DBHOST}

  • 8/59

    SQL Injection: PracticeUser table creation for the application (without hashing):

    Criao de tabela de usurios para a aplicao (sem hashing):

    > CREATE TABLE tb_user( username varchar(50) PRIMARY KEY, -- natural primary key password VARCHAR(72) NOT NULL);

    Inserting a application user in the table:

    Inserindo um usurio do aplicativo na tabela:

    > INSERT INTO tb_user (username, password) VALUES ('foo', 'mypassword');

  • 9/59

    SQL Injection: PracticeScript (1):__________ sql_injection_1.py ___________________________

    #_*_ encoding: utf-8 _*_

    import getpass

    user = input('User: ')password = getpass.getpass('Password: ')

    sql = """SELECT TRUE FROM tb_userWHERE username = '{}'AND password = '{}';""".format(user, password)

    print('\n{}'.format(sql))____________________________________________________

  • 10/59

    SQL Injection: PracticeA simple test:

    Um teste simples:

    $ python3 sql_injection_1.py

    User: fooPassword:

    SELECT TRUE FROM tb_userWHERE username = 'foo'AND password = 'mypassword';

  • 11/59

    SQL Injection: About the ScriptThe script is pretty simple, does not yet have any interaction with the database, but it serves to illustrate.

    O script bem simples, ainda no possui qualquer interao com o banco de dados, mas serve para ilustrar.

  • 12/59

    SQL Injection: PracticeScript (2):__________ sql_injection_2.py ___________________________

    # _*_ encoding: utf-8 _*_

    import getpassimport psycopg2import sys# DB server as first argumentdbhost = sys.argv[1]# Connection stringconn_string = """ host='{}' dbname='db_sql_injection' user='u_sql_injection' password='secret' port='5432' """.format(dbhost)

  • 13/59

    SQL Injection: PracticeScript (2):__________ sql_injection_2.py ___________________________

    try: # Connection conn = psycopg2.connect(conn_string) # Cursor creation to execute SQL commands cursor = conn.cursor() # User input user = input('User: ')

    # Password input password = getpass.getpass('Password: ')

  • 14/59

    SQL Injection: PracticeScript (2):__________ sql_injection_2.py ___________________________

    # SQL string sql = """ SELECT TRUE FROM tb_user \ WHERE username = '{}' \ AND password = '{}'; """.format(user, password)

    # Print the sql string after user and password input print('{}\n'.format(sql))

    # Execute the SQL string in database cursor.execute(sql) # The result of the string SQL execution res = cursor.fetchone()

  • 15/59

    SQL Injection: PracticeScript (2):__________ sql_injection_2.py ___________________________

    # User login validation if res: print('\nAcessed!') else: print('\nError: Invalid user and password combination!') sys.exit(1)except psycopg2.Error as e: print('\nAn error has occurred!\n{}'.format(e))# Close the database connectionconn.close()

    ____________________________________________________

  • 16/59

    SQL Injection: PracticeA simple test access with correct password:

    Um teste simples de acesso com senha correta:

    $ python3 sql_injection_2.py ${DBHOST}

    User: fooPassword:

    SELECT TRUE FROM tb_userWHERE username = 'foo' AND password = 'mypassword';

    Acessed!

  • 17/59

    SQL Injection: PracticeA simple test access with wrong password:

    Um teste simples de acesso com senha errada:

    $ python3 sql_injection_2.py ${DBHOST}

    User: fooPassword:

    SELECT TRUE FROM tb_userWHERE username = 'foo'AND password = '123';

    Error: Invalid user and password combination!

  • 18/59

    SQL Injection: PracticeMalicious code at user login input:

    Cdigo malicioso na entrada de login de usurio:

    $ python3 sql_injection_2.py ${DBHOST}

    User: ' OR 1 = 1; DROP TABLE tb_user; --Password:

    SELECT TRUE FROM tb_userWHERE username = '' OR 1 = 1; DROP TABLE tb_user; -'AND password = '';

    An error has occurred!no results to fetch

    Does the table has been deleted?

    Ser que a tabela foi apagada?

  • 19/59

    SQL Injection: PracticeChecking the table in the database:

    Verificando a tabela na base de dados:

    > SELECT TRUE FROM tb_user;

    bool ------ t

    Everithing is OK... for a while...No commit...

    Est tudo OK... por enquanto...Sem efetivao...

  • 20/59

    SQL Injection: PracticeMalicious code at user login input (with COMMIT):

    Cdigo malicioso na entrada de login de usurio (com COMMIT):

    $ python3 sql_injection.py

    User: ' OR 1 = 1; DROP TABLE tb_user; COMMIT; --Password:

    SELECT TRUE FROM tb_user WHERE username = '' OR 1 = 1; DROP TABLE tb_user; COMMIT; -'AND password = '';

    An error has occurred!no results to fetch

  • 21/59

    SQL Injection: PracticeChecking the table in the database:

    Verificando a tabela na base de dados:

    > SELECT TRUE FROM tb_user;

    ERROR: relation "tb_user" does not existLINE 1: SELECT id FROM tb_user; ^

    The table was dropped and must be created with the data again.

    A tabela foi apagada e ter que ser criada com os dados novamente.

    :(

  • 22/59

    Dollar QuotingIt consists of a dollar sign ($), an optional tag of zero or more characters, another dollar sign, an arbitrary sequence of characters that makes up the string content, a dollar sign, the same tag that began this dollar quote, and a dollar sign. For example, here are two different ways to specify the string Dianne's horse using dollar quoting:

    Consiste de um caractere de dlar, uma tag opcional de zero ou mais caracteres, outro caractere de dlar, uma sequncia arbitrria de caracteres que o contedo da string, um caractere de dlar, a mesma tag que comeou o dollar quoting e um caractere de dlar. Por exemplo, h duas maneiras diferentes de especificar a string Dianne's horse usando dollar quoting:

    $$Dianne's horse$$$SomeTag$Dianne's horse$SomeTag$

  • 23/59

    Dollar QuotingDollar quoting is also a very nice feature to avoid SQL injection, particularly when the application generates a random tag.This tag must start with either a letter or with an underscore, the rest can have underscore, letters or numbers.

    Dollar quoting tambm um recurso muito interessante para se evitar SQL injection, principalmente quando a aplicao gera uma tag aleatria.Essa tag deve comear ou com uma letra ou com underscore, o resto pode ter underscore, letras ou nmeros.

  • 24/59

    Dollar Quoting: PracticeScript (3):__________ sql_injection_3.py ___________________________

    # _*_ encoding: utf-8 _*_

    import getpassimport psycopg2import sys

    # DB server as first argumentdbhost = sys.argv[1]

    # Connection stringconn_string = """ host='{}' dbname='db_sql_injection' user='u_sql_injection' password='secret' port='5432' """.format(dbhost)

  • 25/59

    Dollar Quoting: PracticeScript (3):__________ sql_injection_3.py ___________________________

    try: # Connection conn = psycopg2.connect(conn_string)

    # Cursor creation to execute SQL commands cursor = conn.cursor()

    # User input user = input('User: ')

    # Password input password = getpass.getpass('Password: ')

  • 26/59

    Dollar Quoting: PracticeScript (3):__________ sql_injection_3.py ___________________________

    # SQL string sq