C# Passing parameters to stored procedure with EXEC query

Question

I am trying to pass parameters from my program to Stored Procedure in EXEC format.Following is my code

public void button1_Click(object sender, EventArgs e)
        {
            frm = new FrmLogin();

            OleDbConnection conn = new OleDbConnection("File Name=E:\\Vivek\\License Manager\\License Manager\\login.udl");

            try
            {

                conn.Open();

                string user = username.Text;
                string pass = password.Text;

                string query = "EXEC dbo.checkuser"' + username.Text'" + " " + "'password.Text'"";

                OleDbCommand cmd = new OleDbCommand(query,conn);


                cmd.ExecuteNonQuery();

                // Retrieve the return value

                string result = query.ToString();

                MessageBox.Show(result);



             }

             catch (Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }


              conn.Close();

         }

What should I write in string query=" "?,I am trying to pass username and password as parameters to the stored procedure and once the query executes and returns the result ,I will store it in another variable named result.Am I doing it the right way? I am new to C#

Please suggest,

Thanks

There is an hidden reason to explain why you need to use EXEC here? — Steve
– Steve, Commented Oct 8, 2014 at 5:55
By the way, if you are using SqlServer, you could also use a SqlCommand. This might have better (faster) results than an OleDbCommand. — Hans Keﬆing
– Hans Keﬆing, Commented Oct 8, 2014 at 7:17

Troy Gizzi · Accepted Answer · 2014-10-08 05:56:11Z

8

Building command text with dynamically inserted segments from user input is very dangerous, and leaves you open to SQL Injection.

Below is a slight variation which parameterizes those strings. This approach is much safer.

string query = "dbo.checkuser";

OleDbCommand cmd = new OleDbCommand(query,conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username.Text);
cmd.Parameters.AddWithValue("@password", password.Text);

Note: This updated version sets up the command as a stored procedure, instead of plain text.

answered Oct 8, 2014 at 5:56

Troy Gizzi

2,5191 gold badge17 silver badges15 bronze badges

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

3 Comments

Troy Gizzi Over a year ago

By the way, you didn't ask about this part, but I noticed that the code to retrieve the query output (string result = query.ToString();) is not going to work as expected. If you can describe what output you're expecting, we can help you with that part of it too. For example, if your stored proc is returning a single string value via a SELECT statement, then something like string result = (string)cmd.ExecuteScalar(); might work.

user2614235 Over a year ago

Thanks Troy...is there any other technique apart from AddWithValues?

Troy Gizzi Over a year ago

Yes, there are other alternatives listed in the official MSDN documentation for the OleDbParameterCollection (which is what cmd.Parameters is an instance of).

user3713775 · Accepted Answer · 2014-10-25 09:14:58Z

0

try this

OleDbCommand cmd = new OleDbCommand("StoredPorcedureName",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameter.AddWithValue("@user", username.Text);
cmd.Parameter.AddWithValue("@pwd", password.Text);
cmd.ExecuteNonQuery();

edited Oct 25, 2014 at 9:14

answered Oct 25, 2014 at 9:09

user3713775

1683 gold badges4 silver badges14 bronze badges

Comments

PhantomRick · Accepted Answer · 2014-10-25 04:54:38Z

-2

That looks Okay at a glance except for your query string. Change to:

string query = "EXEC dbo.checkuser '" + username.Text "', '" + password.Text + "'";

might work better.

Edit

Yes, as per comments about SQL injection, Troy's answer is significantly better.

Just for completeness that can be possibly used in other situations, you can avoid SQL injection using this method by trying something like:

string query = "EXEC dbo.checkuser '" + username.Text.Replace("'", "''") "', '" + password.Text.Replace("'", "''") + "'";

edited Oct 25, 2014 at 4:54

answered Oct 8, 2014 at 5:49

PhantomRick

525 bronze badges

1 Comment

Miha Markic Over a year ago

Prone to SQL injection.

Collectives™ on Stack Overflow

C# Passing parameters to stored procedure with EXEC query

3 Answers 3

3 Comments

Comments

Edit

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Edit

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related