CODEWITHSUNDEEP
pythonflasksqlalchemysql-injectionpymssql
Andrés Garita
Andrés Garita

Reputation: 17

Flask-sqlalchemy + pymssql prevent SQL injections from Stored Procedure params

I have the next route that works perfectly fine:

@home_app.route('/<id>', methods=['GET', 'POST'])
def home(id):
    query = "DECLARE @return_value int, @EXIST bit EXEC @return_value = [dbo].[SP_CHECK_ID] @ID = N'" + id +"', @EXIST = @EXIST OUTPUT SELECT @EXIST as N'@EXIST'"
    result = db.session.execute(query, bind=db.get_engine(app, 'second_db'))
    exist = []
    for row in result:
        exist.append(row['@EXIST'])
    return "Exist? " + str(row['@EXIST'])

The problem is that it could be vulnerable to SQL injection due the param. In order to fix that, I've tried:

query = """
        DECLARE @return_value int, @EXIST bit
        EXEC @return_value = [dbo].[SP_CHECK_ID] @ID = N':id',
        @EXIST = @EXIST OUTPUT
        SELECT @EXIST as N'@EXIST'
        """
result = db.session.execute(query, {'id': id}, bind=db.get_engine(app, 'second_db'))

But I got an error:

sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (102, b"Incorrect syntax near '179'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: "DECLARE @return_value int, @EXIST bit EXEC @return_value = [dbo].[SP_CHECK_ID] @ID = N'%(id)s', @EXIST = @EXIST OUTPUT SELECT @EXIST as N'@EXIST'"] [parameters: {'id': '179'}]

I've tried with other ways:

query = """
        DECLARE @return_value int, @EXIST bit
        EXEC @return_value = [dbo].[SP_CHECK_ID] @ID = N'%s',
        @EXIST = @EXIST OUTPUT
        SELECT @EXIST as N'@EXIST'
        """
result = db.session.execute(query, id, bind=db.get_engine(app, 'second_db'))

But I got:

AttributeError: 'list' object has no attribute 'keys'

I tried too with

from sqlalchemy.sql import text
query = text(...)

and using ? instead of %s, but I got the same errors.

Upvotes: 0

Views: 867

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 52939

One of the benefits of using placeholders is that you do not need to – and should not – quote manually. So when you include

""" ... N':id' ... """

in your query that might render as

""" ... N''179'' ... """

or such, depending on your argument's type etc. The fix is then to remove the quotes and let SQLAlchemy/the DB-API driver handle all that:

query = """
        DECLARE @return_value int, @EXIST bit
        EXEC @return_value = [dbo].[SP_CHECK_ID] @ID = :id,
        @EXIST = @EXIST OUTPUT
        SELECT @EXIST as N'@EXIST'
        """
result = db.session.execute(
    query, {'id': id},
    bind=db.get_engine(app, 'second_db'))

The named placeholder style is a backend-neutral SQLAlchemy abstraction provided by the text() construct. Session.execute() implicitly wraps your textual SQL, if you've omitted it. %s, ? etc. are DP-API specific placeholder styles. pymssql would seem to use the percent style.

Upvotes: 2

Related Questions