1

I'm trying to make an access control system based on pincode entry. I'm having issues on how to check if the pincode entered is present in the MySQL database.

Below snippet works, it asks for the pincode, and can successfully parse it with the database when the pincode only contains numbers.

pin_inp = raw_input("Enter PIN: ")
cursor.execute("SELECT COUNT(1) FROM members WHERE pincode = " + pin_inp + ";")

But I would like to have alphanumerical pincodes though. I thought I could just create the pincode column as VARCHAR and enter alphanumerical pincodes, but then it will not successfully parse with the database. With an alphanumerical entry I get this error:

_mysql_exceptions.OperationalError: (1054, "Unknown column '7988DB' in 'where clause'")

So I have a few rows in the members table for testing, some with numerical values in column pincode, some with alphanumerical values. When I run above code, the numerical entries are OK, but the alphanumerical values throw the 1054 error.

Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
ipanema211
  • 40
  • 5
  • 1
    Strings in a query need to be quoted. You should also not be using raw variables due to the risk of SQL injection, like here https://stackoverflow.com/questions/16506643/inserting-variables-mysql-using-python-not-working – alaricljs Jan 27 '18 at 14:56
  • 1
    Well the first step is to not use string formatting for queries. `cursor.execute("SELECT COUNT(1) FROM members WHERE pincode = %s", (pin_inp,))` to help against sql injection. – roganjosh Jan 27 '18 at 14:57
  • `pin_inp = " '1'; delete from members"'` – DeepSpace Jan 27 '18 at 15:02
  • Deepspace, could you please elaborate your code? I’m interested why and what it does... i’m Fully aware that I have to alter my query with parameters to be secure, but I simplified my code at one point to trace the error – ipanema211 Jan 27 '18 at 17:35
  • DeepSpace was just showing you how an SQL injection can be done. If a hacker enters the value `'1'; delete from members` in your pincode field, then your query will end up deleting all records from the `members` table. – Racil Hilan Jan 27 '18 at 17:42
  • Ok thanks, it seems I still have a lot to learn :) – ipanema211 Jan 27 '18 at 19:56

1 Answers1

0

When you use numerical values, you just enter them like you did, but when you used alphanumerical values, you must surround them with apostrophes like this'my value'. So change your code to:

cursor.execute("SELECT COUNT(1) FROM members WHERE pincode = '" + pin_inp + "';")

However, be careful here. This code will be open to SQL injection since the pin_inp is coming from user's input. You either have to validate it, or better use a parameterized SQL statement.

Racil Hilan
  • 24,690
  • 13
  • 50
  • 55