I am trying to help with some SQL and we were really having fun today! A complicated dynamic SQL statement involving cursors was failing with the illuminating error: syntax error near ‘a’. When working with SQL Server it is not easy to parameterize for table names, hence the dynamic SQL instead of normal static SQL. Well, the problem turned out to be a quote in a name in the database. This was causing the generated SQL statement to be incorrect.
The best would be to fix all SQL queries, but that was not feasible right now. Instead we tried to replace all quotes by two quotes. (This was a staging table anyway, so no problem in polluting this table: it is not used by others). Sometimes you have to be pragmatic and take a short cut. The static SQL to do: replace a single quote by two single quotes looks already funky but the dynamic SQL version is really horrific:
Beware. An extreme case of this is when an non-trusted party can manipulate the input and you get SQL injection attacks. You might want to look up the function that cleans input parameters for the programming language that you are using (PHP, C, Python, etc.).
ReplyDeleteIf no such problems exist, you can always use the `` (note: not normal quote, but special back-quote) to put the table names between. You can normally assume that '`' is not inside a table name (that is, assume until an intelligent entity is controlling your inputs)
What I dislike about SQL is that (almost) every DBMS uses its own identifier quotation. And as in your case the error messages are often useless.
ReplyDelete