Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jauch
Starting Member
8 Posts |
 Posted - 2008-02-12 : 07:39:11
|
Hi!I read about SQL injection and etc, when using sp_ExecuteSQL and EXEC.I´d like to use them to do something like this:CREATE PROCEDURE f0AS-- some code that put values in @v1 and @V2...IF @v1 = 1 EXEC f1 ('DB1..Tabela1', @V2)ELSE EXEC f1 ('DB2..Tabela1', @V2)GOCREATE PROCEDURE f1 (@table_name nvarchar(50), @valor int)ASEXEC ('INSERT INTO '+@table_name+' (valor) VALUES ('+@valor+')')GOIs there any security problem to do this type of code?Even if the @table_name and @valor are determined by the program?In case of yes, how can I do something like this?Thanks in advance  |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-12 : 09:15:13
|
| why would you have to use dynamic sql like that?if you really must have dynamic sql then just change all ' to '', or use properly parametrized sql and you'll be ok._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
 |
|
|
jauch
Starting Member
8 Posts |
Posted - 2008-02-12 : 11:33:39
|
| Hum...Because we have to do integration between one data base to a variable number of other databases. The number of databases is fixed for each client.For exemple:A Client have a database (on application A) with on column on a table that can have 3 values.When a row is inserted, depending on the values, I need to start a trigger that will put the data on the other columns on the sabe table into a new record on one of the other tree databases (On application B).Because this number of database can change from client to client, today we use to do this in this way:INSERT -> Trigger F1F1 verify what insert (stored procedure) to call depending on a value.But the NAMES of the database can change from client to client too. So, I need to "re-write" the trigger stored and all the other insert (stored procedure) for each database.If I can use something like I post first, All I need to change is the Trigger Stored, because the "insert function" will be one "generic"This was (for now) the only way I figured out.But I have doubts on the "security issues" with dynamic sql...If there is a beeter way to lower the risks of error and the work of re-write many lines of sql code every time we go to a client, I'll be very happy to hear Thanks! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-12 : 12:04:06
|
| replication maybe?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
jauch
Starting Member
8 Posts |
Posted - 2008-02-12 : 12:42:41
|
| Maybe...But seens to me that the overwork of change the database name on all the sotred procedures (insted only on one trigger) will continue to exist...I'll study Replication to see if ther is a way to do something like what I was thinking, but that don't use dynamic SQL.Thanks. |
|
|
|
|
|
|
|
|
Security issues of sp_ExecuteSQL and EXEC