SQL Query from c# class

Question

I'm trying to modify my code by placing the SQL connections and queries into a C# class because currently all my .aspx.cs has connection strings with different types of queries with parameters

Like this:

string CS2 = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

using (SqlConnection con2 = new SqlConnection(CS2))
{
    SqlCommand cmd2 = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = " + lblCedula.Text + "", con2);
    con2.Open();
    object labels = cmd2.ExecuteScalar();
    lblNombre.Text = labels.ToString();
}

As you notice I complete the query with lblCedula.Text so when I try to do this from a C# class it says lblCedula does not exist in this content.

How could I make a reference to the label I have in another page from the class?

This is my code in the C# class

public static List<Perfil> DeletePerfil()
{
    List<Perfil> listDelete = new List<Perfil>();

    string CS = ConfigurationManager.ConnectionStrings["DBCSATE"].ConnectionString;

    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = " + lblCedula.Text + "", con);
        con.Open();

        SqlDataReader rdr = cmd.ExecuteReader();

        while (rdr.Read())
        {
             Perfil perfil1 = new Perfil();
             perfil1.perfil = Convert.ToInt32(rdr["perfil"]);
             perfil1.descripcion = rdr["descripcion"].ToString();

             listDelete.Add(perfil1);
        }
    }

    return listDelete;
}

Don't make a reference to the label, instead pass it's values as an argument to your function. Also you really need to use sql parameters to avoid sql injection. — juharr, Aug 03 '16 at 13:45
And use parameter binding instead of simply concatenating the string. This is exactly the case where SQL injection is possible. — sstan, Aug 03 '16 at 13:46
The label is what the user entered in a page before this page, so I need the reference to the label because the results dependes on what the user entered in a checkbox earlier — Hans, Aug 03 '16 at 13:48
@HansAjani - that fine to accept what the user inputs, but make sure you sanitise the input first and use parameter binding. If you don't you will be open to SQL injection attacks. — sr28, Aug 03 '16 at 13:49
Im reading about SQL injections now, and there is a lot i still need to learn. thanks for the comments all — Hans, Aug 03 '16 at 13:55

score 1 · Answer 1 · edited May 23 '17 at 12:32

You can hand over the string by a method parameter.

Also please mention the use of the SqlParameter. It will prevent your Query from SQL Injection

public static List<Perfil> DeletePerfil(string numeroPersonal)
{

        using (SqlConnection con = new SqlConnection(CS))
        {

            SqlCommand cmd = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = @PersNo", con);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@PersNo";
            param.Value = numeroPersonal;
            cmd.Parameters.Add(param);

         ...
        }
}

score 0 · Accepted Answer · answered Aug 03 '16 at 13:48

0

Like others have said, pass in lblCedula.Text as a string parameter to your DeletePerfil method:

public static List<Perfil> DeletePerfil(string numeroPersonal)
...

Then your call looks like this:

...
var deletedProfiles = YourClass.DeletePerfil(lblCedula.Text);
...

And like sstan's comment mentioned, make sure you use SQLParameters instead of using string concatenation to avoid a SQL Injection security hole.

answered Aug 03 '16 at 13:48

Overhed

1,289
1
13
41

Ok, i understand the theory now, But i still dont understand how or what i need to place in the query instead of the label Is it something like this? "public static List DeletePerfil(string numeroPersonal)"...... then "SqlCommand cmd = new SqlCommand("SELECT nombre FROM [Portal_B2e].[dbo].[usuarios] WHERE numero_personal = " + numeroPersonal + "", con);" ?? – Hans Aug 03 '16 at 13:59
As a direct replacement of your existing code, yes. What we're also suggesting (strongly) is that instead of using string concatenation, you generate your query string using SQL Parameters: http://software-security.sans.org/developer-how-to/fix-sql-injection-microsoft-.net-with-parameterized-queries – Overhed Aug 03 '16 at 14:04
Ok, thanks a lot! It worked... Let me try with the parameters now – Hans Aug 03 '16 at 14:13

SQL Query from c# class

2 Answers2