Insert SQL statement into a table
-
Arthur, The submission process would need to have the ability to assure a properly formed argument. Is the interface a web page using an ADO object? Is it an internal application? Does the submitting process create a string or create parameters to a stored proc ADO object?
My understanding is that if you are using an ADO Command object (sorry - I;m in c# using SqlCommand, but I think it is similar), you can say <parameter>.value = <some string>. I believe that this may handle the quotes for you, but haven't tested it recently.
If the process puts it into the commandText - the process will need to replace each single quote with two quotes.
Guarddata-
-
First of all, many thanks for you guys response. I appreciate it. The basic requirements for my sp is simple, it must be able to store (temporary) in a table for a user's custom sql statement(s). User can execute it/them later - if more than one query, we assume that user wants to UNION the statements. I am allowed to read the VB source code because it is not my department. Therefore, I have no idea what exactly the VB handling. However, I have seen part of the VB code before in elsewhere; I remember that the VB developer is using ADO objects for the project. I discussed my problem with our Oracle Artchitect a while ago, he told me that I am in the right track. Even though I don't like the way I solved this problem but it works fine so far. Again, I am willing to learn a better solution for this issue. Does it make sense now?
Thanks,
AC
-
Yep - makes sense. I really don't think there is any problem storing the query as long as they can properly call the stored procedure. The burden is really on the submission.
Guarddata-
-
Hopefully I am on the right track. Had a similar problem last week. Solution was to replace all the single quotes in the data with two single qoutes. Problem was product names with single quotes as part of the product name. Includes an example.
Create Table Tmp(Company_Id int IDENTITY(1,1),Company_Name Varchar(100))
GO
Insert Tmp
Select 'Company 1'
Insert Tmp
Select 'Company ''1' -- Application supplied Company '1 but sp replace any single quotes with two single quotes
Insert Tmp
Select 'Company ''1''' -- Aplication supplied Company '1' but sp replace any single quotes with two single quotes
GO
Select * from Tmp
GO
Drop Table Tmp
GO
Result from Select * which is what the user eventually will receive/see
1Company 1
2Company '1
3Company '1' -
-- Part II User commands
-- User supply VB / ASP with "Select * from Tmp Where Company_Name='Company ''1'''"
Set QUOTED_IDENTIFIER off -- More readable
Insert Tmp
Select "Select * from Tmp Where Company_Name='Company ''1'''"
Set QUOTED_IDENTIFIER on
GO
Declare @c Varchar(1000)
Select @c=Company_Name From Tmp Where Company_Id=4
Select 'USER SUPPLIED SQL STATEMENT',@c
Exec (@c)
Go
Drop Table Tmp
GO -
Hi
You can use sp_excutesql sp to create a dynamic sql
e.g sp_executesql(@sqlstmt)
note : @sqlstmt should be nvarchar(4000) maximum.
Another way is execute method.
e.g. exec (@sqlstr)
where @sqlstr contains the any executable select statement.
-
Well this is maybe a very stupid reply but maybe it isn't......
1. Pass the sql-query to a stored procedure as a parameter, the quotes issue should be solved then.
2. Dont try to execute the sql-query in SQL itself instead let the VB-Client execute the string........
i.m.h.o this should always work. no problem with quotes etc........
-
Of course, it is not a stupid suggestion but I have the basic requirements for my sp. The app pass in a sql statement and my sp has to handle the rest. Therefore, your solution does not applicable for my case. Well, so far I can passed the test from the VB developer. He will inform me if anything is broken. Again, many thanks for you guys helpful information and suggestion. I truely appreciate it.
Many thanks,
AC
-
Can you post a sample of what the App sends to the database? That might clear things up for us...
-
Well, I did mention the simple sql statement that passed in from the app interface before. Anyhow, here is the statement look like --> 'SELECT * FROM abc WHERE name LIKE 'A%''. Note, the single quotes are added in the beginning and at the end of the string when the VB inteface pass in the string value to my sp. Again, it is just a simple statement and there is no control for what the user created, the sp has to accept any query even it is not runnable and it will only trigger an error when it actually execute. I worked around with this problem by requesting the VB Developer replaced all the single quote into a rarely used character before passing into my sp and I replace it back to a single quote before the statement(s) execute. So far, it works fine. That's it! Any suggestion?
AC
-
Here is a quick sample of something that might work for you - it can be adjusted however you need. Hope it helps.
Guarddata-
--DROP table sqlReady
create table sqlReady (
cmdID INT IDENTITY(1,1), --Provide an easy ID
ProcOn DATETIME NULL, --Date on which to execute the command
sqlCmd VARCHAR(4000) NOT NULL, --Command to execute
execed DATETIME NULL --Date command was executed
)
GO
--DROP PROCEDURE PrepareSQLStatement
CREATE PROCEDURE PrepareSQLStatement (
@sqlCmd VARCHAR(4000), --Command to execute
@ProcOn DATETIME = NULL --Date on which to execute the command
) AS
INSERT INTO sqlReady ( ProcOn, sqlCmd )
VALUES ( @ProcOn, @sqlCmd )
RETURN 0
GO
EXEC PrepareSQLStatement @sqlCmd = 'SELECT * FROM authors (NOLOCK) WHERE au_fname LIKE ''j%'''
GO
--select * from sqlReady
--GO
-- SELECT cmdID, sqlCmd FROM sqlReady
-- WHERE ISNULL( ProcOn, GETDATE() - 1 ) < GETDATE()
--DROP PROCEDURE RunPreparedProcs
CREATE PROCEDURE RunPreparedProcs (
@UpToDate DATETIME = NULL
) AS
DECLARE
@toExec NVARCHAR(4000), --To read the command
@procID INT --ID of executed command
SELECT @UpToDate = ISNULL( @UpToDate, GETDATE() )
DECLARE exeCurs CURSOR FOR
SELECT cmdID, sqlCmd FROM sqlReady
WHERE ISNULL( ProcOn, GETDATE() - 1 ) < @UpToDate
OPEN exeCurs
FETCH NEXT FROM exeCurs INTO @procID, @toExec
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_executesql @toExec
UPDATE sqlReady SET execed = GETDATE()
WHERE cmdID = @procID
FETCH NEXT FROM exeCurs INTO @procID, @toExec
END
CLOSE exeCurs
DEALLOCATE exeCurs
RETURN 0
GO
EXEC RunPreparedProcs
Oops - forgot to mention that this is meant to run in the pubs database.
Edited by - guarddata on 08/25/2003 10:55:37 AM
-
My point wasn't as much which query the app passes in, but the method it uses to pass it in. Does it call a stored procedure using the ADOCommand object adding parameters? Or does it parse a complete string and executes that using the ADOConnection.Execute method?
In the first case, there is no need for VB nor for you to worry about the quotes. You can insert the SQLStatement as it is passed into the procedure. To execute use
CREATE ExecuteUserProcedure
<add the selectioncriteria like user, ...>
AS
DECLARE @strSQL as varchar(4000)
SELECT @strSQL = SQLStatement FROM SQLTable WHERE <your selection criteria>
EXEC @strSQL
--or use sp_executesql @strSQLIn my opinion, this is by far the most preferable solution, since you don't have to worry about the quotes and stuff. Removes the possibilitiy of SQLInjection when inserting.
(Still leaves sql injection open when executing the query that a user adds, obviously.)
-
That is actually my original design of my sp and it is still the design but added replace function before execute the sql statement(s). Again, I have no idea how the VB interface method that is using. The VB Developer reported to me the bug and gave me the value collected from his VB interface. The only thing could fix the bug is using his provide value to adjust my sp handling. I know it is not a good way but it is the organization structure. What can I do?
AC
-
If any SQL statement would be valid....
Would an Exec or sp_executesql be valid as well.......
-
In principal yes, if the quotes are handled correctly
Select count(*)
from sysobjects
where name like 's%'
go
Exec ('Select count(*)
from sysobjects
where name like ''s%''')
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply