Protect Python Code from Sql Injection

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Highlighted

Protect Python Code from Sql Injection

Hi All,

 

I have devloped  beow code in one of my module but our company's security team  raised incident as below code can be hacked by sql injection.Could you please help me to change  below code so that we can avoid sql injection .

 

print("Please Enter Table name")
tablename = raw_input()
cursor = connect.cursor();
query = "CREATE TABLE D0_DATABASE.{tablename}(CUST_ID INTEGER, CUSTOMER_NAME VARCHAR(50))".format(tablename = tablename)
cursor.execute(query)

 

Thanks in Advance 

 

 

 

Tags (2)

Accepted Solutions
Teradata Employee

Re: Protect Python Code from Sql Injection


@VikashSinha wrote:

Hi @tomnolan ,

 

in actual code the table name is genrated on runtime by python uuid.uuid4().hex method  ... any chance of sql injection if the table name is genrated automatically by uuid


SQL injection attacks refer to SQL request text being composed from user input. Without user input, SQL injection is not possible.

 

If your table name is composed from a UUID-generating function, and is not composed from user input, then SQL injection is not possible.

1 ACCEPTED SOLUTION
4 REPLIES 4
Teradata Employee

Re: Protect Python Code from Sql Injection

The standard technique to avoid SQL injection attacks is to use question-mark parameter markers and bind values, such that any input obtained from the user is only used as bind values, and user input is never used to compose any of the SQL request text. Question-mark parameter markers and bind values can only be used with DML statements such as select, insert, update, and delete.

 

value = raw_input()
with con.cursor() as cur: cur.execute("insert into tab1 values(?)", [value])

 

 

 

Your example shows user input to specify a table name for a DDL statement to create a table. That is a very risky operation. The standard approach to mitigate SQL injection attacks -- question-mark parameter markers -- cannot be used with DDL statements such as create table.

 

Generally speaking, you should avoid composing DDL commands with user input, because that is risky and vulnerable to SQL injection attacks. However, if your app must compose DDL commands with user input, then you need to properly quote any SQL object names that you receive from the user.

 

A SQL object name should be sanitized as follows: Double any embedded double-quote characters ( " ) and then enclose the object name in double-quote characters.

 

tablename = raw_input()
tablename = '"' + tablename.replace('"', '""') + '"' # sanitize to avoid SQL injection attack
with con.cursor() as cur:
cur.execute("create table D0_DATABASE." + tablename + " (CUST_ID INTEGER, CUSTOMER_NAME VARCHAR(50))")

 

 

 

Re: Protect Python Code from Sql Injection

Hi @tomnolan ,

 

Thanks a lot for your reply , the code i provided is a a sample code and in actual code the table name is genrated on runtime by python uuid.uuid4().hex method  . do you still see any and chance of sql injection if the table name is genrated automatically by uuid .

 

Thanks ,

Vikash Sinha

Teradata Employee

Re: Protect Python Code from Sql Injection


@VikashSinha wrote:

Hi @tomnolan ,

 

in actual code the table name is genrated on runtime by python uuid.uuid4().hex method  ... any chance of sql injection if the table name is genrated automatically by uuid


SQL injection attacks refer to SQL request text being composed from user input. Without user input, SQL injection is not possible.

 

If your table name is composed from a UUID-generating function, and is not composed from user input, then SQL injection is not possible.

Re: Protect Python Code from Sql Injection

Thanks a lot @tomnolan