CODEWITHSUNDEEP
.netsqlvb.net
Geoffrey
Geoffrey

Reputation: 976

Parametrized query as String

In my project I need to log all queries executed against my database. As an example we can use the staff user data here. In that class I have a function generating the command with the parameters as follows:

Public Function SQLUpdate(ByVal conn As SqlClient.SqlConnection) As SqlClient.SqlCommand Implements IDbConnected.SQLUpdate
    Dim sqlstatement As String = "UPDATE Persons SET Active=@act, Abbreviation=@abbr, FirstName=@first, LastName=@last, " & _
                                 "Birthday=@bday, Email=@email,Tel=@tel, Fax=@fax, Registered=@reg, Admin=@adm"
    sqlstatement &= " WHERE ID=" & Me.ID
    Dim comm As New SqlClient.SqlCommand(sqlstatement, conn)
    With comm.Parameters
        .Add("@act", SqlDbType.Bit).Value = Me.Active
        .Add("@abbr", SqlDbType.VarChar).Value = Me.Abbreviation
        .Add("@first", SqlDbType.VarChar).Value = Me.FirstName
        .Add("@last", SqlDbType.VarChar).Value = Me.LastName
        .Add("@bday", SqlDbType.SmallDateTime).Value = Me.Birthday
        .Add("@email", SqlDbType.VarChar).Value = Me.Email
        .Add("@tel", SqlDbType.VarChar).Value = Me.Telephone
        .Add("@fax", SqlDbType.VarChar).Value = Me.Fax
        .Add("@reg", SqlDbType.Bit).Value = Me.Registered
        .Add("@adm", SqlDbType.Bit).Value = Me.Administrator
    End With
    Return comm
End Function

When I request the command text

comm.CommandText

then I get still the parametrizid query

UPDATE Persons SET Active=@act, Abbreviation=@abbr, FirstName=@first, LastName=@last, Birthday=@bday, Email=@email,Tel=@tel, Fax=@fax, Registered=@reg, Admin=@adm WHERE ID=2

off course I need the query where the parameters are replaced by the values. Is there an easy way to do this or do I need to program a function that does the replacements itsself?

Upvotes: 0

Views: 164

Answers (3)

Schenz
Schenz

Reputation: 1113

You will have to write your own function to get what you are looking for. What is actually sent to the server is the parametrized query with a collection of parameter objects, and SQL Server does all the work there.

A second option would be to run SQL Profiler on the server to see the commands being executed there.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

The string you're looking for doesn't exist. At no point does the .Net run time or sql server ever substitute your values directly into the query text. It just doesn't happen. This is the whole point of parameterized queries; keep the (potentially dangerous) data parameters separate from the code.

If you want this string, you'll have to build it yourself; the act of building the string should demonstrate why this is wrong: Think about what the code to substitute in the values would look like, and then imagine an angry user enters the value ';DROP TABLE Persons;-- in for an abbreviation or first name. Think about what string you would create and what would happen if that string were executed directly by the server.

If you really want to log the query, the actual command uses sp_executesql behind the scenes, but you can think of it as if it runs a script that's more like this:

DECLARE @act bit; Set @act = (value from client)
DECLARE @abbr VARCHAR(10); Set @abbr = (value from client)
DECLARE @first VARCHAR(30); Set @first = (value from client)
--...
UPDATE Persons SET Active=@act, Abbreviation=@abbr, FirstName=@first

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062530

The query goes down to the server with the parameters as parameters (which helps security and query plan re-use). So there is no need for what you ask to exist - and thus it doesn't.

Personally I wouldn't replace them, even when logging; I would simply append the name/value pairs when logging it, i.e. log something like:

UPDATE Persons SET Active=@act, Abbreviation=@abbr, FirstName=@first, LastName=@last, Birthday=@bday, Email=@email,Tel=@tel, Fax=@fax, Registered=@reg, Admin=@adm WHERE ID=2 || @act=1 @abbr=mjg @first=Fred ...

Upvotes: 7

Related Questions