PostgreSQL: SQL Injection (How to Avoid) & Hashing User Password

This presentation aims to teach the concept of SQL Injection and illustrate in practical examples how such an attack can damage a system.
At the end there is a demonstration of how to securely store user passwords in database, following good practices.
Examples in Python

PostgreSQL: SQL Injection (Como Evitar) & Hashing de Senha de Usuário

Esta apresentação objetiva ensinar o conceito de SQL Injection, bem como ilustrar em exemplos práticos como um ataque desse tipo pode danificar um sistema.
No final há uma demonstração sobre como armazenar senhas de usuários na base de dados de forma segura, seguindo boas práticas.
Exemplos em Python.

Transcript

1. Juliano Atanazio PostgreSQL: PostgreSQL: SQL Injection (Como Evitar) SQL Injection (Como Evitar) & Hashing de Senha de Usuário & Hashing de Senha de Usuário PostgreSQL: PostgreSQL: SQL Injection (How to Avoid) SQL Injection (How to Avoid) & Hashing User Password & Hashing User Password
2. 2/77 About me Juliano Atanazio • Graduated in Computer Science for Business Management (Informática para Gestão de Negócios), FATEC Zona Sul, São 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. 3/77 SQL Injection Definition SQL Injection is a method to introducing malicious SQL code to get unauthorized access or even damage a system. Definição SQL Injection é um método para introduzir código SQL maligno para obter acesso indevido ou mesmo danificar um sistema.
4. 4/77 SQL Injection: Practice $DBHOST enviroment variable to database server address: Variável de ambiente $DBHOST para o endereço 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 endereço do servidor.
5. 5/77 SQL Injection: Practice Database user with encrypted stored password, login permission, no superuser: Usuário de banco de dados com senha armazenada criptografada, permissão de login, não superuser: $ psql -U postgres -h ${DBHOST} -c \ "CREATE ROLE u_sql_injection \ ENCRYPTED PASSWORD 'secret' LOGIN NOSUPERUSER;"
6. 6/77 SQL Injection: Practice Database creation "db_sql_injection" with user "u_sql_injection" as owner: Criação de banco de dados "db_sql_injection" com o usuário "u_sql_injection" como proprietário: $ psql -U postgres -h ${DBHOST} -c \ "CREATE DATABASE db_sql_injection OWNER u_sql_injection;" Enable the pgcrypto extension in the db_sql_injection database: Habilitar a extensão pgcrypto no banco de dados db_sql_injection: $ psql -U postgres -h ${DBHOST} -c \ "CREATE EXTENSION pgcrypto;" db_sql_injection
7. 7/77 SQL Injection: Practice Accessing the database via psql: Acessando a base de dados via psql: $ psql -U u_sql_injection db_sql_injection -h ${DBHOST}
8. 8/77 SQL Injection: Practice Create a prepared statement: Criar um prepared statement: > PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = $2; Execute a prepared statement: Executar um prepared statement: > EXECUTE q_user('foo', 'mypassword'); bool ------ t
9. 9/77 SQL Injection: Practice User table creation for the application: Criação de tabela de usuários para a aplicação: > 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 usuário do aplicativo na tabela: > INSERT INTO tb_user (username, password) VALUES ('foo', 'mypassword');
10. 10/77 SQL Injection: Practice Script (1): __________ sql_injection_1.py ___________________________ #!/usr/bin/env python3 #_*_ encoding: utf-8 _*_ import getpass user = input('User: ') password = getpass.getpass('Password: ') sql = """ SELECT TRUE FROM tb_user WHERE username = '{}' AND password = '{}'; """.format(user, password) print(sql) ____________________________________________________
11. 11/77 SQL Injection: Practice A simple test: Um teste simples: $ ./sql_injection_1.py User: foo Password: SELECT TRUE FROM tb_user WHERE username = 'foo' AND password = 'mypassword';
12. 12/77 SQL Injection: About the Script The script is pretty simple, does not yet have any interaction with the database, but it serves to illustrate. Note that the second command, at the user request login prompt, the malicious SQL code was injected. O script é bem simples, ainda não possui qualquer interação com o banco de dados, mas serve para ilustrar. Nota-se que no segundo comando, no prompt de solicitação de login usuário foi onde o código SQL malicioso foi injetado.
13. 13/77 SQL Injection: Practice Script (2): __________ sql_injection_2.py ___________________________ #!/usr/bin/env python3 # _*_ encoding: utf-8 _*_ import getpass import psycopg2 import sys # DB server as first argument dbhost = sys.argv[1] # Connection string conn_string = """ host='{}' dbname='db_sql_injection' user='u_sql_injection' password='secret' port='5432' """.format(dbhost) →
14. 14/77 SQL Injection: Practice Script (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: ') →
15. 15/77 SQL Injection: Practice Script (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(sql) # Execute the SQL string in database cursor.execute(sql) # The result of the string SQL execution res = cursor.fetchone() →
16. 16/77 SQL Injection: Practice Script (2): __________ sql_injection_2.py ___________________________ # User login validation if res: print('Acessed!') else: print('Error: Invalid user and password combination!') sys.exit(1) except psycopg2.Error as e: print('\nAn error has occurred!\n%s' % (e)) # Close the database connection conn.close() ____________________________________________________
17. 17/77 SQL Injection: Practice A simple test access with correct password: Um teste simples de acesso com senha correta: $ ./sql_injection_2.py ${DBHOST} User: foo Password: SELECT TRUE FROM tb_user WHERE username = 'foo' AND password = 'mypassword'; Acessed!
18. 18/77 SQL Injection: Practice A simple test access with wrong password: Um teste simples de acesso com senha errada: $ ./sql_injection_2.py ${DBHOST} User: foo Password: 123 SELECT TRUE FROM tb_user WHERE username = 'foo' AND password = '123'; Error: Invalid user and password combination!
19. 19/77 SQL Injection: Practice Malicious code at user login input: Código malicioso na entrada de login de usuário: $ ./sql_injection_2.py ${DBHOST} User: ' OR 1 = 1; DROP TABLE tb_user; -- Password: SELECT TRUE FROM tb_user WHERE 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?
20. 20/77 SQL Injection: Practice Checking 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 efetivação...
21. 21/77 SQL Injection: Practice Malicious code at user login input (with COMMIT): Código malicioso na entrada de login de usuário (com COMMIT): $ ./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
22. 22/77 SQL Injection: Practice Checking the table in the database: Verificando a tabela na base de dados: > SELECT TRUE FROM tb_user; ERROR: relation "tb_user" does not exist LINE 1: SELECT id FROM tb_user; ^ The table was dropped. A tabela foi apagada.
23. 23/77 SQL Injection: Practice Create table and insert user again, then query the table: Criar tabela e inserir usuário novamente, em seguida, consultar a tabela: > CREATE TABLE tb_user( username varchar(50) PRIMARY KEY, -- natural primary key password VARCHAR(72) NOT NULL); > INSERT INTO tb_user (username, password) VALUES ('foo', 'mypassword'); > SELECT TRUE FROM tb_user; bool ------ t
24. 24/77 Dollar Quoting It 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 dólar, uma “tag” opcional de zero ou mais caracteres, outro caractere de dólar, uma sequência arbitrária de caracteres que é o conteúdo da string, um caractere de dólar, a mesma tag que começou o dollar quoting e um caractere de dólar. Por exemplo, há duas maneiras diferentes de especificar a string “Dianne's horse” usando dollar quoting: $$Dianne's horse$$ $SomeTag$Dianne's horse$SomeTag$
25. 25/77 Dollar Quoting Dollar 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 and numbers. Dollar quoting também é um recurso muito interessante para se evitar SQL injection, principalmente quando a aplicação gera uma tag aleatória. Essa tag deve começar ou com uma letra ou com underscore, o resto pode ter underscore, letras e números.
26. 26/77 Dollar Quoting: Practice Script (3): __________ sql_injection_3.py ___________________________ #!/usr/bin/env python3 # _*_ encoding: utf-8 _*_ import getpass import psycopg2 import sys # DB server as first argument dbhost = sys.argv[1] # Connection string conn_string = """ host='{}' dbname='db_sql_injection' user='u_sql_injection' password='secret' port='5432' """.format(dbhost) →
27. 27/77 Dollar Quoting: Practice Script (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: ') →
28. 28/77 Dollar Quoting: Practice Script (3): __________ sql_injection_3.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(sql) # Execute the SQL string in database cursor.execute(sql) # The result of the string SQL execution res = cursor.fetchone() →
29. 29/77 Dollar Quoting: Practice Script (3): __________ sql_injection_3.py ___________________________ # User login validation if res: print('\nAcessed!\n') else: print('\nError: Invalid user and password combination!\n') sys.exit(1) except psycopg2.Error as e: print('\nAn error has occurred!\n%s' % (e)) # Close the database connection conn.close() ____________________________________________________
30. 30/77 Dollar Quoting: Practice Normal access: Acesso normal: $ ./sql_injection_3.py ${DBHOST} User: foo Password: SELECT TRUE FROM tb_user WHERE username = $$foo$$ AND password = $ $mypassword$$; Acessed!
31. 31/77 Dollar Quoting: Practice Attempted malicious code (with apostrophe): Tentativa de código malicioso (com apóstrofo): $ ./sql_injection_3.py ${DBHOST} 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 = $$$$; Error: Invalid user and password combination! Neutralized malicious code. Código malicioso neutralizado.
32. 32/77 Dollar Quoting: Practice Attempted malicious code (with double dollar sign): Tentativa de código malicioso (com dólar duplo): $ ./sql_injection_3.py ${DBHOST} 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
33. 33/77 Dollar Quoting: Practice Checking the table in the database: Verificando a tabela na base de dados: > SELECT TRUE FROM tb_user; ERROR: relation "tb_user" does not exist LINE 1: SELECT id FROM tb_user; ^ The table was dropped. A tabela foi apagada.
34. 34/77 Dollar Quoting: Practice Create table and insert user again, then query the table: Criar tabela e inserir usuário novamente, em seguida, consultar a tabela: > CREATE TABLE tb_user( username varchar(50) PRIMARY KEY, -- natural primary key password VARCHAR(72) NOT NULL); > INSERT INTO tb_user (username, password) VALUES ('foo', 'mypassword'); > SELECT TRUE FROM tb_user; bool ------ t
35. 35/77 Dollar Quoting: Practice Script (4): __________ sql_injection_4.py ___________________________ #!/usr/bin/env python3 # _*_ encoding: utf-8 _*_ import getpass import psycopg2 import sys import string import random # DB server as first argument dbhost = sys.argv[1] →
36. 36/77 Dollar Quoting: Practice Script (4): __________ sql_injection_4.py ___________________________ # Connection string conn_string = """ host='{}' dbname='db_sql_injection' user='u_sql_injection' password='secret' port='5432' """.format(dbhost) →
37. 37/77 Dollar Quoting: Practice Script (4): __________ sql_injection_4.py ___________________________ # Function: tag generator def tag_gen(size): first_char = '{}_'.format(string.ascii_letters) last_chars = '{}{}'.format(string.digits, first_char) tag = random.choice(first_char) for i in range(size - 1): tag = '{}{}'.format(tag, random.choice(last_chars)) return tag # Tag for dollar quoting tag = tag_gen(7) →
38. 38/77 Dollar Quoting: Practice Script (4): __________ sql_injection_4.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: ') →
39. 39/77 Dollar Quoting: Practice Script (4): __________ sql_injection_4.py ___________________________ # SQL string sql = "SELECT TRUE FROM tb_user \ WHERE username = ${}${}${}$ \ AND password = ${}${}${}$;".format(tag, user, tag, tag, password, tag) # Print the sql string after user and password input print(sql) # Execute the SQL string in database cursor.execute(sql) # The result of the string SQL execution res = cursor.fetchone() →
40. 40/77 Dollar Quoting: Practice Script (4): __________ sql_injection_4.py ___________________________ # User login validation if res: print('\nAcessed!\n') else: print('\nError: Invalid user and password combination!\n') sys.exit(1) except psycopg2.Error as e: print('\nAn error has occurred!\n%s' % (e)) # Close the database connection conn.close() ____________________________________________________
41. 41/77 Dollar Quoting: Practice A simple test access with correct password: Um teste simples de acesso com senha correta: $ ./sql_injection_4.py ${DBHOST} User: foo Password: SELECT TRUE FROM tb_user WHERE username = $PJPWqvS$foo$PJPWqvS$ AND password = $PJPWqvS$mypassword$PJPWqvS$; Acessed!
42. 42/77 Dollar Quoting: Practice Attempted malicious code (with apostrophe): Tentativa de código malicioso (com apóstrofo): $ ./sql_injection_4.py ${DBHOST} User: ' OR 1 = 1; DROP TABLE tb_user; COMMIT; -- Password: SELECT TRUE FROM tb_user WHERE username = $EbVRSoG$' OR 1 = 1; DROP TABLE tb_user; COMMIT; --$EbVRSoG$ AND password = $EbVRSoG$$EbVRSoG$; Error: Invalid user and password combination! Neutralized malicious code. Código malicioso neutralizado.
43. 43/77 Dollar Quoting: Practice Attempted malicious code (with double dollar sign): Tentativa de código malicioso (com dólar duplo): $ ./sql_injection_4.py ${DBHOST} User: $$ OR 1 = 1; DROP TABLE tb_user; COMMIT; -- Password: SELECT TRUE FROM tb_user WHERE username = $Re7Gqwb$$$ OR 1 = 1; DROP TABLE tb_user; COMMIT; --$Re7Gqwb$ AND password = $Re7Gqwb$$Re7Gqwb$; Error: Invalid user and password combination! Neutralized malicious code. Código malicioso neutralizado.
44. 44/77 Prepared Statement A prepared statement is a server-side object that can be used to optimize performance. Um prepared statement (comando preparado) é um objeto do lado do servidor que pode ser usado para otimizar performance. When the PREPARE statement is executed, the statement is analyzed, statistics collections are made (ANALYZE) and rewritten. Quando PREPARE statement é executado, o comando (statement) é analisado, são feitas coletas de estatísticas (ANALYZE) e reescrito.
45. 45/77 Prepared Statement When given an EXECUTE statement, the statement is planned and prepared executed. Quando é dado um comando EXECUTE, o prepared statement é planejado e executado. This division of labor prevents repetitive tasks of collecting statistics, while allowing the execution plan depend on specific parameters that can be provided. Essa divisão de trabalho evita repetitivos trabalhos de coleta de estatística, enquanto permite ao plano de execução de depender de parâmetros específicos que podem ser fornecidos.
46. 46/77 Prepared Statement Steps / Etapas Normal query: Consulta normal: 1) Parser → 2) Rewrite System → 3) Planner / Optimizer → 4) Executor Prepared Statement: 1) Executor
47. 47/77 Prepared Statement: Practice Create a prepared statement: Criar um prepared statement: > PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = $2;
48. 48/77 Prepared Statement: Practice Execute a prepared statement: Executar um prepared statement: > EXECUTE q_user('foo', 'mypassword'); bool ------ t
49. 49/77 Prepared Statement: Practice Script (5): __________ sql_injection_5.py ___________________________ #!/usr/bin/env python3 # _*_ encoding: utf-8 _*_ import getpass import psycopg2 import sys # DB server as first argument dbhost = sys.argv[1] # Connection string conn_string = """ host='{}' dbname='db_sql_injection' user='u_sql_injection' password='secret' port='5432' """.format(dbhost) →
50. 50/77 Prepared Statement: Practice Script (5): __________ sql_injection_5.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: ') →
51. 51/77 Prepared Statement: Practice Script (5): __________ sql_injection_5.py ___________________________ # SQL string sql = "PREPARE q_user (text, text) AS \ SELECT TRUE FROM tb_user WHERE username = $1 \ AND password = $2;" # Print the sql string after user and password input print(sql) # Execute the SQL string in database cursor.execute(sql) # SQL string with EXECUTE sql = "EXECUTE q_user('{}', '{}');".format(user, password) # Print the SQL string print(sql) →
52. 52/77 Prepared Statement: Practice Script (5): __________ sql_injection_5.py ___________________________ # Execute the SQL string in database cursor.execute(sql) # The result of the string SQL execution res = cursor.fetchone() # User login validation if res: print('Acessed!') else: print('Error: Invalid user and password combination!') sys.exit(1) except psycopg2.Error as e: print('\nAn error has occurred!\n%s' % (e)) # Close the database connection conn.close() ____________________________________________________
53. 53/77 Prepared Statement: Practice A simple test access with correct password: Um teste simples de acesso com senha correta: $ ./sql_injection_5.py ${DBHOST} User: foo Password: PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = $2; EXECUTE q_user('foo', 'mypassword'); Acessed!
54. 54/77 Prepared Statement: Practice A simple test access with wrong password: Um teste simples de acesso com senha errada: $ ./sql_injection_5.py ${DBHOST} User: foo Password: PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = $2; EXECUTE q_user('foo', '123'); Error: Invalid user and password combination!
55. 55/77 Prepared Statement: Practice Attempted malicious code (with apostrophe): Tentativa de código malicioso (com apóstrofo): $ ./sql_injection_5.py ${DBHOST} User: ' OR 1 = 1; DROP TABLE tb_user; COMMIT; -- Password: PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = $2; EXECUTE q_user('' OR 1 = 1; DROP TABLE tb_user; COMMIT; --', ''); An error has occurred! syntax error at or near ";" LINE 1: EXECUTE q_user('' OR 1 = 1; DROP TABLE tb_user; COMMIT; --',... ^ Neutralized malicious code. Código malicioso neutralizado
56. 56/77 Prepared Statement: Practice Attempted malicious code (with double dollar sign): Tentativa de código malicioso (com dólar duplo): $ ./sql_injection_5.py ${DBHOST} User: $$ OR 1 = 1; DROP TABLE tb_user; COMMIT; -- Password: PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = $2; EXECUTE q_user('$$ OR 1 = 1; DROP TABLE tb_user; COMMIT; --', ''); Error: Invalid user and password combination! Neutralized malicious code. Código malicioso neutralizado.
57. 57/77 Password Hashes • Store passwords of application users in database is not a good practice. Não é uma boa prática armazenar senhas de usuários de uma aplicação em uma base de dados. • The DBA does not need and should not know the users passwords. O DBA não precisa e não deve saber senhas de usuários.
58. 58/77 Password Hashes • If your database is invaded the attacker will have access to user passwords. Se sua base de dados for invadida o invasor terá acesso às senhas dos usuários. • The password must be encrypted in an irreversible way, that you can not decrypt it (theoretically). A senha deve ser criptografada de forma irreversível, que não seja possível descriptografá-la (teoricamente).
59. 59/77 Password Hashes • The password unencrypted should never be stored in the database. A senha sem criptografia jamais deve ser armazenada no banco de dados. • We will use the pgcrypto extension that provides the functions crypt to encrypt the password and gen_salt function to "salt" encryption with password. Utilizaremos a extensão pgcrypto que fornece as funções crypt para criptografar a senha e a função gen_salt para "salgar" a criptografia com a senha.
60. 60/77 Password Hashes: Practice Enable the pgcrypto extension: Habilitar a extensão pgcrypto: > CREATE EXTENSION pgcrypto; Drop the table: Apagar a tabela: > DROP TABLE tb_user;
61. 61/77 Password Hashes: Practice Recreate the table: Recriar a tabela: > CREATE TABLE tb_user( username varchar(50) PRIMARY KEY, -- natural primary key password VARCHAR(72) NOT NULL, salt_pw text NOT NULL);
62. 62/77 Password Hashes: Practice Using CTE* to INSERT new user: Usando CTE* para inserir novos usuários: > WITH x AS ( SELECT 'foo' AS user, '123' AS pw, gen_salt('bf') AS salt ) INSERT INTO tb_user (username, password, salt_pw) SELECT x.user, crypt(x.pw::text, x.salt::text), -- password hash x.salt FROM x; * CTE = Common Table Expressions
63. 63/77 Password Hashes: Practice User search test with correct password: Teste de busca de usuário com senha correta: > SELECT TRUE FROM tb_user WHERE username = 'foo' AND password = crypt('123', salt_pw); bool ------ t One row returned. Uma linha retornada.
64. 64/77 Password Hashes: Practice User search test with wrong password: Teste de busca de usuário com senha errada: > SELECT TRUE FROM tb_user WHERE username = 'foo' AND password = crypt('1234', salt_pw); bool ------ No row returned. Nenhuma linha retornada.
65. 65/77 Password Hashes: Practice Script (6): __________ sql_injection_6.py ___________________________ #!/usr/bin/env python3 # _*_ encoding: utf-8 _*_ import getpass import psycopg2 import sys # DB server as first argument dbhost = sys.argv[1] # Connection string conn_string = """ host='{}' dbname='db_sql_injection' user='u_sql_injection' password='secret' port='5432' """.format(dbhost) →
66. 66/77 Password Hashes: Practice Script (6): __________ sql_injection_6.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: ') →
67. 67/77 Password Hashes: Practice Script (6): __________ sql_injection_6.py ___________________________ # SQL string sql = """PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = crypt($2, salt_pw)""" # Print the sql string after user and password input print(sql) # Execute the SQL string in database cursor.execute(sql) # SQL string with EXECUTE sql = "EXECUTE q_user('{}', '{}');".format(user, password) →
68. 68/77 Password Hashes: Practice Script (6): __________ sql_injection_6.py ___________________________ # Print the SQL string print(sql) # Execute the SQL string in database cursor.execute(sql) # The result of the string SQL execution res = cursor.fetchone() →
69. 69/77 Password Hashes: Practice Script (6): __________ sql_injection_6.py ___________________________ # User login validation if res: print('Acessed!') else: print('Error: Invalid user and password combination!') sys.exit(1) except psycopg2.Error as e: print('\nAn error has occurred!\n%s' % (e)) # Close the database connection conn.close() ____________________________________________________
70. 70/77 Password Hashes: Practice A simple test access with correct password: Um teste simples de acesso com senha correta: $ ./sql_injection_6.py ${DBHOST} User: foo Password: PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = crypt($2, salt_pw) EXECUTE q_user('foo', '123'); Acessed!
71. 71/77 Password Hashes: Practice A simple test access with wrong password: Um teste simples de acesso com senha errada: $ ./sql_injection_6.py ${DBHOST} User: foo Password: PREPARE q_user (text, text) AS SELECT TRUE FROM tb_user WHERE username = $1 AND password = crypt($2, salt_pw) EXECUTE q_user('foo', '567'); Error: Invalid user and password combination!
72. 72/77 Password Hashes: Practice Enable expanded display (psql): Ativar modo de exibição expandido (psql): > \x on Querying username, password, salt in the table: Consultando usuário, senha e salt na tabela: > SELECT username, password, salt_pw FROM tb_user; -[ RECORD 1 ]---------------------------------------------------------- username | foo password | $2a$06$GxR7QM.n.qDYWN416OZ7pe4MYyKfo0qwBtAiPlHdFCP4/ikM2wi1C salt_pw | $2a$06$GxR7QM.n.qDYWN416OZ7pe
73. 73/77 Conclusion / Conclusão PostgreSQL has its own mechanisms of encryption passwords and against SQL injection which makes it very independent of the application. O PostgreSQL possui mecanismos próprios de criptografia de senhas e contra SQL injection que o torna muito independente da aplicação.
74. 74/77 Conclusion / Conclusão This makes it easier for the application developer, may delegate such tasks to the database, avoiding technical adjustments in the application and finally provide a robust solution independent of language. Isso facilita para o desenvolvedor da aplicação, podendo confiar tais tarefas ao banco de dados, evitando adaptações técnicas na aplicação e por fim prover uma solução robusta independente da linguagem.
75. 75/77 Donate! The elephant needs you! O Elefante precisa de você! Contribute! :) Contribua! :) http://www.postgresql.org/about/donate/
76. 76/77 Save our planet! Save our planet!
77. 77/77 See you soon!!! Até a próxima!!! Juliano Atanazio juliano777@gmail.com http://slideshare.net/spjuliano https://speakerdeck.com/julianometalsp https://juliano777.wordpress.com :)