October 11, 2011 at 8:37 am
Hi all,
I am an entry-level programmer who had limited knowledge of the power of SQL when I first started. Naturally, when I first started developing database applications, I generated dynamic SQL statements through my code, with user input, to make changes to the database. I knew to escape special characters and especially single quotes, in order to prevent SQL injection attacks. However, I've recently learned (thankfully, not through experience, but from a security article I read online), that even escaping your special characters and quotes is not always enough to prevent SQL injection. So, I started using parameterized queries instead.
It was easier than I thought it would be. I just created a method that accepts five arguments - the beginning of the SQL statement (INSERT INTO... UPDATE...) an array of field names, an array of values, an array of datatypes, and the end of the SQL statement (either an empty string or a WHERE clause). Then it iterates through the arrays, creating the parameterized SQL statement, and executing it. I use it all the time now and it's pretty slick.
My question is, what is the norm as far as generating SQL statements when no user input is contained within the statement? My example would be an audit trail entry. I write to a table that a particular user (using their logged-in username) performed an addition, edit, or removal of something, on whatever table name, at a given time, blah blah blah and so on and so forth.
Right now, I'm still using Dynamic SQL for inserting rows into my audit trail. Is it considered necessary to migrate this over to parameterized queries as well, even though there is no user-entered data in the SQL query itself?
Thanks!
October 11, 2011 at 8:46 am
Do you need to use dynamic SQL at all? For the example you gave, it sounds like a simple stored procedure, with parameters but no dynamic SQL, will do the job.
John
October 11, 2011 at 8:58 am
I suppose I could, but I am of the philosophy that program logic goes in the program, and data goes in the database. Stored procedures and triggers or any other method of manipulating data on the database side, aside from those used by outside interfaces and reports, are things I try to stay away from.
October 11, 2011 at 9:02 am
It sounds like you are building the query in the front-end code and then sending that query to the database to be executed. If that is the case, then that is not dynamic sql, but a pass-through query. Dynamic sql is building and executing the query string within a stored procedure.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 11, 2011 at 9:05 am
Well, I don't agree with you on that, but we can agree to differ. So my question becomes this: why do you need dynamic SQL at all? Can you not just issue INSERT statements, or whatever else you need, without wrapping it up in an EXEC or sp_executesql command?
John
October 11, 2011 at 9:08 am
toddasd, I believe you are correct. I must have been using the wrong term. I apologize. What you said is exactly what I am doing.
October 11, 2011 at 9:08 am
John, yes, I am issuing an INSERT statement. I apologize for the confusion. I think I called it the wrong thing.
October 11, 2011 at 9:16 am
I would have to agree with John. Auditing belongs in the database. If it is in your application how can you create your audit trail when the data is updated from another place in the code? do you have to recreate the logic to create your audit? What about another application that modifies your data that would require an audit? What about when you have to edit the data directly from SSMS? do you also have to insert the audit information. This type of thing just simple belongs in a trigger. Business rules belong in the front end (unless you have business rule layer between presentation and data), and data belongs in the data layer. That means that ALL data and data manipulation belongs in the data layer (the database).
In your example, you are probably safe not using parameterized pass through queries for auditing. But as you said, they are really easy to use. Why not just do it for auditing too?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 9:22 am
Hmm, you know, I never thought about it from that point of view!
I think I will still stand by my thought that the manipulation of data belongs in the application itself, but you are right - a more complete audit trail would be generated when it is generated via triggers. I am going to have to seriously consider that.
Thank you for the suggestion.
BTW - yes, I do use an object layer. I work for a relatively small company, and we never had any business objects in place, but I create them as I need them, and my application calls methods inside the objects, and the objects themselves generate the SQL that goes to the database.
October 11, 2011 at 9:28 am
Just another point of view on your business objects creating your sql. What happens when the data structure changes slightly? You have to recompile and deploy all your applications that use that business object. On the other hand, if your business object calls a stored proc (and the parameter list is unchanged), all you have to do is alter your procedure. No code compile, no deployment. It is all about separation of duties. Let your business objects do what they do best, be the middle man between your data storage and your front end. I am guessing you don't try to manipulate the front end from your business objects, why manipulate the data? I accept you have a different take on this and we may just have to agree to disagree with our implementation but I have to voice own opinion. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 9:33 am
Sean,
No that's fine, that's the whole point of forums. If we all went about our merry way doing things our way all the time, no one would ever innovate.
I have to admit to you, no one taught me in school what a business object was. I kinda sorta figured it out on my own. And sometimes, when programmers "figure stuff out" on their own, bad things can happen.
So you suggest that I have a front end that manipulates the data objects themselves, then call some sort of method on the data object to save the information to the database using a stored procedure? Please correct me if I am wrong, I'm always willing to learn.
October 11, 2011 at 9:43 am
pdonley (10/11/2011)
Sean,No that's fine, that's the whole point of forums. If we all went about our merry way doing things our way all the time, no one would ever innovate.
I have to admit to you, no one taught me in school what a business object was. I kinda sorta figured it out on my own. And sometimes, when programmers "figure stuff out" on their own, bad things can happen.
So you suggest that I have a front end that manipulates the data objects themselves, then call some sort of method on the data object to save the information to the database using a stored procedure? Please correct me if I am wrong, I'm always willing to learn.
No it sounds like the interaction between the front end and your business objects is solid. The business objects is where I would make the change.
Let's say you have a Customer object that currently has a method called Save(). Your front end would still instantiate the Customer and call the Save method. The contents of the Save method is where I suggest you change. Keep any validation etc in the Save method but instead of building a pass through query, call a SaveCustomer stored proc.
Does that make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 9:47 am
Yes, that does make sense. So the saveCustomer stored procedure, then, would accept, as parameters, the data fields of the Customer object, and then craft its own UPDATE or INSERT query, depending on what I'm doing, and execute that database-server-side?
October 11, 2011 at 9:51 am
pdonley (10/11/2011)
toddasd, I believe you are correct. I must have been using the wrong term. I apologize. What you said is exactly what I am doing.
Not a problem. It struck me as strange when you said you were building the query in a method. 😉 If we're using the same terminology, we can discuss things properly.
On the subject of logic in the front-end vs database, I fall on the database side. Although, this is major point of contention, both in my office and in the development world in general. It just makes more sense to me to keep the data manipulation code tightly coupled with the data.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 11, 2011 at 9:56 am
pdonley (10/11/2011)
Yes, that does make sense. So the saveCustomer stored procedure, then, would accept, as parameters, the data fields of the Customer object, and then craft its own UPDATE or INSERT query, depending on what I'm doing, and execute that database-server-side?
Yeah you just pass in whatever you are currently using as your parameters to your parameterized query. As Todd says it is a point of contention across the industry about where the best place for this stuff is. I think most people here will tend to lean more to the side of it belonging to the database. Having built and worked on many systems over the years from both sides I am a stern believer that data belongs to the database.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply