Problem with INSERT Statement

[alexmaster_2004]

hi
i have made an application using C# that access sql2000.
this application is just used to insert data to the database.
i use something like this in my code:
//
string colmnA = TextBox1.Text;
string comlnB = TextBox2.Text;
string sqlstatment = "INSERT INTO TABLENAME VALUES(" +"'"+colmnA+ "'" + "," +
"'" + colmnB + "'" + ")";

everything was fine till someone have entered the following :
colmnA = "My name'";
colmnB = "Alex";

then the INSERT statement is:
INSERT INTO TABLENAME VALUES('My name'','Alex').
As you see the second " ' " was the problem.
i want to know how to avoid this problem.
Is there is a meean that make the sql to insert the value as "My name'" to
the DB.
and not throw exception about the second " ' ".
Thanks in advance

[Ian Frawley]

Hi

What you want to do is create a stored procedure that takes in two
parameters eg.

CREATE PROCEDURE InsertRow
@name varchar(50),
@description varchar(100)
AS

INSERT INTO TableName(Table Column1, TableCoulmn2)
Values(@name , @description )

Go

And then in yer c# use a ADO command object with parameters eg.

SqlCommand command = new SqlCommand("Ins ertRow",new SqlConnection ("Your
connection string));
command.Command Type = CommandType.Sto redProcedure;
command.Paramet ers.Add(new SqlParameter("@ name", TextBox1.Text)) ;
command.Paramet ers.Add(new SqlParameter("@ description", TextBox2.Text)) ;
command.Connect ion.Open();
command.Execute Scalar();
command.Connect ion.Close();
That should do ya.

Ian

"alexmaster_200 4" <al************ @discussions.mi crosoft.com> wrote in
message news:28******** *************** ***********@mic rosoft.com...

hi
i have made an application using C# that access sql2000.
this application is just used to insert data to the database.
i use something like this in my code:
//
string colmnA = TextBox1.Text;
string comlnB = TextBox2.Text;
string sqlstatment = "INSERT INTO TABLENAME VALUES(" +"'"+colmnA+ "'" + ","
+
"'" + colmnB + "'" + ")";

everything was fine till someone have entered the following :
colmnA = "My name'";
colmnB = "Alex";

then the INSERT statement is:
INSERT INTO TABLENAME VALUES('My name'','Alex').
As you see the second " ' " was the problem.
i want to know how to avoid this problem.
Is there is a meean that make the sql to insert the value as "My name'" to
the DB.
and not throw exception about the second " ' ".
Thanks in advance

[Gav]

Why not add:

while(colmnA.In dexOf("'")!= -1)
{
colmnA.Remove(c olmnA.IndexOf(" '"), 1);
}

and of course the same for any other columns

"alexmaster_200 4" <al************ @discussions.mi crosoft.com> wrote in
message news:28******** *************** ***********@mic rosoft.com...

hi
i have made an application using C# that access sql2000.
this application is just used to insert data to the database.
i use something like this in my code:
//
string colmnA = TextBox1.Text;
string comlnB = TextBox2.Text;
string sqlstatment = "INSERT INTO TABLENAME VALUES(" +"'"+colmnA+ "'" + ","
+
"'" + colmnB + "'" + ")";

everything was fine till someone have entered the following :
colmnA = "My name'";
colmnB = "Alex";

then the INSERT statement is:
INSERT INTO TABLENAME VALUES('My name'','Alex').
As you see the second " ' " was the problem.
i want to know how to avoid this problem.
Is there is a meean that make the sql to insert the value as "My name'" to
the DB.
and not throw exception about the second " ' ".
Thanks in advance

[Richard Blewett [DevelopMentor]]

What you have is a wide open door to a SQL Injection attack. Imaging if someone set

TextBox2.Text = "';drop database accounts;--"

The result would be the dropping of your accounts database. So the standard way to get round this is to use parameters rather than string concatenation to build the statement - e.g. use SqlParameter objects added to the Parameters collection of your command object and change your SQL statement to take parameters

string sqlstatment = "INSERT INTO TABLENAME VALUES(@col1, @col2)";

In this case you add two parameter objects with the ParameterName set to "@col1" and "@col2" respectively

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

hi
i have made an application using C# that access sql2000.
this application is just used to insert data to the database.
i use something like this in my code:
//
string colmnA = TextBox1.Text;
string comlnB = TextBox2.Text;
string sqlstatment = "INSERT INTO TABLENAME VALUES(" +"'"+colmnA+ "'" + "," +
"'" + colmnB + "'" + ")";

everything was fine till someone have entered the following :
colmnA = "My name'";
colmnB = "Alex";

then the INSERT statement is:
INSERT INTO TABLENAME VALUES('My name'','Alex').
As you see the second " ' " was the problem.
i want to know how to avoid this problem.
Is there is a meean that make the sql to insert the value as "My name'" to
the DB.
and not throw exception about the second " ' ".
Thanks in advance

[microsoft.publi c.dotnet.langua ges.csharp]