programmatically load and execute sql script

  • Hello:

    Is it possible to get SQL Analyzer to load and execute a sql script from a text file stored on local machine?

    For example, I have a file ( c:\showproducts.sql ) which contains the following contents:

    <-- start script -->

    USE NORTHIND

    select productID from products where productid < 4

    <-- end script -->

    Is it possible to get SQL Analyzer to look for that file on c:\ and execute the script so that I get:

    productID

    -----------

    1

    2

    3

    Thanks in advance

    Billy

  • You might try something like the script below. Hope this is what you where looking for:

    declare @file_size int

    Create table #Foo

    (

    Alternate_Name char(20),

    Size char(20),

    Creation_Date char(20),

    Creation_Time char(20),

    Last_Written_Date char(20),

    Last_Written_Time char(20),

    Last_Accessed_Date char(20),

    Last_Accessed_Time char(20),

    Attributes char(20)

    )

    Insert Into #Foo

    EXEC Master..xp_getfiledetails 'c:\showproducts.sql'

    Select @file_size= cast(Size as numeric(20,0))

    From #Foo

    drop table #foo

    if @file_size > 0

    exec master.dbo.xp_cmdshell 'osql -E -SEsp--gal0303 -ic:\showproducts.sql'

    else

    print 'script does not exist'

    -------------------------

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • be very very careful with this. Like dynamic sql, this is asking for an injection attack.

    Is there a reason not to use isql or osql?

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • Steve,

    > Like dynamic sql, this is asking for an injection attack.

    Can you explain what that means?

    Cheers,

    Mike.

  • Ok imagine you wrote an Proc this way.

    CREATE PROCEDURE ip_getauthors

    @col varchar(50),

    @keyword VARCHAR(1000) --Did this because some keyword sets are large.

    AS

    SET NOCOUNT ON

    EXECUTE ('SELECT * FROM tbl_Authors WHERE [' + @col + '] = ''' + @keyword + '''')

    GO

    Now some jerk comes along (hacker or otherwise) and he has figured out the fact that the second input item on the app or web page is valued = to his column selection. He might try the following type of attack which is an injection.

    He chooses column First Name and for the keyword entry he enters

    JIM'' GO EXEC sp_MSForEachDb ''DROP DATABASE ?'' GO SELECT ''HA HA HA HA!!!!!

    So when execute runs the dynamic stuff this is what it reads.

    SELECT * FROM tbl_Authors WHERE [FNAME] = 'JIM' GO EXEC sp_MSForEachDb 'DROP DATABASE ?' GO SELECT 'HA HA HA HA!!!!!'

    Now he has blown away all your databases including I do believe system databases, your site is gone until a backup restore. This is why you have to be carefull with Dynamic SQL.

    Edited by - antares686 on 09/05/2002 04:46:20 AM

  • Injection attacks are a possibility, but remember that Exec() still only works with permissions given to the login. Typically I grant only execute permissions on procs to standard users, when I need Exec() it's usally for searches so I grant select on the tables that may be included in the search. That restricts the damage that can be done. You could also add some additional security by checking for the ';' separator if you know you don't normally include it and removing/reporting it.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Thanks Gregory, Steve and all for your responses.

    If I could get SQL Analyzer to execute those scripts, then it would make life a lot simpler because I can access my library of scripts in a flash. There probably won't be much of an opportunity for an injection attack because I won't be saving the sql scripts that load and execute the other sql scripts.

    Billy

  • I am working on some problems with dynamic SQL and came accross your discussion that involved dynamic SQL:

    CREATE PROCEDURE ip_getauthors

    @col varchar(50),

    @keyword VARCHAR(1000) --Did this because some keyword sets are large.

    AS

    SET NOCOUNT ON

    sp_executesql N'SELECT * FROM tbl_Authors WHERE [' @col '] = @keyword )',N' @keyword varchar(1000)', @keyword

    GO

    The code above is dynamic but does not have described volnurability. It will take a fortune for the 'jerk' to realize the structure of SQL to supply any harmful values to @col. Plus in most cases @col will be supplied by application while @keyword will be likely supplied by the end-user.

     
  • Hi,

    Please ignore the reply if its irrelevant.

    Use the command below on the command prompt

    isqlw -S [server] -d database -U [sa] -P [sa] -i c:\a.sql -o c:\a.txt

    OR

    osql -S qaserver -d Master -U sa -P sa -i c:\a.sql -o c:\a.txt

    These commands will open the sql file located at c:\ and stores the output in a text file.

    I think this relates to what you are looking for, you can do it programatically.

    I hope this helps

    Kind Regards

    Affan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply