Python cgi script inserts only integers into mysql database
Question: I started scripting in Python and I ran a into weird problem.

I am trying to insert some values into mysql database. Database fields are
varchar, utf-8 unicode

Using Python 3.2 and wamp server 2.2(apache 2.2.21, mysql 5.5.20)

If I try print(ime), print(prezime) etc. it prints values of a form from
another page, so I am getting correct values.

if I write sql statement as:

    
    
    "INSERT INTO Clanovi(CL_Ime,CL_Prezime, CL_Email, CL_Tel) VALUES ("aa","aa","aa","aa")"
    

it inserts those values into database

if I write:

    
    
    sql = "INSERT INTO Clanovi(CL_Ime,CL_Prezime, CL_Email, CL_Tel) VALUES (%s,%s,%s,%s)",(ime,prezime,email,tel)
    

it doesnt work

finaly if I put

sql = "INSERT INTO Clanovi(CL_Ime,CL_Prezime, CL_Email, CL_Tel) VALUES
(%s,%s,%s,%s)"%(ime,prezime,email,tel)

if I put only numbers into form fields it will commit to database (add new
data) ime= 21 prezime= 232 email =123 tel=123 if I put anything else like
letters it wont add new row to database ime= smth prezime=blabla email = aa
tel =dada

(I know this method has security risk)

here is whole code ( I putted print(sql) just to see what my query looks like
at the end)

    
    
    #!c:\python32\python.exe
    import MySQLdb
    import cgi
    print ("Content-type: text/html\n\n")
    
    form = cgi.FieldStorage()
    ime =form["ime"].value
    prezime = form["prezime"].value
    email= form["email"].value
    tel = form["tel"].value 
    print(ime)      
    db = MySQLdb.connect("localhost","root","","biblioteka" )
    
    cursor = db.cursor()
    
    sql = "INSERT INTO Clanovi(CL_Ime,CL_Prezime, CL_Email, CL_Tel) VALUES (%s,%s,%s,%s)"%(ime,prezime,email,tel)
    print(sql)
    try:
       cursor.execute(sql)
       db.commit()
    except:
       db.rollback()
    db.close()
    

Thank you for your time and help.


Answer: You shouldn’t assemble your query using Python’s string operations because
doing so is insecure; it makes your program vulnerable to an SQL injection
attack. Instead, use the DB-API’s parameter substitution. Put `?` as a
placeholder wherever you want to use a value, and then provide a tuple of
values as the second argument to the cursor’s `execute()` method:

    
    
    sql = "INSERT INTO Clanovi(CL_Ime,CL_Prezime, CL_Email, CL_Tel) VALUES (?,?,?,?)"
    print(sql)
    try:
       cursor.execute(sql, (ime, prezime, email, tel))
       db.commit()
    except:
       db.rollback()
    db.close()
    

**Update** : Luke is right (see comments): MySQL uses '%s' instead of '?

Updated sql-statement: `sql = "INSERT INTO Clanovi(CL_Ime,CL_Prezime,
CL_Email, CL_Tel) VALUES (?,?,?,?)"`

