== How to fix it in SQLAlchemy

=== Code examples

The following code is an example of an overly simple data retrieval function.
It is vulnerable to SQL injection because user-controlled data is inserted
directly into a query string: The application assumes that incoming data
always has a specific range of characters and ignores that some characters may
change the query logic to a malicious one.

In this particular case, the query can be exploited with the following string: 

----
' OR '1'='1
----

Using the UNION clause, an attacker would also be able to perform queries against
other tables and combine the returned data within the same query result.

==== Noncompliant code example

[source,python,diff-id=1,diff-type=noncompliant]
----
from flask import request
import sqlalchemy

@app.route('/example')
def get_users():
    user = request.args["user"]
    engine = sqlalchemy.create_engine(connection_string)
    conn = engine.connect()

    conn.execute("SELECT user FROM users WHERE user = '" + user + "'") # Noncompliant
----

==== Compliant solution

[source,python,diff-id=1,diff-type=compliant]
----
from flask import request
import sqlalchemy

@app.route('/example')
def get_users():
    user = request.args["user"]
    engine = sqlalchemy.create_engine(connection_string)
    metadata = sqlalchemy.MetaData(bind=conn, reflect=True)
    users = metadata.tables['users']
    conn = engine.connect()

    sql = users.select().where(users.c.user == user)
    conn.execute(sql)
----

=== How does this work?

:secure_feature: SQLAlchemy
:unsafe_function: sqlalchemy.text()

include::../../common/fix/secure-by-design.adoc[]
