How can I view the final query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How can I view the final query?

Author

Topic

Ogriv
Starting Member

40 Posts

Posted - 2011-11-08 : 13:46:01

Greetings,

I have a query that utilizes variables. For auditing, I want to save a copy of the final query to an audit log.

I tried this:

SET @Query1 = 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (@PatientURN,@DateOfCharge,@TimeOfCharge,@ChargeCode,@SubmittedByMnemonic,@NO,@DateTimeStamp,@NO)'

and this:

SET @Query1 = 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (' + @PatientURN + ',' + @DateOfCharge + ',' + @TimeOfCharge + ',' + @ChargeCode + ',' + @SubmittedByMnemonic + ',' + @NO + ',' + @DateTimeStamp + ',' + @NO + ')'

But I get the same result:

INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (@PatientURN,@DateOfCharge,@TimeOfCharge,@ChargeCode,@SubmittedByMnemonic,@NO,@DateTimeStamp,@NO)

I want the string to look like this:

INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES ('9876543210','2011-10-24','13:50','56423','ISMJB','N','2011-10-25 08:30:00','N')

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-08 : 13:57:14

why would you do that ever?

Just do an


INSERT <table> (columns)
VALUES ( LIST OF VARIABLES )

Then you get types safety and avoid all the possible problems of dynamic sql -- the way you are doing it right now leaves you wide open to sql injection attack

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ogriv
Starting Member

40 Posts

Posted - 2011-11-08 : 14:08:24

The query to save the final query is in a stored procedure which is called from other stored
procedures. I wanted to document the query and it's actual values as it was submitted. How am
I open to injection attack, if I'm just storing the query into a field as a varchar().

I suppose I'm trying to get a result like PRINT would give. But instead of output to the screen, I want to assign the output to a variable.


If I can do this:
PRINT 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (' & @PatientURN & ',' + @DateOfCharge + ',' + @TimeOfCharge + ',' + @ChargeCode + ',' + @SubmittedByMnemonic + ',' + @NO + ',' + @DateTimeStamp + ',' + @NO + ')'

How can I do this: (I know this is completely wrong, but I'm just trying to clarify what result I am looking for)


DECLARE @SomeVariable varchar(100)
@SomeVariable = PRINT 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (' & @PatientURN & ',' + @DateOfCharge + ',' + @TimeOfCharge + ',' + @ChargeCode + ',' + @SubmittedByMnemonic + ',' + @NO + ',' + @DateTimeStamp + ',' + @NO + ')'

Thanks,

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-11-08 : 18:13:24

Try This

declare @PatientURN varchar(20),@DateOfCharge varchar(20),@TimeOfCharge varchar(20),
@ChargeCode varchar(20),@SubmittedByMnemonic varchar(20),@NO varchar(20),@DateTimeStamp varchar(20)
set @PatientURN = '9876543210'--(,'2011-10-24','13:50','56423','ISMJB','N','2011-10-25 08:30:00','N')
set @DateOfCharge = '2011-10-24'
set @TimeOfCharge = '13:50'
set @ChargeCode = '56423'
set @SubmittedByMnemonic ='ISMJB'
set @NO = 'N'
set @DateTimeStamp = '2011-10-25 08:30:00'
declare @Query1 varchar(2000)

SET @Query1 =
'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser
,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)
VALUES (''' + @PatientURN + ''', ''' +@DateOfCharge + ''',''' + @TimeOfCharge + ''',''' + @ChargeCode + ''',''' + @SubmittedByMnemonic + ''',''' + @NO + ''',''' + @DateTimeStamp + ''',''' + @NO + ''')'

print @Query1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:11:12

quote:
Originally posted by Ogriv

The query to save the final query is in a stored procedure which is called from other stored
procedures. I wanted to document the query and it's actual values as it was submitted. How am
I open to injection attack, if I'm just storing the query into a field as a varchar().

I suppose I'm trying to get a result like PRINT would give. But instead of output to the screen, I want to assign the output to a variable.

If I can do this:
PRINT 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (' & @PatientURN & ',' + @DateOfCharge + ',' + @TimeOfCharge + ',' + @ChargeCode + ',' + @SubmittedByMnemonic + ',' + @NO + ',' + @DateTimeStamp + ',' + @NO + ')'

