Query in an SQL string in C#

Question

I’m doing a query in the database via string sql.append, in the method call I have two parameters, two strings these receive one textbox typed by the user and check in the database.

How do I concatenate correctly? Below is the insert that I made and is working. And in the case of the other, how do I?

Code

    sql.Append("Insert into AppSignin(UserName, FirstName, LastName, Email, Password) Values ('");
    sql.Append(ClearText(userName));
    sql.Append("','");
    sql.Append(ClearText(firstName));
    sql.Append("','");
    sql.Append(ClearText(lastName));
    sql.Append("','");
    sql.Append(ClearText(Email));
    sql.Append("','");   
    sql.Append(ClearText(Password));
    sql.Append("')'");

And below the query I should do:

sql.Append("Select Count(*) as Qtd From AppUser Where UserName = UserName OR Email = Email)

Based on the model above how I should do this?

Querys were treated using the method ClearText. But really, it’s much easier. In this case I can’t use sqlcommand because I am using Oledb and Connectionstring inside an UDL file.

Below is the complete code I managed to query:

public partial class _Default : System.Web.UI.Page
{
}

protected void Button1_Click(object sender, EventArgs e)
{
    string usuario = TextBox1.Text;
    string nome = TextBox2.Text;
    string sobrenome = TextBox3.Text;
    string email = TextBox4.Text;
    string senha = TextBox5.Text;

    try
    {
        string sErr = ValidateFields();
        if (sErr.Length == 0)
        {
            SetUser(usuario, nome, sobrenome, email, senha);
        }
        else
        {
            Response.Write(sErr.ToString());  
        }

        Label6.Text = "Dados cadastrados com Sucesso";
    }
    catch(Exception ex)
    {
    }
}

public void SetUser(string userName, string firstName, string lastName, string Email, string Password)
{
    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
    DbCommand cmd = factory.CreateCommand();
    DbConnection cn = factory.CreateConnection();
    StringBuilder sql = new StringBuilder();

    cn.ConnectionString = ConfigurationManager.AppSettings["connectionString"];

    sql.Append("Insert into AppSignin(UserName, FirstName, LastName, Email, Password) Values ('");
    sql.Append(ClearText(userName));
    sql.Append("','");
    sql.Append(ClearText(firstName));
    sql.Append("','");
    sql.Append(ClearText(lastName));
    sql.Append("','");
    sql.Append(ClearText(Email));
    sql.Append("','");   
    sql.Append(ClearText(Password));
    sql.Append("')");

    cmd.Connection = cn;
    cmd.CommandText = sql.ToString();
    cmd.CommandType = CommandType.Text;

    cn.Open();
    cmd.ExecuteNonQuery();

    cn.Close();
}

private string ValidateFields()
{
    StringBuilder sErr = new StringBuilder();
    string usuario = TextBox1.Text;
    string nome = TextBox2.Text;
    string sobrenome = TextBox3.Text;
    string email = TextBox4.Text;
    string senha = TextBox5.Text;

    if (TextBox1.Text.Trim().Length == 0)
    {
        Label6.Text = "Campo Usuario Obrigatório!";
    }
    if (TextBox4.Text.Trim().Length == 0)
    {
        Label6.Text = "Campo Email Obrigatório!";
    }

    if (sErr.Length == 0)
    {
        if (HasAppUser(usuario, email) || HasAppSignin(usuario, email))
        {
            Label6.Text = " Usuário ou Email já cadastrado ";
        }
    }

    return sErr.ToString();  
}


private string ClearText(string value)
{
    value = value.Replace("'", "`");
    value = value.Replace("<", "");
    return value;
}

private bool HasAppUser(string userName, string Email)
{
    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
    DbCommand cmd = factory.CreateCommand();
    DbConnection cn = factory.CreateConnection();
    StringBuilder sql = new StringBuilder();

    cn.ConnectionString = ConfigurationManager.AppSettings["connectionString"];

    sql.Append("Select Count(*) as Qtd From AppUser Where UserName = ? OR Email = ?");

    cmd.Connection = cn;
    cmd.CommandText = sql.ToString();
    cmd.CommandType = CommandType.Text;

    DbParameter par = factory.CreateParameter();
    par.DbType = DbType.String;
    par.ParameterName = "?";
    par.Value = userName;

    DbParameter par2 = factory.CreateParameter();
    par2.DbType = DbType.String;
    par2.ParameterName = "?";
    par2.Value = Email;

    cmd.Parameters.Add(par);
    cmd.Parameters.Add(par2);

    cn.Open();
    int nQtd = int.Parse(cmd.ExecuteScalar().ToString());
    cn.Close();

    return (nQtd > 0);
}

private bool HasAppSignin(string userName, string Email)
{
    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
    DbCommand cmd = factory.CreateCommand();
    DbConnection cn = factory.CreateConnection();
    StringBuilder sql = new StringBuilder();

    cn.ConnectionString = ConfigurationManager.AppSettings["connectionString"];

    sql.Append("Select Count(*) as Qtd From AppSignin Where UserName = ? OR Email = ?");

    cmd.Connection = cn;
    cmd.CommandText = sql.ToString();
    cmd.CommandType = CommandType.Text;

    DbParameter par = factory.CreateParameter();
    par.DbType = DbType.String;
    par.ParameterName = "?";
    par.Value = userName;

    DbParameter par2 = factory.CreateParameter();
    par2.DbType = DbType.String;
    par2.ParameterName = "?";
    par2.Value = Email;

    cmd.Parameters.Add(par);
    cmd.Parameters.Add(par2);

    cn.Open();
    int nQtd = int.Parse(cmd.ExecuteScalar().ToString());
    cn.Close();

    return (nQtd > 0);
}
}

Answer 1

This is terribly wrong. The right thing is for you to parameterize your query to avoid SQL Injection:

sql.Append("Insert into AppSignin(UserName, FirstName, LastName, Email, Password) Values (:UserName, :FirstName, :LastName, :Email, :Password)");
var sqlCommand = new DbCommand(sql, connection);
sqlCommand.Parameters.Add(new DbParameter { ParameterName = "UserName", Value = valorUserName});
sqlCommand.Parameters.Add(new DbParameter { ParameterName = "FirstName", Value = valorFirstName});
sqlCommand.Parameters.Add(new DbParameter { ParameterName = "LastName", Value = valorLastName});
sqlCommand.Parameters.Add(new DbParameter { ParameterName = "Email", Value = valorEmail});
sqlCommand.Parameters.Add(new DbParameter { ParameterName = "Password", Value = valorPassword});

So the second one is:

sql.Append("Select Count(*) as Qtd From AppUser Where UserName = :UserName OR Email = :Email");
var sqlCommand = new DbCommand(sql, connection);
sqlCommand.Parameters.Add(new DbParameter { ParameterName = "UserName", Value = valorUserName});
sqlCommand.Parameters.Add(new DbParameter { ParameterName = "Email", Value = valorEmail});

Answer 2

It is also nice to use parameters to your method Sqlcommand(). Example:

public void SeuMetodo()
{
string commandText = "Insert into AppSignin(UserName) values ('@userNameParameter')";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);

        string userName = "Franscisco";

        command.Parameters.AddWithValue("@userNameParameter", userName);
        connection.Open();
        command.ExecuteNonQuery();

    }
}

Answer 3

How the people spoke the correct way is using Command. But what you want to do, it can be easier using string.format.

Follow the example:

string email, userName;
email = "[email protected]";
userName = "xpto";
sql.Append(string.Format("Select Count(*) as Qtd From AppUser Where UserName = {0} OR Email = {1})", ClearText(userName), ClearText(email)));