c# - Sql Injection parameterised query -

i need achieve function below

     public partydetails getallpartydetails(string name)     {         try         {             string query = "select * [party details] name=@name ";             pd = new partydetails();             com = new sqlcecommand(query, con);             com.parameters.addwithvalue("@name", name);             con.open();             sdr = com.executereader();             while (sdr.read())             {                  pd.name = sdr.getstring(0);              }             con.close();             return pd;         }          catch (exception e)         {             con.close();             throw e;         }     } 

but function not efficient me because don't need write different function code because of change in query.

now need

  public partydetails getallpartydetails(string query)         {             try             {             pd = new partydetails();             com = new sqlcecommand(query, con);             con.open();             sdr = com.executereader();             while (sdr.read())             {                 pd.name = sdr.getstring(0);             }             con.close();             return pd;         }         catch (exception e)         {             con.close();             throw e;         }     } 

but increases risk of sql injection not using com.parameters.addwithvalue("@name", name);.is there replacement possible achieved calling function stop sql injection .

for don't understand question

for example have query select * [party details] address=@address , name=@anme , need again write function in use com.parameters.addwithvalue("@address", address); com.parameters.addwithvalue("@name", name);, wastage of time. query can have different no of parameter's , need function not depend upon no of parameter in query.

a function named "getallpartydetails" should not accept sql string argument. purpose of method abstract away need rest of app know or care sql, , provide source party details separate database implementation. should accept name of party argument, if need accept sql query, you're building sql in wrong place.

what need method can called in generic way not getpartydetails() other methods need data particular data source. if you're building query string outside of getpartydetails, need re-architect little bit.

what should method retrieving data like? of course needs accept sql string. needs way accept parameter information. simple array of key/value pairs, prefer code avoids need build parameter collections twice. argument should required, rather optional or overloaded, encourage parameter use.

i use pattern, , i've become fond of it:

private ienumerable<t> getdata(string sql, action<sqlparametercollection> addparams, func<idatarecord, t> translate) {     using (var cn = new sqlconnection("connection string here"))     using (var cmd = new sqlcommand(sql, cn))     {        addparams(cmd.parameters);        cn.open();        using (var rdr = cmd.executereader())        {           while (rdr.read())           {               yield return translate(rdr);           }        }     } } 

this meets goals of our generic data access method. part i'm not thrilled need translation delegate, , it's not big loss because code going have write @ level of application anyway. if don't copy each row new object inside method, can unexpected results, , need way translate datarecord business object right there.

you call this:

public string getpartydetailsbyname(string name) {     return getdata("select * [party details] name=@name", p =>     {        p.add("@name", sqldbtype.nvarchar, 50).value = name;     }, row =>     {        row.getstring(0);     }).first(); } 

if have query parameters, call this:

public string getpartydetailsbynameaddress(string name, string address) {     return getdata("select * [party details] name=@name , address=@address", p =>     {        p.add("@name", sqldbtype.nvarchar, 50).value = name;        p.add("@address", sqldbtype.nvarchar,200).value = address;     }, row =>     {        row.getstring(0);     }).first(); } 

a method not take parameter this:

public ienumerable<string> getallpartydetails() {     return getdata("select * [party details]", p => {}, row =>     {        row.getstring(0);     }); } 

it's little awkward, that's point. want people deliberate not using parameters, can't stumble doing right way.

i know wanted avoid writing 2 methods, but right way handle data access. yes, have 1 method talks database, abstract away of boilerplate code. having additional method each sql query still right thing do.

you don't need follow getdata() method exactly: functional style bit some. need single method 1 , place can send query database, , method must have mechanism accepting parameter data. other methods should not passing sql around. leads injection issues.

each question ask data belongs in it's own method. ideally, these methods gathered in class, or group of classes gathered in single project larger applications.