How can I do this: (I know this is completely wrong, but I'm just trying to clarify what result I am looking for)

DECLARE @SomeVariable varchar(100)
@SomeVariable = PRINT 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (' & @PatientURN & ',' + @DateOfCharge + ',' + @TimeOfCharge + ',' + @ChargeCode + ',' + @SubmittedByMnemonic + ',' + @NO + ',' + @DateTimeStamp + ',' + @NO + ')'

Thanks,

make sure you read this

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 01:13:37

also one thing i noticed

as per your requirement


SET @Query1 = 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (' + @PatientURN + ',' + @DateOfCharge + ',' + @TimeOfCharge + ',' + @ChargeCode + ',' + @SubmittedByMnemonic + ',' + @NO + ',' + @DateTimeStamp + ',' + @NO + ')'

should be

SET @Query1 = 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (''' + @PatientURN + ''',''' + @DateOfCharge + ''',''' + @TimeOfCharge + ''',''' + @ChargeCode + ''',''' + @SubmittedByMnemonic + ''',''' + @NO + ''',''' + @DateTimeStamp + ''',''' + @NO + ''')'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ogriv
Starting Member

40 Posts

Posted - 2011-11-09 : 08:21:13

I thank you for your response and I will definitely read the document you linked. But I think my request may be misunderstood.
I am trying to save the complete query (A query that has all of the variable values populated) to a variable which would get written to an "audit trail log" of sorts. Much like the PRINT result would have it's variables populated.

The query will not be run from this variable, it is simply going to be stored as a record of what exact query was run on that date and time.


DECLARE @SomeVariable varchar(100)
@SomeVariable = 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES ('9876543210','2011-10-24','13:50','56423','ISMJB','N','2011-10-25 08:30:00','N')'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 09:03:21

quote:
Originally posted by Ogriv

I thank you for your response and I will definitely read the document you linked. But I think my request may be misunderstood.
I am trying to save the complete query (A query that has all of the variable values populated) to a variable which would get written to an "audit trail log" of sorts. Much like the PRINT result would have it's variables populated.

The query will not be run from this variable, it is simply going to be stored as a record of what exact query was run on that date and time.

DECLARE @SomeVariable varchar(100)
@SomeVariable = 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)VALUES (''9876543210'',''2011-10-24'',''13:50'',''56423'',''ISMJB'',''N'',''2011-10-25 08:30:00'',''N'')'

in above case also you need to add ' s to escape ' within string values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ogriv
Starting Member

40 Posts

Posted - 2011-11-09 : 10:08:55

I do apologize, I think we are on different pages. Let me try again.

Is there a way to get the @SomeVariable populated with the actual value of the passed parameters?

This is what I would do in VB to get the result I am looking for: (Reduced the variable list to simplify)


DIM URNvalue AS String = "9876543210"
DIM DateValue AS String = "2011-10-24"
DIM SomeVariable As String = ""

SomeVariable = "INSERT INTO PendingCharges (URN,DateOfCharge)VALUES ('" & URNValue & "','" & DateValue & "')"

So I could then use SomeVariable anywhere I wanted to display it and it would contain the actual values of the passed parameters.

The value of SomeVariable would be:

INSERT INTO PendingCharges (URN,DateOfCharge)VALUES ('9876543210','2011-10-24')

I know you can print the variable in SQL and it will display the query correctly populated with the value of all of the variables.
So is there a function that will "print" the query to a variable?

Perhaps there is not a way to do this in SQL?

Kristen
Test

22859 Posts

Posted - 2011-11-09 : 15:04:04

"So is there a function that will "print" the query to a variable?"

That's what folk have been telling you. You are using pseudo-code from another language, such as BASIC.

In SQL you can't use " to delimit a string, you have to use '

This means that to create a string which itself contains ' you have to mess about a bit.

If you do


SomeVariable = "INSERT INTO PendingCharges (URN,DateOfCharge)VALUES ('" & URNValue & "' ...

then what happens if the value of URNValue happens to be:


xxx', 'yyy'); DROP DATABASE zzz; '

????

If URNValue is a field that contains data that USERS enter, rather than some internal-use-only trusted and sanitised data then some hacker can, and will, have a go at those type of commands.

If that happens the "insert"

command you will wind up with is:


INSERT INTO PendingCharges (URN,DateOfCharge)VALUES ('xxx', 'yyy'); DROP DATABASE zzz; ' ... '

which will insert one row and then DROP the database !!!!!!!!!!

That's why protecting against SQL Injection is important.

Ogriv
Starting Member

40 Posts

Posted - 2011-11-09 : 15:41:36

I must be missing something. I'll reread the responses

Kristen
Test

22859 Posts

Posted - 2011-11-09 : 15:47:12

I'll paraphrase:

Using your code you could (but might not) wind up executing code like:


INSERT INTO PendingCharges (URN,DateOfCharge)VALUES ('xxx', 'yyy'); DROP DATABASE zzz; ' ... '

which will result in a database being dropped. That's the thing that you need to be constantly guarding against as you write SQL code which generates "self executing" code; so-called "SQL Injection"

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 16:19:15

Is there a new DBA position opening up somewhere?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Ogriv
Starting Member

40 Posts

Posted - 2011-11-09 : 21:12:01

hehe cute comment

I understand what you all are saying regarding SQL injection. But we have a scrubber on the values passed to the SP so injection is not the issue. I just wanted to create an audit log of the query that was executed. Just trying to save a string that shows the actual values that were included in the query.

Thank you for the constructive responses

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:19:57

oK..WHY are you not using stored procedure access ONLY

