Use parametrized sql queries when SQLCommand used in separate class

Question

I am interesting to add parametrize sql queries in my ASP.net application. I have seen some good articles regarding Avoid SQL Injection.

string sql = string.Format("INSERT INTO [UserData] (Username, Password, Role, Membership, DateOfReg) VALUES (@Username, @Password, @Role, @Membership, @DateOfReg)");
        SqlCommand cmd = new SqlCommand(sql, conn);
        try
        {
        cmd.Parameters.AddWithValue("Username", usernameTB.Text);
        cmd.Parameters.AddWithValue("Password", passwordTB.Text);
        cmd.Parameters.AddWithValue("Role", roleTB.Text);
        cmd.Parameters.AddWithValue("Membership", membershipTB.Text);
        cmd.Parameters.AddWithValue("DateOfReg", dorTB.Text);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

find the Reference

However this way is not useful to me since I couple the DB connection to separate class since I have reuse it.

public class DBconnection{     
    public int insertQuery(String query) {



            int affectedRowCount = 0;
            SqlConnection conn = null;
            try{

                conn = new SqlConnection("Server=localhost;Database=master;UID=sa;PWD=sa;");
                SqlCommand cmd = new SqlCommand( query, conn );
                cmd.CommandType = CommandType.Text;

                conn.Open(  );
                affectedRowCount = cmd.ExecuteNonQuery(  );
                conn.Close(  );         

            } catch ( Exception e ){

                       String error = e.Message;

            }

            return affectedRowCount;
    }
}

Therefore I only use bellow code part to call above class and Insert values to DB.

 String SQLQuery1 = insert into Article values('" + Txtname.Text + "','" + TxtNo.Text + "','" + Txtdescription.Text + "' ,0)");
DBconnection dbConn = new DBconnection();
        SqlDataReader Dr = dbConn.insertQuery(SQLQuery1);

Please help me to use Parameterize sqlString to Avoid me Sql Injection. To use @name , @ No and @description without use Textbox inputs.

Cade Roux · Accepted Answer · 2012-04-23 01:28:36Z

It's perfectly reasonable to do this, but have your class call back (lambda/delegate) out to get the parameters. This is a static method in a class which is called by various overloaded instance methods:

private static int SqlExec(string ConnectionString, string StoredProcName, Action<SqlCommand> AddParameters, Action<SqlCommand> PostExec)
        {
            int ret;
            using (var cn = new SqlConnection(ConnectionString))
            using (var cmd = new SqlCommand(StoredProcName, cn))
            {
                cn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                if (AddParameters != null)
                {
                    AddParameters(cmd);
                }

                ret = cmd.ExecuteNonQuery();

                if (PostExec != null)
                {
                    PostExec(cmd);
                }
            }
            return ret;
        }

Then, a usage example:

    public void Save()
    {
        Data.Connect().Exec("Project_Update", Cm =>
        {
            Cm.Parameters.AddWithValue("@ProjectID", ID);
            Cm.Parameters.AddWithValue("@PrimaryApplicantID", PrimaryApplicant.IdOrDBNull());
            Cm.Parameters.AddWithValue("@SecondaryApplicantID", SecondaryApplicant.IdOrDBNull());
            Cm.Parameters.AddWithValue("@ProjectName", ProjectName.ToDBValue());
        });
    }

It's also possible to do this with non-stored procedure calls.

In your case it would look like:

DBconnection.InsertQuery(
    "INSERT INTO [UserData]
        (Username, Password, Role, Membership, DateOfReg)
        VALUES (@Username, @Password, @Role, @Membership, @DateOfReg)"
    ,cmd => {
                cmd.Parameters.AddWithValue("Username", usernameTB.Text);
                cmd.Parameters.AddWithValue("Password", passwordTB.Text);
                cmd.Parameters.AddWithValue("Role", roleTB.Text);
                cmd.Parameters.AddWithValue("Membership", membershipTB.Text);
                cmd.Parameters.AddWithValue("DateOfReg", dorTB.Text);
            }
);

Which puts all your database stuff together the way you want and lets the DBconnection keep its internals isolated.

taylonr · Accepted Answer · 2012-04-22 16:48:38Z

How about instead of a generic InsertQuery() method you write specific InsertQuery methods?

For example:

public void AddNewUser(User u)
{
   var query = "insert Users (name, password) values (@0, @1)";
   SqlCommand cmd = new SqlCommand(query, conn);
        try
        {
        cmd.Parameters.AddWithValue("@0", u.UserName);
        cmd.Parameters.AddWithValue("@1", u.Password);
        }
}

This has the advantage of ALL your SQL logic being in this other class, as opposed to the calling class needing to know how to construct the query etc.

It also makes your code more readable, because you see AddUser or UpdateUser or ChangePassword as method calls, and don't have to read SQL at that moment to try and guess what is going on in the program.

HOWEVER if you're going to do something like this, you should check out some MicroORMs, my personal favorite is PetaPoco (or the NuGet version)

PetaPoco and others like Massive and Dapper would let you do something like:

database.Insert(u);

Where u is a User object that maps to your DB's table. It uses ADO.NET and makes sure to use SQL Parameters.

Community · Accepted Answer · 2020-06-20 09:12:55Z

I would suggest using LINQ to SQL, which automatically parametrizes everything.

Q. How is LINQ to SQL protected from SQL-injection attacks?

A. SQL injection has been a significant risk for traditional SQL queries formed by concatenating user input. LINQ to SQL avoids such injection by using SqlParameter in queries. User input is turned into parameter values. This approach prevents malicious commands from being used from customer input.

You can insert, update and delete from a SQL database in a straightforward manner using a DataContext (right-click on your project to add a new item and add the LINQ to SQL Classes template, then use the Server Explorer to add objects to it).

I haven't worked with this in a while, but I believe your code would then look somewhat like this:

UserData user = new UserData();

user.Username = ...;
user.Password = ...;
user.Role = ...;
user.Membership = ...;
user.DateOfReg = ...;

db.UserDatas.InsertOnSubmit(user);
db.SubmitChanges();

When you call SubmitChanges, LINQ to SQL automatically generates and executes the SQL commands that it must have to transmit your changes back to the database.

Edit1:

As an added note, to retrieve an existing item from a database, you could do this:

var user = (from i in db.UserDatas
            where i.UserName == "devan"
            select i).Single();

Oh, and as is my standard policy when answering questions about databases with login information, I must implore you, for the love of god and all that is holy, to salt and hash your users' passwords.

Collectives™ on Stack Overflow

Use parametrized sql queries when SQLCommand used in separate class

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related