Prevent the sql injection by using LINQ to SQL

Question

I'm a new in the world of coding,

I built a large web site with several textboxes, so now i figure out that I've been using a dangerous method of inserting data in the SQL server by some thing like this:

 execSQL("insert into Dossier(ID_Dossier,Nom_Giac) values(" & id_dossier.text & "," Nom_gaic.text & "')")

     Public Function execSQL(ByVal req As String, Optional ByVal type As String = "r")
            cmd = New SqlCommand
            cmd.CommandText = req
            cmd.Connection = con
            openCon()
            If type = "r" Then
                Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Else
                Return cmd.ExecuteNonQuery
            End If

        End Function

I just want to know if Using LINQ to SQL can help solve this problem in my entire web site. and to use it , i'm flowing this course :

http://www.upsizing.co.uk/Art34_IntergratingASPSecurity.aspx

You will find this post helpful [link](http://stackoverflow.com/questions/473173/will-using-linq-to-sql-help-prevent-sql-injection) — Steven Ackley, Jan 24 '13 at 13:36
This has nothing to do with a regex. Have a look at this question for more things to consider: http://stackoverflow.com/questions/72394/what-should-a-developer-know-before-building-a-public-web-site — Jacco, Jan 24 '13 at 13:36
Maybe I'm missing some VB specific feature, but when you `Return` before the `closeCon()`, I don't think the connection really is closed! — Hans Keﬆing, Jan 24 '13 at 13:38

James · Accepted Answer · 2013-01-24T13:46:01.877

I just want to know if Using LINQ to SQL can help solve this problem in my entire web site.

Technically it will because internally it will deal with all the parameter sanatization your queries currently lack, however, that's not to say you can't solve your problem using the code you already have. All you need to do is update your queries to use SqlParameters e.g.

command.CommandText = "INSERT INTO Dossier(ID_Dossier,Nom_Giac) values(@id, @giac)"
command.Parameters.AddWithValue("@id" , id_dossier.Text))
command.Parameters.AddWithValue("@giac", Nom_giac.Text))

score 1 · Answer 2 · answered Jan 24 '13 at 13:36

You don't need to use LINQ to SQL just for SQL injection. You can use what you've done and modify it to use parameters like:

execsql("insert into Dossier(ID_Dossier,Nom_Giac) values(@dossier, @nom", param1, param2)

These parameters need to be manually added to the command:

cmd = New SqlCommand
cmd.CommandText = req
cmd.Parameters.Add("ID_Dossier", <Type>).Value = dossier
cmd.Parameters.Add("Nom_Giac", <Type>).Value = nom

.
.

So your method accepts a ParamArray of parameters, and passes them to the command. Obviously, the solution needs to be more generic than what i provided, but hopefully this will get you started.

Prevent the sql injection by using LINQ to SQL

2 Answers2