I encountered an interesting bug in an older third-party application this week. The application includes a customer look-up feature by first and/or last name. Occasionally, instead of returning results, the application returns the following syntax error:
Select Error: SQLSTATE = 42000
Microsoft OLE DB Provider for SQL Server
Incorrect syntax near ‘Isaac’
The error seemed to be linked to specific names–each time the same name was entered, the application would return the same error. Querying the same view with the same parameters would not return an error in SSMS.
The key to the problem, and a workaround, came from the names themselves:
(These aren’t the exact same names, but they follow the same pattern.)
Notably, three of the first names start with “Is,” which is also a reserved keyword in SQL, but Robin Nottingham looked like an exception, until I realized that his last name also starts with the reserved keyword “Not.”
Somehow, the way that the application was passing the parameters to the SQL 2008 database was causing SQL to interpret the keywords independent of the parameter and causing the error.
The most obvious workaround seemed to be to enclose the search criteria in [brackets], “quotation marks”, or ‘apostrophes’. In this case, brackets failed but quotation marks worked! (We never tested apostrophes since quotation marks worked but I’ve included them here because that could be a workaround in a different application.)