Which statement is better and more secure for SQL Injection?

Question

Which statement is better and more secure to prevent any sql injection, or are they the same?

com.CommandText = "update tblTableName set Name=@name, Age=@age WHERE ID=1";
com.Parameters.AddWithValue("@name", name.ToString());
com.Parameters.AddWithValue("@age", age.ToString());

OR

com.CommandText = @"update tblTableName set Name=" + name.ToString() + ", Age=" + age.ToString() + " WHERE ID=1";

OR

com.CommandText = "update tblTableName set Name=" + HttpUtility.HtmlEncode(name.ToString()) + ", Age=" + age.ToString() + " WHERE ID=1";

OR is there a better way.? appriciate your help and opinion.

of course first one. because HtmlEncode would never help us to prevent sql injection. — Jenish Rabadiya, Mar 10 '15 at 07:14
the first one... -> http://stackoverflow.com/questions/7505808/using-parameters-in-sql-statements — Edi G., Mar 10 '15 at 07:16

Soner Gönül · Accepted Answer · 2015-03-10T07:28:46.770

3

Only first one prevents SQL Injection attacks.

You don't use any prepared statements and parameterized sql in your second or third example. I can send them 18; DROP TABLE tblTableName in your age variable. Creating html-encoded string with HttpUtility.HtmlEncode method doesn't help either.

Also don't use AddWithValue method. It may generate unexpected results sometimes. Use .Add() method overloads to specify your column type and size explicitly.

Read: Can we stop using AddWithValue() already?

edited Mar 10 '15 at 07:28

answered Mar 10 '15 at 07:14

Soner Gönül

97,193
102
206
364

how would you use paremeter in this example: com.CommandText = "update tblTableName set Notes = convert(nvarchar(4000),Notes) + '. " + notes + "' WHERE ID=1"; – Kamiran Mar 10 '15 at 07:36
@Kamiran You can parameterize your `Notes` as `com.CommandText = "update tblTableName set Notes = convert(nvarchar(4000), @Notes) WHERE ID=1";` then you can it's value with `com.Parameters.Add("@Notes", ...);` fro example. – Soner Gönül Mar 10 '15 at 07:46
did you notes that i am trying to append more text to Note ? – Kamiran Mar 10 '15 at 07:49
@Kamiran What do you mean by that? You can only add the size of `nvarchar(4000)` of it. Or do you mean multiple parameter value in the same parameter? You can't do that as far as I know. – Soner Gönül Mar 10 '15 at 08:15
dear, what i want is : I have a field called Notes. I want to add more text to existing text in that field, not replacing the existing text using parameters – Kamiran Mar 10 '15 at 08:18
@Kamiran Then you can do it like; `update tblTableName set Notes = Notes + convert(nvarchar(4000) ...` for example. – Soner Gönül Mar 10 '15 at 08:21

score 0 · Answer 2 · answered Mar 10 '15 at 07:16

0

Only first, its prevent to sql injection.

http://www.c-sharpcorner.com/UploadFile/puranindia/parameterized-query-and-sql-injection-attacks/

answered Mar 10 '15 at 07:16

puko

2,819
4
18
27

Which statement is better and more secure for SQL Injection?

2 Answers2