How do I query SQL Server database with a user's input?

Question

import pyodbc

def read(conn):
    print("Read")
    cursor = conn.cursor()

    userInputOpening = input("Enter a opening that you would like to know: ")
    print(userInputOpening)

    cursor.execute("select * from openings where name like '%{}%'".format(userInputOpening))
    
    for row in cursor:
        print(f"{row}")

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=SAM-PC\SQLEXPRESS;'
                      'Database=ChessOpenings;'
                      'Trusted_Connection=yes;'
)

read(conn)

I get a error with the user input when I use an ' within the name.

For example:

If the userInputOpening = london

It works and gives a list of all the openings that have something like "london" in them.

But...

If the userInputOpening = King's

It throws an error:

cursor.execute("select * from openings where name like '%{}%'".format(userInputOpening)) pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 's'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark after the character string ''. (105)")

What do I need to put in the cursor.execute() so that it takes whatever the userInputOpening entered.

I want to treat it like a search engine and have it display all the results of the users search.

I also want to make it so that user doesn't have to be perfect with their title hence why I've added the LIKE in my SQL statement

Thanks!

The correct way to handle that is pass the user input using a parameter. Otherwise you need to escape it as defined in the documentation. — Dale K, Dec 13 '21 at 01:28
Does this answer your question? [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) — Charlieface, Dec 13 '21 at 17:41

score -1 · Answer 1 · answered Dec 13 '21 at 02:24

-1

The problem is the quotation mark in the input string (King's) Your SQL statement then becomes

where name like '%King's%'

(hence an extra single quote error message). Change the single quote to two single quotes and it should work fine

 userInputOpening.replace("'", "''")

Not a python guy, but I believe that is the proper replace syntax

answered Dec 13 '21 at 02:24

Sparky

14,967
2
31
45

Note that longer-term, I agree with Dale K, you should adapt the code to use parameters since a malicious user could cause problems by the value they enter in the UserInputOpening (search SQL Injection to understand the potential risk) – Sparky Dec 13 '21 at 02:30
1

This is really not a good answer at all. It leaves the code wide open to sql injection. I get that you mention it in your comments but this answer would be great if you demonstrated how to use parameters instead of incredibly dangerous code. – Sean Lange Dec 13 '21 at 04:01
I agree that the risk is there. This is a student in College asking the question, so I felt the first step is to illustrate /fix his problem. I think once you (the op) understands what is happening, they can then tackle SQL Injection. If the op were an experienced developer, rather than a college student, I'd answer differently. – Sparky Dec 13 '21 at 13:03
Nothing in this post indicates they are a student. And if they are a student then it is even more important to teach them the right way of doing things before they develop terrible habits. Nothing worse than having to unlearn your knowledge after college because what you learned is wrong. – Sean Lange Dec 13 '21 at 14:18
If you look up the op, you will see his profile indicates he is a student. – Sparky Dec 13 '21 at 16:54

How do I query SQL Server database with a user's input?

1 Answers1