Python MYSQL problems

Question

I have been trying to create a clock in and out the system, for the small business that I work for.

At the moment I am having trouble getting it to talk to the MySQL server. I am probably doing something really silly wrong.

The section I am having trouble with is this:

cnx = mysql.connector.connect( host=localhost, user="time_clock", passwd="kayak100", db="Staff_time_clock")
cursor = cnx.cursor()
staffid = input('Please enter your Staff ID now ...')
idcheck = ("SELECT staff_name, staff_id FROM Staff WHERE staff_id = %s")
cursor.execute(idcheck,(staffid))

I am quite new to using MYSQL and have learnt it to try and do this small program.

Edit - Yeah sorry forgot to add the error code that I get.

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-49-b672a17a258e> in <module>()
----> 1 cursor.execute(idcheck,)

C:\Users\ellio\Anaconda3\lib\site-packages\mysql\connector\cursor.py in     execute(self, operation, params, multi)
    505             self._executed = stmt
    506             try:
--> 507                        self._handle_result(self._connection.cmd_query(stmt))
    508             except errors.InterfaceError:
    509                 if self._connection._have_next_result:  # pylint:   disable=W0212

C:\Users\ellio\Anaconda3\lib\site-packages\mysql\connector\connection.py in  cmd_query(self, query)
    720         if not isinstance(query, bytes):
    721             query = query.encode('utf-8')
--> 722         result = self._handle_result(self._send_cmd(ServerCmd.QUERY,    query))
    723 
    724         if self._have_next_result:

C:\Users\ellio\Anaconda3\lib\site-packages\mysql\connector\connection.py in _handle_result(self, packet)
    638             return self._handle_eof(packet)
    639         elif packet[4] == 255:
--> 640             raise errors.get_exception(packet)
    641 
    642         # We have a text result set

ProgrammingError: 1064 (42000): 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' at line 1

This is the total of the error.

Thank you

What is the problem you're having exactly?

Avihoo Mamka
– Avihoo Mamka

2017-02-21 14:23:33 +00:00
Commented Feb 21, 2017 at 14:23 — Avihoo Mamka
– Avihoo Mamka, Commented Feb 21, 2017 at 14:23
add the output (error) you're getting

davejal
– davejal

2017-02-21 14:24:57 +00:00
Commented Feb 21, 2017 at 14:24 — davejal
– davejal, Commented Feb 21, 2017 at 14:24

nil · Accepted Answer · 2021-02-21 02:14:46Z

New user here, so not enough rep to comment on the existing answer. I know the question (and answer) I'm replying to is four years old, but this page came up when I was googling earlier, and the existing answer contains a rather dangerous solution.

When building queries, there are two ways to do it - either by building a string with the whole query in it, or by building the query string with placeholders for values to be added (aka a parametrised query). The first is dangerous, as unless you're extremely careful, it allows SQL injection attacks. Parametrised queries are the way to keep safe.

The question starts with a broken parametrised query - idcheck is the query with placeholders, and the (staffid) bit in .execute is the parameter to insert. That's the right way to do it, but it's broken because the first argument to .execute is a string (so the parentheses around it are unnecessary but not a problem), and the second argument is a tuple - something like (thing1,thing2) - but as you've only one parameter in there (a singleton) you need to use the special syntax of (staffid,) with the oddly placed comma.

The suggested (and accepted) answer makes things work by removing the parametrisation and just building a plain, unsafe, string. It's dangerous - the user could enter 1 OR 1=1 and it will return the whole table (because your query now says ...WHERE staff_id = 1 OR 1=1, which is always true), or worse still, 1;DROP TABLE Staff - then what are you executing?

# broken original
idcheck = ("SELECT staff_name, staff_id FROM Staff WHERE staff_id = %s")
cursor.execute(idcheck,(staffid))

# dangerous - allows SQL injection attacks
idcheck = "SELECT staff_name, staff_id FROM Staff WHERE staff_id = %s" % staffid
cursor.execute(idcheck)

# do it this way
idcheck = "SELECT staff_name, staff_id FROM Staff WHERE staff_id = %s"
cursor.execute(idcheck, (staffid,))

Stay safe!

Avihoo Mamka · Accepted Answer · 2017-02-21 14:33:52Z

0

Your problem is that you're not formatting your string correctly.

Try to run the following:

cnx = mysql.connector.connect( host=localhost, user="time_clock", passwd="kayak100", db="Staff_time_clock")
cursor = cnx.cursor()
staffid = input('Please enter your Staff ID now ...')
idcheck = "SELECT staff_name, staff_id FROM Staff WHERE staff_id = %s" % staffid
cursor.execute(idcheck)

answered Feb 21, 2017 at 14:33

Avihoo Mamka

4,8164 gold badges34 silver badges45 bronze badges

1 Comment

Elliott Buckingham Over a year ago

Thank you @AvihooMamka that has solved it. Looked it up quite a bit online but there were so many variations that didn't seem to work.

Collectives™ on Stack Overflow

Python MYSQL problems

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related