Python put dynamic string that has \ and "and ' to mysql table

Question

I have 675,000 strings to put in to mysql table. most of strings are normal words.

but some of strings are like below example of strings

"kms\24""
keyboard'
'someword'and
"touch+\"someword\
24"monitor
cleaner"150"

like this.

I can remove all \ from string. by make string to list and do

list_of_string = list(word)
for i in range(10) :
    if """\\""" in list_of_string :
        del list_of_string[list_of_string.index("""\\""")

but as you can see I can not replace or strip every " because there is some inch expression as at example number 5.

what I should do is remove " when it's number is mutiple of two. so i did

numb = list_of_string.count('"')
if numb % 2 == 0 :
    for i in range(numb):
        del list_of_string[list_of_string.index('"')
else :
    for i in range(numb-1):
        del list_of_string[list_of_string.index('"')

at last did

word = "".join(list_of_string)

now result is

kms24"
keyboard
somewordand (I also removed all ' )
touch+someword
24"monitor
cleaner150

now, I need to put this word into mysql table what i did is

sql1 ='UPDATE {cat} SET {col} = "{val}" WHERE ind = "{ex}"'.format(cat=category, col=column, val=word, ex=index)
c.execute(sql1)

but as you expected, there is error. I can not put kms24" with above script.

anyone know how to force myspl to put string that has " in string?

Use a [parameterized query](https://stackoverflow.com/a/775399/987358). — Michael Butscher, Mar 19 '20 at 02:26
Which sql package are you using? `c.execute()` should have its own method of formatting the sql statement that will take care of escaping for you. Using python's `str.format` leaves you open to sql injection attacks. — tdelaney, Mar 19 '20 at 02:37

score 1 · Accepted Answer · answered Mar 19 '20 at 02:26

1

You should never build SQL strings from external sources. Use the APIs, such as the MySQl Connector API.

The reason for that is you can introduce your own bugs and others can exploit it, which is called a "SQL injection attack".

answered Mar 19 '20 at 02:26

Keith

42,110
11
57
76

I don't understand. what should I do to prevent SQL injection attack? and what should I do to put kms24" in my mysql table. – Shin euichul Mar 19 '20 at 04:00
@Shineuichul Basically, follow the example I linked to. Use the built-in string substitution feature of the API. Don't use your own string formatting at all. – Keith Mar 21 '20 at 00:52

Python put dynamic string that has \ and "and ' to mysql table

1 Answers1