CODEWITHSUNDEEP
sql
NoviceToDotNet
NoviceToDotNet

Reputation: 10805

string concatation in sql query

i am having confusion with this string concatenation could some body please brief me how this string concatenation taking place? The confusion i am having is that, how this +, "", ' are working in this

int i = Magic.Allper("insert into tbl_notice values ('" + Label1.Text + "','" + companyTxt.Text + "','" + txtBranch.Text + "','" + dateTxt.Text + "'  ,'" + reportingTxt.Text + "','" + venueTxt.Text + "','" + eligibilityTxt.Text + "')");

Upvotes: 0

Views: 291

Answers (4)

hunter
hunter

Reputation: 63512

I would use the string.Format method for clarity

int i = Magic.Allper(string.Format("insert into tbl_notice values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", 
    Label1.Text, 
    companyTxt.Text, 
    txtBranch.Text, 
    dateTxt.Text, 
    reportingTxt.Text,
    venueTxt.Text, 
    eligibilityTxt.Text));

You might also want to create an extension method that will make sure the strings are safe to pass to SQL in this fashion

public static string ToSqlFormat(this string mask, params string[] args)
{
    List<string> safe = args.ToList();
    safe.ForEach(a => a.Replace("'", "''"));
    return string.Format(mask, safe);
}

which will let you write

string insert = "insert into tbl_notice values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')";
int i = Magic.Allper(insert.ToSqlFormat( 
    Label1.Text, 
    companyTxt.Text, 
    txtBranch.Text, 
    dateTxt.Text, 
    reportingTxt.Text,
    venueTxt.Text, 
    eligibilityTxt.Text));

Upvotes: 1

Will Marcouiller
Will Marcouiller

Reputation: 24132

I strongly recommend that you don't use string concatenation in SQL queries. They provoque SQL injections. This will cause security issues.

What is SQL Injection?

In response to your question, this concatenation simply takes every TextBox.Text property value and concatenate it into your insert statement.

I strongly recommend that you're using parameterized queries using ADO.NET lise the following example (assuming SQL Server):

using (var connection = new SqlConnection(connString))
    using (var command = connection.CreateCommand()) {
        string sql = "insert into tbl_notice values(@label1, @companyTxt, @txtBranch, @dataTxt, @reportingTxt, @venueTxt, @eligibilityTxt)";

        command.CommandText = sql;
        command.CommandType = CommandType.Text;

        SqlParameter label1 = command.CreateParameter();
        label1.ParameterName = "@label1";
        label1.Direction = ParameterDirection.Input;
        label1.Value = Label1.Text;

        SqlParameter companyTxt = command.CreateParameter();
        companyTxt.ParameterName = "@companyTxt";
        companyTxt.Direction = ParameterDirection.Input;
        companyTxt.Value = companyTxt.Text;

        // And so forth for each of the parameters enumerated in your sql statement.

        if (connection.State == ConnectionState.Close)
            connection.Open();

        int rowsAffected = command.ExecuteNonQuery();
    }

Upvotes: 2

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

("insert into tbl_notice values ('" + Label1.Text + "','" + companyTxt.Text + "','" + txtBranch.Text + "','" + dateTxt.Text + "' ,'" + reportingTxt.Text + "','" + venueTxt.Text + "','" + eligibilityTxt.Text + "')");

Assuming that

  • Label1= Hello
  • companyTxt = ABC
  • txtBranch = Engineering
  • dateTxt = 2010-12-01
  • reportingTxt = Fergusson
  • venueTxt = Batcave
  • eligibilityTxt = No

The above values are replaced in the SQL statement, making it look like

("insert into tbl_notice values ('" + Hello + "','" + ABC + "','" + Engineering + "','" + 2010-12-01 + "' ,'" + Fergusson + "','" + Batcave + "','" + No + "')");

The "+" operator joins the string values, resulting in

("insert into tbl_notice values ('Hello','ABC','Engineering','2010-12-01' ,'Fergusson','Batcave','No')")

Upvotes: 2

greenimpala
greenimpala

Reputation: 3966

Anything between two " characters is taken as a String in Java so "','" produces ','. SQL requires Strings wrapped in '. So "'" + venueTxt.Text + "'" parses to 'variable value' when the query is made.

Upvotes: 3

Related Questions