16

I've got a situation where I need to use LINQ's ExecuteCommand method to run an insert.

Something like (simplified for purposes of this question):

object[] oParams = { Guid.NewGuid(), rec.WebMethodID };

TransLogDataContext.ExecuteCommand (
"INSERT INTO dbo.Transaction_Log (ID, WebMethodID) VALUES ({0}, {1})",
oParams);

The question is if this is SQL injection proof in the same way parameterized queries are?

The Archetypal Paul
  • 41,321
  • 20
  • 104
  • 134
Scott Marlowe
  • 7,915
  • 10
  • 45
  • 51

2 Answers2

14

Did some research, and I found this:

In my simple testing, it looks like the parameters passed in the ExecuteQuery and ExecuteCommand methods are automatically SQL encoded based on the value being supplied. So if you pass in a string with a ' character, it will automatically SQL escape it to ''. I believe a similar policy is used for other data types like DateTimes, Decimals, etc.

http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx
(You have scroll way down to find it)

This seems a little odd to me - most other .Net tools know better than to "SQL escape" anything; they use real query parameters instead.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • 8
    The commenter, ScottGu, \is more than just a random guy on the net! http://en.wikipedia.org/wiki/Scott_Guthrie – nathaniel Oct 01 '08 at 21:15
  • While this answer is correct in saying that it is safe to use `ExecuteCommand`, the reasoning does not look correct to me. First of all, it is [documented](https://learn.microsoft.com/en-us/dotnet/api/system.data.linq.datacontext.executecommand?view=netframework-4.8) that ExecuteCommand is safe. Then, using the profiler I clearly see `sp_executesql` with proper parameters arriving to the server, as opposed to "sql escaped query". This is true starting with FW3.5 which is when LINQ [first appeared](https://en.wikipedia.org/wiki/Language_Integrated_Query). – GSerg Jul 19 '19 at 10:49
0

LINQ to SQL uses exec_sql with parameters, which is much safer than concatenating into the ad-hoc query string. It should be as safe againt SQL injection as using SqlCommand and its Paramaters collection (in fact, it's probably what LINQ to SQL uses internally). Then again, how safe is that?

Lucas
  • 17,277
  • 5
  • 45
  • 40
  • SqlCommand and parameters are safe unless the stored procedure being called is manipulating strings to process with sp_exec. – DamienG Oct 02 '08 at 04:28