Prevent Sql injection when compose a dynamic Sql

Question

I had to dynamically create Sql statements for the parameters of a stored procedure due to the legacy database access layer.

var p1 = Encoding.ASCII.GetString(p1).Replace("'", "''").Replace("\n", " ");
var p2 = Encoding.ASCII.GetString(p2).Replace("'", "''").Replace("\n", " ");
.....
var sql = string.Format("exec storedprocedure @p1='{0}', @p2='{1}', @p3='{2}', ....", 
    p1, p2, p3, ...);

Right now I replace ' to '' and \n' to an empty space after converting the strings to ascii string. What else I need to do? I cannot use SqlParamter and had to concat the string due to some facts.

What about \', ", \", unicode that when decoded in the right way turns into one of the above ( security.stackexchange.com/questions/11391/… ), etc? — Patashu
– Patashu, Commented Mar 28, 2013 at 4:05
@Patashu I've updated the question and force the string to Ascii — ca9163d9
– ca9163d9, Commented Mar 28, 2013 at 4:17
@NickW I'm not sure if forcing it to ASCII fixes it, but I've honestly never tried something like that — Patashu
– Patashu, Commented Mar 28, 2013 at 4:21

Jake H · Accepted Answer · 2013-03-28 04:17:14Z

1

Dont.

Use typed parameters on all of your queries that have dynamic inputs. All of the flavors of ADO have typed parameters, as well as ADO.NET providers for every database under the sun. If you try to roll your own SQL injection prevention, you're gonna have a bad time.

answered Mar 28, 2013 at 4:17

Jake H

1,7401 gold badge12 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Prevent Sql injection when compose a dynamic Sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related