Name Search Returns Syntax Error on Specific Names

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:

Isaac Newton
Isabella Rossellini
Robin Nottingham
Ishmael Reed

(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.)

Name Search Returns Syntax Error on Specific Names

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s