EEVERY Sproc that I write, on the exit records the excution of that sproc, the time it started, the time it ended, WHO called the sproc, what the HOST it ran from, and a whole bunch of other stuff.

IF you want to do what you are trying to do..(FROM the FRON END??)..then just set the SQL You are going to execut as a literal and insdert into a table before you execute

I can tell you right NOW..that that will get out of synch quickly when developers don't update the "query" twice every time theres a change, or because they don't understand your "best practices"

I don't think I read WHY you want to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:21:30

OH, wait, you want to see waht the people used as paramters so you can debug what they are doing because USERS LIE or are very confused, or misinformed, or the sign your paycheck..right?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 21:23:50

I do exacly that..for exactly the reason to say..hey..suzieQ..you called tis sproc at theis exact tim and day, and you passedd in these values..Usually there is silence on the phone

I also do that with data changes with TRIGGERS..

"You chnage the data at 3:00, your boss went in at 3:01, that why"...silence

I'll post the code tomorrow..BUT you will have to be using sprocs

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Ogriv
Starting Member

40 Posts

Posted - 2011-11-09 : 22:30:06

Exactly

Ogriv
Starting Member

40 Posts

Posted - 2011-11-09 : 22:35:44

Yes! That is what I have been trying to say. I don't think my users actually lie, it I don't think they actually remember exactly what they did.
This logging would give me a record of the actual query, with populated values, that was processed. It is really just for my benefit,
but it never hurts to have more data than you need when troubleshooting.

I''m very sorry for not being more descriptive from the beginning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 00:34:46

quote:
Originally posted by Ogriv

Yes! That is what I have been trying to say. I don't think my users actually lie, it I don't think they actually remember exactly what they did.
This logging would give me a record of the actual query, with populated values, that was processed. It is really just for my benefit,
but it never hurts to have more data than you need when troubleshooting.

I''m very sorry for not being more descriptive from the beginning.

isnt it just matter of capturing executed statements using a profiler trace then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-11-10 : 03:25:38

"But we have a scrubber on the values passed to the SP so injection is not the issue"

Whilst I'm not against that I would respectfully say that you won't know you've been hit until you have been hit. Defensive programming would, IMHO, be a better policy - i.e. never trust data that came from an external source.

What you are trying to achieve is probably this:


DECLARE @SomeVariable varchar(100)
SELECT @SomeVariable = 'INSERT INTO PendingCharges (URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag)'
              + 'VALUES (' + COALESCE('''' + REPLACE(@PatientURN, '''', '''''') + '''', 'NULL')
              + ',' + COALESCE('''' + CONVERT(varchar(30), @DateOfCharge) + '''', 'NULL')
              + ',' + COALESCE(CONVERT(varchar(20), @TimeOfCharge), 'NULL')	-- Assuming it is INT
               + ',' + COALESCE('''' + REPLACE(@ChargeCode, '''', '''''') + '''', 'NULL')
etc
EXEC (@SomeVariable)

Note that "100" is not wide enough for @SomeVariable. I would suggest you write a function to do the REPLACE, Single-quote wrapping, and COALESCE to NULL so that you can wrap all string's that you concatenate using that.

Or write an SProc:


CREATE PROCEDURE MyProcName
    @PatientURN varchar(9999),
    @DateOfCharge datetime,
    @TimeOfCharge int,
    @ChargeCode varchar(9999),
... etc ...
AS
    INSERT INTO PendingCharges
    (
        URN,DateOfCharge,TimeOfCharge,ChargeCode,SubmittedByUser,ChargeCompleteFlag,DateTimeStamp,HasBeenEditedFlag
    ) VALUES
    (
        @PatientURN,
        @DateOfCharge,
        @TimeOfCharge,
        @ChargeCode,
... etc ...
    )

and then you just do


EXEC MyProcName @PatientURN = @PatientURN, @DateOfCharge = @DateOfCharge, @TimeOfCharge = @TimeOfCharge, ... etc ...

Another thought: for this type of "parameter log" we just concatenate the values and insert that concatenated-value into a log table, which also has a "source" uniquely indicating where in the program the action took place:


SELECT @SomeVariable = COALESCE(@PatientURN, '[NULL]')
              + '~' + COALESCE(CONVERT(varchar(30), @DateOfCharge), '[NULL]')
              + '~' + COALESCE(CONVERT(varchar(20), @TimeOfCharge), '[NULL]')
               + '~' + COALESCE(@ChargeCode, '[NULL]')
INSERT INTO MyParameterLogTable(Source, Parameters) VALUES('PendingCharges INSERT', @SomeVariable)

We use tilde "~" delimiter as it is less likely to occur in the data than, say, a comma

There is no issue of whether there is SQL Injection, or any other "rogue" character, as we just store the raw data (but you do need to converted NULL values to something - e.g. "[NULL]")

But to provide a history of exactly how the data changed we use triggers. The "Before" for every row that is UPDATED or DELETED is stored in an Audit / History table. (The "After" is in the row itself, so we don't store that).

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources