September 4, 2002 at 1:37 pm
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
September 4, 2002 at 2:11 pm
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
September 4, 2002 at 2:22 pm
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
September 5, 2002 at 1:36 am
Steve,
> Like dynamic sql, this is asking for an injection attack.
Can you explain what that means?
Cheers,
Mike.
September 5, 2002 at 4:18 am
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
September 5, 2002 at 4:57 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
September 6, 2002 at 12:08 pm
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
September 17, 2002 at 3:29 pm
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.
September 19, 2002 at 7:25 am
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