Problem with INSERT Statement

[Guest]

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(TableColumn1, TableCoulmn2)
Values(@name , @description )

Go

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

SqlCommand command = new SqlCommand("InsertRow",new SqlConnection ("Your
connection string));
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@name", TextBox1.Text));
command.Parameters.Add(new SqlParameter("@description", TextBox2.Text));
command.Connection.Open();
command.ExecuteScalar();
command.Connection.Close();


That should do ya.

Ian

 

[Gav]

Why not add:

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

and of course the same for any other columns

 

[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.public.dotnet.languages.csharp]