Is it safe to use string concatenation for dynamic SQL queries in Python with psycopg2

Question

I’m developing a Python application that interacts with a PostgreSQL database using psycopg2. In certain places, I’m building dynamic SQL queries by concatenating user input with query strings. 

For instance, I have something like this:

query = "SELECT * FROM users WHERE username = '" + user_input + "';"

I’ve heard that this approach might expose the application to SQL injection attacks, but I’m not sure how vulnerable it is in practice, especially since I’m using psycopg2. Is it generally unsafe to use string concatenation like this, and if so, what’s the best alternative to safely handle dynamic queries?

Answer 1

The use of string concatenation while building dynamic SQL queries can expose your application to SQL Injection Attacks, even if you're using psycopg2.

Although, psycopg2 provides some safety, but concatenating user input into SQL strings in risky.

Consider the following example:

query = "SELECT * FROM users WHERE username = '" + user_input + "';"

Here, the attacker could manipulate user_input to execute arbitrary SQL commands.

So, instead of concatenating strings, use parameterized queries, which are safer:

query = "SELECT * FROM users WHERE username = %s;"
cursor.execute(query, (user_input,))

This will ensure that user input is treated as data, not as part of the SQL command.

Comments

Thanks for explaining this so clearly! I didn’t realize how risky string concatenation could be, even with psycopg2. Parameterized queries seem like a much safer option.