Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #61371 > unrolled thread

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")

Back to article view | Back to comp.lang.python

Contents

  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 <jaiprakashsingh213@gmail.com> - 2013-12-09 00:41 -0800
    Re: 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 <daniel@daniel-watkins.co.uk> - 2013-12-09 09:32 +0000
    Re: 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") Chris Angelico <rosuav@gmail.com> - 2013-12-09 20:36 +1100
    Re: 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") MRAB <python@mrabarnett.plus.com> - 2013-12-09 18:06 +0000
    Re: 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") Dan Stromberg <drsalists@gmail.com> - 2013-12-10 08:23 -0800
    Re: 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") Chris Angelico <rosuav@gmail.com> - 2013-12-11 03:28 +1100
    Re: 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") nowebdevmyrrh@gmail.com - 2014-02-25 09:37 -0800
      Re: 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") MRAB <python@mrabarnett.plus.com> - 2014-02-25 18:10 +0000

#61371 — 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")

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")
>>> 

[toc] | [next] | [standalone]

#61373

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

[toc] | [prev] | [next] | [standalone]

#61375

On Mon, Dec 9, 2013 at 7:41 PM, Jai <jaiprakashsingh213@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

[toc] | [prev] | [next] | [standalone]

#61400

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)"""
     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
>

[toc] | [prev] | [next] | [standalone]

#61476

[Multipart message — attachments visible in raw view] — view raw

On Mon, Dec 9, 2013 at 12:41 AM, Jai <jaiprakashsingh213@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.

[toc] | [prev] | [next] | [standalone]

#61477

On Wed, Dec 11, 2013 at 3:23 AM, Dan Stromberg <drsalists@gmail.com> wrote:
>
> On Mon, Dec 9, 2013 at 12:41 AM, Jai <jaiprakashsingh213@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

[toc] | [prev] | [next] | [standalone]

#67045

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. 

[toc] | [prev] | [next] | [standalone]

#67047

On 2014-02-25 17:37, nowebdevmyrrh@gmail.com wrote:
> 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.
>
It might have the same cause, but that's just a guess because you
haven't provided much information.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web