| Author |
Topic |
|
blocker
Yak Posting Veteran
89 Posts |
 Posted - 2010-04-19 : 05:07:02
|
| Hi Good day.I just want to ask a little help about my t-sql syntax regarding on how to update a table whete table name to update in soted in a variable that was passed from stored procedure.CREATE PROC updatetbl_cus@tblname as varcchar(20)@tblname='tbl_cust' beginUPDATE @tblname SET statusx='done' WHERE custid='00001'enderrors occur when the update is executed..cannot find @tblname..@tblname holds the table name to be updated.Pls help. thank you.! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-19 : 05:09:35
|
| you need to use dynamic sql for this. This needs to be done carefully to avoid sql injection attackes. the synatx will be likeEXEC('UPDATE ' + @tblname + ' SET statusx='done' WHERE custid=''00001'')question is why you want to do it this way?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-19 : 05:16:43
|
| I have two transactions in vb6 that access stored procedures, Instead of coding it in seperate way, I just take the tblname to be updated and pass it to stored procedures then execute the stored procedures update base on table name.Is this a risky process for update.? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-19 : 05:26:09
|
| its a risky thing as you're increasing chnaces of sql injection. If the tables are same always why not write straightforward updates for them?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-19 : 06:06:03
|
| so its safer to create an update code seperately.? the above process saves a lines of codes for me because i only forward the table name to be updated. Nut if its risky then i would use a seperate code for update. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-19 : 06:19:52
|
| yup..it isif you really want to save lines go for dynamic sql and add relevant checks to avoid injection attacks------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-19 : 06:24:46
|
| thank you very much... ive just wondered how can attackers inject sql vulnerability base on the above dynamic sql. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-19 : 06:53:04
|
quote:
Originally posted by blocker
thank you very much... ive just wondered how can attackers inject sql vulnerability base on the above dynamic sql.
Just imagine getting this string from a user:'employee SET id = Id; DROP TABLE employee; --' Assuming there is a table called employee and it has an ID column called Id and you don't validate that string then..... bye bye employees.Your EXEC statement would produceUPDATE employee SET id = Id; DROP TABLE employee; -- SET statusx='done' WHERE custid='00001' Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-19 : 09:25:18
|
Its common to have a separate Sproc for each table doing pretty much the same thing. (We create ours mechanically, rather than writing them by hand  )This may seem daft at first sight, but there are several benefits:Each has its own, optimised, query plan.Each has its own permissions - so you could grant Execute Permission on the Sproc for Update on TableA to Group-1, and the Sproc on Update to TableB to Group-1 and also Group-2If the "Update" logic needs to change for a table then the logic is isolated to a single Sproc. Lets say you decide you need Auditing on one of your tables; you can change that SProc to save the update changes into another "audit" table whilst it makes the actual update; or maybe you want to put some validation that salary can't be increased by more than 10% (except for your own  ), that extra logic can go in the SProc for updating that table. No changes to your application code, so easy to deploy (less testing to do than deploying an application which will contain all sorts of potential changes within it) |
|
|
|
blocker
Yak Posting Veteran
89 Posts |
Posted - 2010-04-19 : 20:55:56
|
| thank you very much fo the explanation. Ill try to put some validation on the sql instead.. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-04-20 : 12:02:45
|
| >> how to update a table where the table name to update in stored in a variable that was passed from stored procedure. <<Please read any book on **basic** software engineering. Look at the chapter on "cohesion in modules" and stop programming until you understand it. This is a more universal principle than just SQL programming. We want high cohesion in a module. That means it does one thing and only one thing. The best cohesion is functional cohesion -- think of mathematical functions; deterministic and predicable. What you have is one of the worst way to write ANY software. Your update module is dependent on a containing module! Here, it works on automobiles! There it works on squids! Oh, you might also want to also read a book on **basic** data modeling so you will stop using that silly "tbl-" prefix in violation of ISO-11179 rules.What SQL programmers do is write declarative code in the DDL. That means we have a lot of "ON UPDATE CASCADE" and "ON DELETE CASCADE" clauses on referencing columns. --CELKO--Joe Celko, SQL Guru |
|
|
|
|
Table name to update in variable.