Inserting a record into the SQL Server Database

Hi,
look at this short sample. myTextValue has a value, that foe example an user has entered in a TextBox:
string myTextValue = "Now we will drop the Foo table!'); DROP TABLE Foo --";
string insert = "INSERT INTO Foo ( MyTextValue ) VALUES( '" + myTextValue + "' )";

// Now we have to valid statements:
// INSERT INTO Foo ( MyTextValue ) VALUES( 'Now we will drop the Foo table!');
// DROP TABLE Foo --' )

using( SqlCommand command = new SqlCommand( insert, connection /* , transaction */ ) )
{
    command.ExecuteNonQuery();
}

Of course that needs a good knowledge of the database, and the privileges to drop tables. But it is possible to guess some tablenames. User, T_User, TUser or Setting, T_Setting,...
Sometimes error messages can help to get hints on the database structure.

To exactly answer your question: The SQLEngine does not create a string that concats all parameters together. But how the SQLEngine internly handels parameters... I don't know. It is just working ;)

Just another note when not using parameters:
You might run into trouble when using float, double, decimal, datetime,... variables. You have to convert all the types into a string.
So, when you have decimal a = 4150.42423m; and call a.ToString(); on a german machine it will most likely end up with 4.150,42423 and in switzerland with 4'150.42423 .
I don't want to write a sample for DateTime. Much much much... trouble.

the end

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dr Spack18y ago
Hi,
look at this short sample. myTextValue has a value, that foe example an user has entered in a TextBox:
string myTextValue = "Now we will drop the Foo table!'); DROP TABLE Foo --";
string insert = "INSERT INTO Foo ( MyTextValue ) VALUES( '" + myTextValue + "' )";

// Now we have to valid statements:
// INSERT INTO Foo ( MyTextValue ) VALUES( 'Now we will drop the Foo table!');
// DROP TABLE Foo --' )

using( SqlCommand command = new SqlCommand( insert, connection /* , transaction */ ) )
{
    command.ExecuteNonQuery();
}

Of course that needs a good knowledge of the database, and the privileges to drop tables. But it is possible to guess some tablenames. User, T_User, TUser or Setting, T_Setting,...
Sometimes error messages can help to get hints on the database structure.

To exactly answer your question: The SQLEngine does not create a string that concats all parameters together. But how the SQLEngine internly handels parameters... I don't know. It is just working ;)

Just another note when not using parameters:
You might run into trouble when using float, double, decimal, datetime,... variables. You have to convert all the types into a string.
So, when you have decimal a = 4150.42423m; and call a.ToString(); on a german machine it will most likely end up with 4.150,42423 and in switzerland with 4'150.42423 .
I don't want to write a sample for DateTime. Much much much... trouble.

the end
Srikanth Guna18y ago
@Dr Spack

thank you for the suggestion ......it really helped me

how does it help in preventing SQL Injection ??????
Dr Spack18y ago
Hi,

you should always use parameters in non constant values.
1. It prevents SQL-Injection.
2. It solves your current problem

Example:
// ...
using( SqlCommand command = new SqlCommand( "INSERT INTO Foo ( MyTextValue ) VALUES( @MyTextValue )", connection /* , transaction */ ) )
{
    command.Parameters.Add( new SqlParameter( "@MyTextValue", "O'Corner... \"<>,;.,#+*%!?&´`§~" ) );
    command.ExecuteNonQuery();
}
// ...

I hope that helps!?