0

I'm trying to parse html data and storing into mysql database. i'm taking the index page into a variable and pass the variable values to the database using mysql query but while doing that sometimes i'm getting a syntax error like this 

MySQL Error [1064]: You have an error in your SQL syntax; 
check the manual that corresponds to your     
MySQL server version for the right syntax to use near 've come a long way.&quot;<br><span style="float:   right ; font-size: 11px;">- Jim' at line 1

I've directly copied the raw data line which is causing the problem-

'&quot;I\'ve come a long way.&quot;<br><span style="float: right ; font-size: 11px;">- Jim B., Yardley, PA</span>',

I suspect

\'

is causing the problem. I'm using python for this. I'm not sure what should i use to replace that string so that mysql doesn't detect it as a syntax. I would appreciate your response.

The code for this portion look like

if (section.find('<') >= 0 ):
        indexPage = section[section.find('<'):len(section)]
        indexPage = indexPage.replace("'","\\'")
        #print "Index Page:\n" + indexPage
    else:
        indexPage = None
        #print "No index page"

sql = "insert into testdata(ip,httpV,response_Code,response_date,server,X_Powered_By,index_page,dataset) \
    values('%s','%s','%s','%s','%s','%s','%s','%s')" % \
    (ip,httpV,responseCode,response_date,server,XPower,indexPage,dataSetNo)

try:
        cursor.execute(sql)
        db.commit()
except MySQLdb.Error, e:
        try:
            print "MySQL Error [%d]: %s" % (e.args[0], e.args[1])
        except IndexError:
            print "MySQL Error: %s" % str(e)
saz
  • 955
  • 5
  • 15
  • 26
  • 1
    A better question: What does your code look like where you are building this query? There are plenty of ways to prevent this error and based on your error it looks like you have SQL injection potential. – Andy Nov 11 '14 at 02:10
  • Your use of `%s` placeholders is incorrect. They should be unquoted, and all the input variables should be a tuple as a function argument, not following a `%` like a format string. [Refer to the examples in the docs](http://mysql-python.sourceforge.net/MySQLdb.html#some-examples) – Michael Berkowski Nov 11 '14 at 02:27
  • 1
    So your statement done correctly would look like `cursor.execute("insert into testdata (ip,httpV,......) VALUES (%s, %s,....)", (ip, httpV,.....))` – Michael Berkowski Nov 11 '14 at 02:35
  • @MichaelBerkowski thanks. it helps. query is executing without any error now. – saz Nov 11 '14 at 03:35

0 Answers0