Problems passing a Python String to SQL query

Question

I got some issues passing my SQL query made in Python to MS SQL.

Were talking about retrieving some code via pyodbc, editing it with regex, and passing it back into a new table of the Database. I also tried some print()-Commands to assure the Code is running in Python and being edited the right way, but I am not able to write those contents back to SQL after editing the strings.

Im trying to pass the string via:

try:   
    values = ""
    for row in cursor.execute("select row1, row2 from db_table"):
        cleanheader = cleanhtml(str(row.row1))
        cleanbody = cleanhtml(str(row.row2))
        values = values + "('" + cleanheader + "','" + cleanbody + "'),"
    values[0:len(values)-1]
    values = values + ";"
    sql = "INSERT INTO db_new_table VALUES ('"+str(values)+"')"
    cursor.execute(sql)

whereas cleanhtml() refers to my regex-function:

def cleanhtml(raw_html):
    cleanr = re.compile('<.*?>|&([a-z0-9]+|#[0-9]{1,6}|#x[0-9a-f]{1,6});')
    cleantext = re.sub(cleanr, '', raw_html)
    return cleantext

Does anyone have an idea on how to pass those lines correctly to SQL?

Never, EVER, use string concatenation to add data to an SQL query. It's a Massive security hole (SQL Injection Attack). Always us parametrised queries... github.com/mkleehammer/pyodbc/wiki/Getting-started#parameters — MatBailie
– MatBailie, Commented Dec 22, 2020 at 11:22

Barbaros Özhan · Accepted Answer · 2020-12-22 12:02:41Z

1

Use a prepared statement rather than concatenation, which is vulnerable to SQL Injection, by creating an auxiliary list(row) to append the each processed member of the tuples coming from the cursor, and prefer using executemany rather than execute as being more performant such as

row=[]
for i in cursor.execute("SELECT col1, col2 FROM db_table"):
        cleanheader = cleanhtml(str(i[0]))
        cleanbody = cleanhtml(str(i[1]))
        row.append((cleanheader,cleanbody),)        

for i in row:
        cursor.executemany('INSERT INTO db_new_table VALUES(?,?)',([i]))

con.commit()
con.close

answered Dec 22, 2020 at 12:02

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hannu Varjoranta · Accepted Answer · 2020-12-22 11:12:08Z

0

Looks like you are quoting twice the data in VALUES block with single-quotes. It seems that you are also messing up the parenthesis there. Print out your query before executing it.

sql = "INSERT INTO db_new_table VALUES ('"+str(values)+"')"

values here is already quoted and in parenthesis.

answered Dec 22, 2020 at 11:12

Hannu Varjoranta

2094 silver badges7 bronze badges

Collectives™ on Stack Overflow

Problems passing a Python String to SQL query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related