locked
Tables and fields as parameters RRS feed

  • Question

  • User294545519 posted

     Hi, I'm trying to use tables and table fields as parameters in my SQL function. Basically I need to replace some content in several fields throughout my database, so I'm trying to do something like this, but its not working. Any help would be much appreciated. Thanks.

     

      

    CREATE FUNCTION dbo.ReplaceFunction
    	(
    	@TableName table,
    	@FieldName field
    	)
    RETURNS TABLE
    AS
    	UPDATE @TableName Set @FieldName=REPLACE(@FieldName, 'xxxx', 'yyyy')
     
    Friday, April 17, 2009 10:11 AM

Answers

  • User-348832806 posted

     Hi,

    Instead of Function use Store procedure.

    For dynamic query in SQL or SP ...get more details 

    http://www.sommarskog.se/dynamic_sql.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 17, 2009 12:23 PM
  • User1096912014 posted

    I remember what I did, but it was a bit involved. First I used this query

    select tc1.PK_Field, col.Table_Name, col.Column_Name, col.Data_Type, col.Character_Maximum_Length from INFORMATION_SCHEMA.COLUMNS col INNER JOIN INFORMATION_SCHEMA.TABLES tab on col.Table_Name = tab.Table_Name 
    LEFT JOIN (SELECT ccu.Column_Name as PK_Field, tc.Table_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    		ON tc.CONSTRAINT_name = ccu.CONSTRAINT_name 
    WHERE CONSTRAINT_type = 'PRIMARY KEY') tc1 ON col.Table_Name = tc1.Table_Name 
    WHERE tab.Table_Type = 'BASE TABLE' AND (col.Character_Maximum_Length >=50 or col.Character_Maximum_Length = -1) order by 2,3
    to get list of tables / fields that may be affected.
    Then I imported it as CSV file. Then I wrote few little programs in VFP (Visual Foxpro - since this is the language I'm most profficient) to create a script to check every table every field based.
    The final script looked like
    PRINT 'Testing table Adult Volunteer..........'
    IF EXISTS (SELECT 1 from [Adult Volunteer] WHERE [Address] LIKE '%<script%')
        BEGIN
            PRINT 'We have malicious script in table  Adult Volunteer in column Address '
            PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
          SELECT [Address] from [Adult Volunteer] WHERE [Address] LIKE '%<script%'
         RAISERROR('Error in table  Adult Volunteer in column Address ',16,1)
         return -1        --- uncomment line below and insert the actual text (script) to replace
         --- UPDATE [Adult Volunteer] SET [Address]  = REPLACE([Address],'Malicious script','')      
    END 
    So, using some extra program I just created long scripts where every name was substituted with my table/field names combinations.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2009 1:58 PM

All replies

  • User-348832806 posted

     Hi,

    Instead of Function use Store procedure.

    For dynamic query in SQL or SP ...get more details 

    http://www.sommarskog.se/dynamic_sql.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 17, 2009 12:23 PM
  • User1096912014 posted

    In your case since you're not really returning a table you need to use a stored procedure. Also it looks like you want a dynamic SQL, which usually not a good idea. You may try instead to generate a script to create such replace command for several tables several field names.

    I wrote such scripts when our database undergone SQL injection attack to clean up fields that got malicious script inserted.

    Friday, April 17, 2009 2:08 PM
  • User294545519 posted

    In your case since you're not really returning a table you need to use a stored procedure. Also it looks like you want a dynamic SQL, which usually not a good idea. You may try instead to generate a script to create such replace command for several tables several field names.

    I wrote such scripts when our database undergone SQL injection attack to clean up fields that got malicious script inserted.

     

    Thanks for your reply Naom.

    Can you give me an example of how you did the SQL injection 'fix' stored procedure without dynamic SQL please? This sounds like exactly the right thing I'm after.

    Thanks again.

    Monday, April 20, 2009 11:01 AM
  • User1096912014 posted

    I remember what I did, but it was a bit involved. First I used this query

    select tc1.PK_Field, col.Table_Name, col.Column_Name, col.Data_Type, col.Character_Maximum_Length from INFORMATION_SCHEMA.COLUMNS col INNER JOIN INFORMATION_SCHEMA.TABLES tab on col.Table_Name = tab.Table_Name 
    LEFT JOIN (SELECT ccu.Column_Name as PK_Field, tc.Table_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
    		ON tc.CONSTRAINT_name = ccu.CONSTRAINT_name 
    WHERE CONSTRAINT_type = 'PRIMARY KEY') tc1 ON col.Table_Name = tc1.Table_Name 
    WHERE tab.Table_Type = 'BASE TABLE' AND (col.Character_Maximum_Length >=50 or col.Character_Maximum_Length = -1) order by 2,3
    to get list of tables / fields that may be affected.
    Then I imported it as CSV file. Then I wrote few little programs in VFP (Visual Foxpro - since this is the language I'm most profficient) to create a script to check every table every field based.
    The final script looked like
    PRINT 'Testing table Adult Volunteer..........'
    IF EXISTS (SELECT 1 from [Adult Volunteer] WHERE [Address] LIKE '%<script%')
        BEGIN
            PRINT 'We have malicious script in table  Adult Volunteer in column Address '
            PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
          SELECT [Address] from [Adult Volunteer] WHERE [Address] LIKE '%<script%'
         RAISERROR('Error in table  Adult Volunteer in column Address ',16,1)
         return -1        --- uncomment line below and insert the actual text (script) to replace
         --- UPDATE [Adult Volunteer] SET [Address]  = REPLACE([Address],'Malicious script','')      
    END 
    So, using some extra program I just created long scripts where every name was substituted with my table/field names combinations.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 20, 2009 1:58 PM
  • User294545519 posted

    Thanks...that seems like a very complex script to me!

    Tuesday, April 21, 2009 4:33 AM