2

So I have a SQL database project which tracks our application's database schemas, stored procedures, etc. It also has deployment script generation sections, including pre- and post-deployment scripts. Part of these scripts includes doing things like caching values of columns that have been removed so they can be moved elsewhere (usually normalized into its own table).

The problem is that the upgrade script process I have implemented, which automatically detects the version and what updates need to be applied at deploy-time, includes references to columns which no longer exist. This is fine when deploying to either an empty database (so the script executor doesn't know there's no column) or to a being-upgraded database, as it can see the column before the deployment. The script validator however doesn't like that I'm referencing columns from a table that it knows about, even if the section which references it will never get called because of the conditions around it.

My current solution is to store in a string all of the parts of the script which handle columns being removed, then running those sections dynamically using EXEC. This is a little icky to me (and EXEC is a code smell issue), though not the worst thing. This is code that's only run once per DB and is never accessible during production operation.

What I want to know is, is there a better way of doing this? Can I somehow comment to the validator that "This won't run, please ignore it"? Is this whole process terrible? (I'm not that experienced of a DB admin at all, so any expertise here would be very helpful.) If I'm doing this all wrong, how do you handle one-off scripts for database updates? I've also considered doing one-off conditional compilations using SQLCMD declarations/labels, then automatically removing sections that aren't needed for a particular version-to-version upgrade using a script.

As a last note, we are using SQLPACKAGE to generate our upgrade scripts. Is there possibly a way to tell the tool to omit some sections in certain cases?

Improve this question
4
  • Why are you allergic to dynamic T-SQL? What's the code smell around EXEC sp_executesql if it's correctly written?
    – Randolph West
    Commented Mar 1, 2017 at 8:04
  • 1
    I've rephrased a little. If I've broken it I apologise. You can reverse edits if you choose.
    – Michael Green
    Commented Mar 1, 2017 at 10:30
  • @RandolphWest Not necessarily allergic. A lot of security talks I've taken have just urged extreme caution with using EXEC in production code (for potentially creating SQLi vulnerabilities), so in general we avoid them where we can. It's not necessarily that using EXEC is bad, but a part of it is that a lot of non-syntax-highlighted SQL in a particular section just looks kind of weird. If the common solution to this problem is to execute the code dynamically then I've designed it using best practices and that's the answer. If there is a better or cleaner way to do it, then I'd prefer that.
    – Nate Diamond
    Commented Mar 1, 2017 at 21:43
  • The concern about SQL injection is unfounded when using EXEC sp_executesql, since it is a parameterised command. There were certainly issues previously with EXEC on its own, running random dynamic code, but this is not the case with sp_executesql, and I encourage you to give it due consideration.
    – Randolph West
    Commented Mar 1, 2017 at 21:56

1 Answer 1

Reset to default
1

Code sent to SQL Server is validated, prior to execution, by SQL Server itself. The query processor checks object names for existence prior to running the query, without regard for any code paths that may or may not be taken.

The only way to avoid the issue you're experiencing is to us sp_executesql exactly as it sounds like you are doing now. Since you are doing this in an upgrade script that you control, there is virtually zero chance for a SQL injection problem, assuming you aren't taking non-validated input from a user (or via a config file the user has access to).

In case you haven't seen this, read Erland Sommarskog's bible on dynamic SQL. - it's a long read, but well worth it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.