Getting "final" prepared statement from MySqlCommand

Question

I have the following MySqlCommand:

Dim cmd As New MySqlCommand
cmd.CommandText = "REPLACE INTO `customer` VALUES( ?customerID, ?firstName, ?lastName)"

With cmd.Parameters
 .AddWithValue("?customerID", m_CustomerID)
 .AddWithValue("?firstName", m_FirstName)
 .AddWithValue("?lastName", m_LastName)
End With

I have a class that handles execution of MySqlCommands and I'd like to have it log every query to a file. I can retrieve the query/command being executed with:

cmd.CommandText

but that just returns the original CommandText with the parameters (?customerID, ?firstName, etc.) and not the actual substituted values added by the AddWithValue functions. How can I find out the actual "final" query that was executed?

Answer 1

You would have to build it yourself.

Parameters are not just plopped into a string and then run as a SQL statement. The RDBMS will actually prepare the SQL and then use the parameter values as needed. Therefore, there's not a single SQL statement going to the server. To see what the SQL would be, you would have to do:

Console.WriteLine("REPLACE INTO `customer` VALUES('" & m_CustomerID & _
    "', '" & m_FirstName & "', '" & m_LastName & "')")

Answer 2

I havn't seen a method for this.

And in any case, prepared statements are sent to the server with the ?customerID,?firstname parameters, and then the actual parameters are sent seperately - the mysql driver doesn't build up a final sql query like you'd do if you didn't use prepared statements.

Answer 3

The parameterised method you're using should be okay for preventing SQL injection.

.AddWithValue("?customerID", m_CustomerID)

If m_CustomerID contains the text

Haha I'm stealing your data; drop table whatever;

Then it won't end up being executed on the server as such. The AddWithValue sorts that out for you.

As for the actual executed query, you should be able to get that from the query-log, if it's enabled.