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.

Improve this question
6
  • 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 stackoverflow.com/questions/16506643/… Commented Jan 27, 2018 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. Commented Jan 27, 2018 at 14:57
  • pin_inp = " '1'; delete from members"' Commented Jan 27, 2018 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 Commented Jan 27, 2018 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. Commented Jan 27, 2018 at 17:42

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.