ProgrammingError: (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 'S SIZE 11.5 NEW IN BOX', '$49.99')' at line 1")

[Jai]

from BeautifulSoup import BeautifulSoup
import re, urllib2, MySQLdb
#MySQLdb.escape_string(" ")
import sys
import unicodedata

if __name__=="__main__":
#link = raw_input("Enter the url link: ")
db = MySQLdb.connect("localhost","root","", "ebay")
cursor=db.cursor()
link = "http://www.ebay.com/sch/Mens-Shoes-/93427/i.html?_pppn=r1&_dcat=93427&LH_ItemCondition=1000%7C1500"
link1 =db.escape_string(link)
page = urllib2.urlopen(link)
soup = BeautifulSoup(page)

Contents = soup.findAll(itemprop = "name")
lst_content = []
for x in Contents:
x = x.string
lst_content.append(x)
Price = soup.findAll(itemprop = "price")
lst_price =[]
for y in Price:
y = y.string
lst_price.append(y)
for x , y in zip(lst_content, lst_price):
sql = """insert into `category` (url, catagory,price) VAlUES ('%s', '%s', '%s')"""%(link1,x,y)
#print sql
sql = unicodedata.normalize('NFKD', sql).encode('ascii','ignore')
#sys.exit(0)
cursor.execute(sql)
db.commit()
db.close()



+++++++++++++++++++++++

error occur

ProgrammingError: (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 'S SIZE 11.5 NEW IN BOX', '$49.99')' at line 1")
>>>

[Daniel Watkins]

On Mon, Dec 09, 2013 at 12:41:57AM -0800, Jai wrote:
> sql = """insert into `category` (url, catagory,price) VAlUES ('%s', '%s', '%s')"""%(link1,x,y)

> sql = unicodedata.normalize('NFKD', sql).encode('ascii','ignore')

> cursor.execute(sql)

>
> ProgrammingError: (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 'S SIZE 11.5 NEW IN BOX', '$49.99')' at line 1")

Though you haven't given the full traceback, I suspect that the lines
above are what is causing your problem. My best guess is that you're
being hit by a form of SQL injection[0], in that the values you are
combining in to your query have single quotes which are resulting in an
SQL statement that looks like:

insert into `category` (url, category, price) VALUES ('...', 'MEN'S SIZE 11.5 NEW IN BOX', '$49.99');

As you can see, the second value you are passing has mismatched quotes.
This is a common problem, so the MySQLdb library handles it by allowing
you to pass in the values you want to cursor.execute; it then takes care
of escaping them correctly:

sql = """insert into `category` (url, catagory,price) VAlUES ('%s', '%s', '%s')"""

cursor.execute(sql, (link1, x, y))

I'm not 100% sure what the Unicode normalisation is meant to be doing,
so you'll have to work out how to integrate that yourself.


Cheers,

Dan


[0] https://en.wikipedia.org/wiki/SQL_injection

[Chris Angelico]

On Mon, Dec 9, 2013 at 7:41 PM, Jai <jaiprakas...@gmail.com> wrote:
> for x , y in zip(lst_content, lst_price):
> sql = """insert into `category` (url, catagory,price) VAlUES ('%s', '%s', '%s')"""%(link1,x,y)
> #print sql
> sql = unicodedata.normalize('NFKD', sql).encode('ascii','ignore')
> #sys.exit(0)
> cursor.execute(sql)

My reading of your error message suggests that you just came across an
item category with an apostrophe in it. What you've done here is
horribly dangerous - it's an invitation for SQL injection attacks. Do
not EVER do this sort of thing, it will always come back to bite you!

Look into parameterized queries. They are the right way to do this.
Read up on SQL injection and how to avoid it.

Also: Encoding to ASCII, ignore, is a bad idea. You'll do far better
to tell MySQL to use UTF-8 and then store true Unicode. Though you may
find that you'll do better to get a better database like PostgreSQL,
as MySQL has been known to have some issues with Unicode (not sure if
they're all fixed; MySQL has enough other problems that I don't bother
with it any more).

Small tip, also: Spelling "VAlUES" with a lower-case L is only going
to confuse people, mainly yourself :) You also seem to be inconsistent
with your names - the table is called "category" (I don't think it's a
reserved word, so the escaping is unnecessary there), and the column
is "catagory". Is one of those incorrect?

ChrisA

[MRAB]

On 09/12/2013 09:32, Daniel Watkins wrote:
> On Mon, Dec 09, 2013 at 12:41:57AM -0800, Jai wrote:

>> sql = """insert into `category` (url, catagory,price) VAlUES ('%s', '%s', '%s')"""%(link1,x,y)

>> sql = unicodedata.normalize('NFKD', sql).encode('ascii','ignore')

>> cursor.execute(sql)

>>
>> ProgrammingError: (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 'S SIZE 11.5 NEW IN BOX', '$49.99')' at line 1")
>

> Though you haven't given the full traceback, I suspect that the lines
> above are what is causing your problem. My best guess is that you're
> being hit by a form of SQL injection[0], in that the values you are
> combining in to your query have single quotes which are resulting in an
> SQL statement that looks like:
>
> insert into `category` (url, category, price) VALUES ('...', 'MEN'S SIZE 11.5 NEW IN BOX', '$49.99');
>
> As you can see, the second value you are passing has mismatched quotes.
> This is a common problem, so the MySQLdb library handles it by allowing
> you to pass in the values you want to cursor.execute; it then takes care
> of escaping them correctly:
>

> sql = """insert into `category` (url, catagory,price) VAlUES ('%s', '%s', '%s')"""

> cursor.execute(sql, (link1, x, y))
>
You shouldn't put quotes around the placeholders:

sql = """insert into `category` (url, catagory,price) VAlUES (%s,
%s, %s)"""

[Dan Stromberg]

On Mon, Dec 9, 2013 at 12:41 AM, Jai <jaiprakas...@gmail.com> wrote:

        sql = """insert into `category` (url, catagory,price) VAlUES ('%s', '%s', '%s')"""%(link1,x,y)

Is that VALUES or VAlUES or VAIUES?   It probably should be VALUES.

[Chris Angelico]

On Wed, Dec 11, 2013 at 3:23 AM, Dan Stromberg <drsa...@gmail.com> wrote:
>
> On Mon, Dec 9, 2013 at 12:41 AM, Jai <jaiprakas...@gmail.com> wrote:
>>
>>

>> sql = """insert into `category` (url, catagory,price) VAlUES
>> ('%s', '%s', '%s')"""%(link1,x,y)
>
>

> Is that VALUES or VAlUES or VAIUES? It probably should be VALUES.

SQL's case insensitive. But I did have to check carefully to see that
that really was correct.

ChrisA

[nowebd...@gmail.com]

HI, I'm also getting this kind of error.

This will show when I do the edit function
http://screencast.com/t/hGSbe1vt

and this when performing the delete
"Error: 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 '' at line 1"

What confused me more is that my website works fine on my live server and will only throws error when running on my wampserver.

[MRAB]

It might have the same cause, but that's just a guess because you
haven't provided much information.