Connections and prepared statements

Reaching a database from a program: driver, connection string, the open, prepare, bind, execute, read, close cycle; prepared statements against concatenation, SQL injection and releasing resources.

34 cards
7 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers
The steps
Scenarios
Understand

A few cards from the deck

Database driver: what is it?
Show the answer
A library that speaks the protocol of one specific database system and gives the program an interface to send queries and read the answers.
The program never talks to the server directly: the driver (a JDBC driver in Java, psycopg for PostgreSQL in Python) opens the channel to the server and translates the calls.
What does this program print?

```python
import sqlite3
c = sqlite3.connect(":memory:")
c.execute("CREATE TABLE compte (id INTEGER PRIMARY KEY, email TEXT)")
c.executemany("INSERT INTO compte (email) VALUES (?)", [("a@x.fr",), ("b@x.fr",), ("c@x.fr",)])
saisie = "' OR '1'='1"
q = "SELECT id FROM compte WHERE email = '" + saisie + "'"
print(c.execute(q).fetchall())
`
- [(1,), (2,), (3,)]
- []
- [(1,)]
- an OperationalError because of the apostrophe
Show the answer
[(1,), (2,), (3,)]
The query becomes WHERE email = '' OR '1'='1': the condition is always true, so every row is returned. With WHERE email = ? and the input as a parameter, the result would be [].
In a prepared statement, the SQL text is sent with …, then the values are … separately before execution.
Show the answer
In a prepared statement, the SQL text is sent with placeholders, then the values are bound separately before execution.
The two steps are distinct: the server knows the shape of the query before it receives any data.
Database connection: what is it?
Show the answer
An authenticated channel opened between the program and the database server, through which queries and results travel.
A connection uses up a server resource, and their number is limited: close it as soon as it is no longer needed.

Study this deck