The process of python connecting to a PostgreSQL database in detail

1. Common Modules

# Connect to the database

connect() function creates a new database connection dialog and returns a new connection instance object

PG_CONF_123 = {
 'user':'emma',
 'port':123,
 'host':'192.168.1.123',
 'password':'emma',
 'database':'dbname'}
conn = (**PG_CONF_123)

# Open a cursor that operates on the entire database

The connection object creates a cursor that can be used to execute SQL statements.

cur = ()

# Execute an SQL statement that creates a table

The cursor can be used with the execute() and executemany() functions

("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

# Pass parameters to the insert statement

("INSERT INTO test (num, data) VALUES (%s, %s)",(100, "abc'def"))

# Execute the query statement and fetch the data as a python object

("SELECT * FROM test;")
()
(1, 100, "abc'def")

# Submit changes

If you only use the query statement without the commit method, insert/update/delete operations need to call commit(). rollback() function is used to roll back to the last time the commit() method was called.

()

# Close the database connection

()
()

2. Protection against SQL injection vulnerabilities

Typical form of SQL injection vulnerability:

SQL = "select * from userinfo where id = '%s'" % (id)
SQL = "select * from userinfo where id = '{}'".format(id)

If someone maliciously attacks by adding malicious code to the code of the incoming parameter, for example:

= '123; drop tabel userid;'

can pose a serious risk, to prevent this problem, you should pass the parameter via the second bit of the variable: %s (use %s regardless of the data type of the variable)

SQL = "INSERT INTO authors (name) VALUES (%s);" # Note: no quotes
data = ("O'Reilly", )
(SQL, data) # Note: no % operator

This is the whole content of this article.