CODEWITHSUNDEEP
pythonsqlmariadbsql-injectionstacked-query
Roshin Raphel
Roshin Raphel

Reputation: 2709

Executing Multiple SQL statements using execute()

I am trying to do an SQL injection in a server of mine. I am using the command :

cursor.execute("select * from some_table")

to execute the SQL commands in my server. But is there a way to execute multiple commands using the same execute() function.
I tried :

cursor.execute("select * from some_table ; INSERT INTO ...")

DBMS is mariadb

Upvotes: 1

Views: 4567

Answers (2)

T.H.
T.H.

Reputation: 876

MySQL (and MariaDB) allows you to run several SQL statements in one go by setting capability flag CLIENT_MULTI_STATEMENTS (0x10000) on connecting to the database server. Check out the documentation of the python database driver used in your implementation, there should be method to set the flag and you need to do so in advance before creating cursor and execute the SQL statements.

Here is the code example of mariadb python driver, for other drivers (like pymysql) they may work the same way

import mariadb
from mariadb.constants.CLIENT import MULTI_STATEMENTS

conn_params= {
    "user" : "YOUR_USERNAME",
    "password" : "YOUR_PASSWORD",
    "host" : "NETWORK_DOMAIN_NAME",
    "database" : "DB_NAME",
    "client_flag": MULTI_STATEMENTS,
}

db_conn = mariadb.connect(**conn_params)

rawsqls = [
    'SELECT * FROM table2',
    'INSERT INTO table3 ....',
    'SELECT * FROM table4',
]

with db_conn.cursor() as cursor:
     cursor.execute(';'.join(rawsqls))
     rows1 = cursor.fetchall()
     cursor.nextset()
     rows2 = cursor.fetchall()
     cursor.nextset()
     rows3 = cursor.fetchall()

CAUTION

To avoid SQL injection, You should be careful and use the flag CLIENT_MULTI_STATEMENTS ONLY when you are pretty sure that all the inputs to your SQL statements come from trusted source.

Upvotes: 2

Stefan Winkler
Stefan Winkler

Reputation: 3966

Here is an overview of SQL injection strategies. The one you are trying to do is called stacking queries. It seems that at least this strategy is prevented by most database APIs.

You mention MariaDB which is basically more or less the same as MySQL.

And although python is not listed explicitly, I would also assume that the python database API prevents query stacking.

Update: When you check the API of execute() you can see there is a parameter multi which defaults to False. As long as you don't set it to True, you should be safe.

Upvotes: 1

Related Questions