Dynamic SQL

[Mr. R]

Hi

I need to send dynamic SQL commands to the database. For example:

update AffiliateAccount
(
username,
company,
contactName,
address,
city,
zip,
state,
country,
homepage,
email,
phone,
fax,
pwd
)
values
(
"jdoe",
"Johwn Doe Music Ltd",
"John Doe",
"Some streeed",
"Los Angeles",
"ZIP 345",
"California",
"USA",
"www.johndoeltd.com",
"(e-mail address removed)",
"+1 555 5555 5555",
"+1 555 5555 5556",
"yankedoodleday"
);

The values is given by TextBoxes in an aspx page. I don't want to use
GridViews for adding and manigin user information. It looks more
professional if it is made by ordanary forms instead.


What type ASP.NET of componant do I use for this? Can I use a DataSet or is
there any SQLCommand component I can use.


Any suggestin is aprecheated.

Lars

 

[Mark Rae [MVP]]

I need to send dynamic SQL commands to the database.
The values is given by TextBoxes in an aspx page. Any suggestin is
appreciated.

Before you go any further with this, open up Google and search for "SQL
Injection"...

If your site is currently live on the Internet, take it down immediately...

 

[Scott Roberts]

Hi

I need to send dynamic SQL commands to the database. For example:

update AffiliateAccount
(
username,
company,
contactName,
address,
city,
zip,
state,
country,
homepage,
email,
phone,
fax,
pwd
)
values
(
"jdoe",
"Johwn Doe Music Ltd",
"John Doe",
"Some streeed",
"Los Angeles",
"ZIP 345",
"California",
"USA",
"www.johndoeltd.com",
"(e-mail address removed)",
"+1 555 5555 5555",
"+1 555 5555 5556",
"yankedoodleday"
);

The values is given by TextBoxes in an aspx page. I don't want to use
GridViews for adding and manigin user information. It looks more
professional if it is made by ordanary forms instead.


What type ASP.NET of componant do I use for this? Can I use a DataSet or
is there any SQLCommand component I can use.


Any suggestin is aprecheated.

Lars

Interestingly enough, the SQLCommand component is called SqlCommand.

SqlConnection cn = new SqlConnection("my_connection_string");
SqlCommand cmd = new SqlCommand("update AffiliateAccount set company =
@company where keyvalue = @keyvalue", cn);
cmd.Parameters.AddWithValue("@company", TextBox1.Text);
cmd.Parameters.AddWithValue("@keyvalue", SomeKeyValue);
cmd.ExecuteNonQuery();

As Mark Rae implied, simply coding GUI text directly into the SQL statement
is a bad idea. Be sure to use query parameters (or stored procedures if you
prefer).

 

[George Ter-Saakov]

My guess would be that this guy far far far away from real website.....

George

 

[Mr. R]

HI

The problem was easy to solve.

I used s SQLDataSource and set the SelectCommand to access the information.
Once I found out the way to do it it was as easy as a pie.

Here's the code

protected void pbEdit_Click(object sender, EventArgs e)

{

sdsAffiliateAccount.SelectCommand =

"select username, company, contactName, address, city, zip, state,
country, homepage, " +

" email, phone, fax, pwd from AffiliateAccount " +

"where ('" + tbUsername.Text + "' = username) and ('" + tbPassword.Text
+ "' = pwd);";

}

Sice I have a DetailesView connected to the SQLDataSOurce the data fills the
form nicely.


Lars


I do have a website that works this way but it's written in PHP and HTML.
Works just fine. My affiliates can log in and track how many installs they
have distributed. I can also track the number of installs my products have.
Note NON unique installs. To track unique installs within EU is illegal. The
purpose for the database is to make sure my affiliate gets paid for every
install they generate. My website have more than 1000 visitors a day. Far
from a real web site? Far from a real ASP.NET site? Not that, I just need to
set up the database and hoast it some place. Don't know if my current ISP
supports ASP.NET.

But yes I'm new to ASP.NET.

Lars

My guess would be that this guy far far far away from real website.....

George

 

[Mark Rae [MVP]]

Once I found out the way to do it it was as easy as a pie.

Here's the code

protected void pbEdit_Click(object sender, EventArgs e)
{
sdsAffiliateAccount.SelectCommand =
"select username, company, contactName, address, city, zip, state,
country, homepage, " +
" email, phone, fax, pwd from AffiliateAccount " +
"where ('" + tbUsername.Text + "' = username) and ('" + tbPassword.Text
+ "' = pwd);";
}

Oh my God!!! Please tell me that this isn't on the live Internet...

 

[Mr. R]

Hi

Since I'm nerw to ASP.NET can you okease tell me why this is dangerous?

No, this actual source is NOT live on the Internet. Any other suggestion
that doesn't need to add users to the database.

Lars

 

[Scott Roberts]

You should Google for "SQL Injection" as Mark suggested in his first reply.

Have you tried logging in as this user?

N00bHax0r') or 1=1; --

Hi

Since I'm nerw to ASP.NET can you okease tell me why this is dangerous?

No, this actual source is NOT live on the Internet. Any other suggestion
that doesn't need to add users to the database.

Lars

 

[Mark Rae [MVP]]

Since I'm new to ASP.NET can you please tell me why this is dangerous?

I already told you - SQL Injection:
http://technet.microsoft.com/en-us/library/ms161953.aspx

No, this actual source is NOT live on the Internet.

Glad to hear it.

Any other suggestion that doesn't need to add users to the database.

Do not, under *ANY* circumstances, build up dynamic SQL from form fields...

E.g., if someone were to enter the data below in your tbUsername TextBox:

1=0); DROP TABLE AffiliateAccount;--

the resulting SQL sent to the database would be something like:

select username, company, contactName, address, city, zip, state, country,
homepage, email, phone, fax, pwd from AffiliateAccount where (1=0);

DROP TABLE AffiliateAccount;

--= username) and ('MyPassword' = pwd);

The first line is perfectly valid SQL, and it would be processed, returning
no data.
The second line (the DROP TABLE line) is also perfectly valid SQL and would
also be processed.
The third line would be treated as a comment and, therefore, ignored.

 

[Mr. R]

Login where?

To the server or Windows.

The security issue is not that improtant on the webpages. The page shall not
be live 24/7 only when I do the demos.

Don't need to login to the database. There's no users created to it yet. THe
user creates their own account.

Anyhow I still don't see the problem, of course the real live site needs
checking that the right carracters are entered to the fields.

OF course entering username N00bHax0r') will cause an error. Isn't that vat
the validators are for?

Can I use any validator to test this. Some how the user must enter username
and password and the other data the vry first time. I get your point and the
problem is familiure to me. I deal with that type of input checking in all
my exe program. But thanks for reminding me. At the moment my focus is to
get the database work, understanding how ASP.NET deals with database. The
finnish of input forms will be dealth with at a later time.


protected void pbEdit_Click(object sender, EventArgs e)
{
// Of course:
// Before calling this make sure every field has valid caracters.
// is the Validators good for this ?????

sdsAffiliateAccount.SelectCommand =
"select username, company, contactName, address, city, zip, state,
country, homepage, " +
" email, phone, fax, pwd from AffiliateAccount " +
"where ('" + tbUsername.Text + "' = username) and ('" +
tbPassword.Text + "' = pwd);";
}

You should Google for "SQL Injection" as Mark suggested in his first
reply.

Have you tried logging in as this user?

N00bHax0r') or 1=1; --

 

[Mr. R]

E.g., if someone were to enter the data below in your tbUsername TextBox:

1=0); DROP TABLE AffiliateAccount;--

Get the point. I guess I have to validate the commands before sending them
to the SQL Server. When I write programs that access databases with dynamic
SQL you always make sure that the data entered is correct.

But you get the problem any how I guess the very first time the use has to
enter data. If you know please advice how to use the validarors for this.

Lars

 

[Mark Rae [MVP]]

Get the point. I guess I have to validate the commands before sending them
to the SQL Server. When I write programs that access databases with
dynamic SQL you always make sure that the data entered is correct.

No you don't! You simply *NEVER EVER* use dynamic SQL built up from form
fields...

You use either parameterised queries or stored procedures...

 

[Mr. R]

Hi

Stored procedures is a good suggestion i guess. Although a bit more
complicated. As for parameterised values doesn't that give the same type of
errors.

Suggestion you have a stored procedure. How would you avoid the problem.
Doesn't stored procedures have to deal with SQL commands? Can a stored
procedure use parameterised quiries. How does such look.

Doesn't parameterised updates or selections generate SQL commands?

If you have worked with ASP.NET how do I add parameters to SQL commands. I
have done similar in Delphi and their Database components. Although it was 8
years ago.

Thanks for your advise.
Lars

No you don't! You simply *NEVER EVER* use dynamic SQL built up from form
fields...

As long as you make sure there is impossible to enter fault data and check
that *NEVER EVER* SQL commands parameters (values) are faulty or have
incorrect characters I see no problem.

Thenyou can not enter
1=0); DROP TABLE AffiliateAccount; --=

There are incorrect characters in that line. But then this must be checked
just before sending the SQL command.

 

[Mark Rae [MVP]]

Thanks for your advice.

Don't take this the wrong way, but you really should consider a basic
introduction to ASP.NET rather than hacking around with it... You're going
to find it a huge and frustrating uphill struggle unless you gain a
fundamental understanding of how it works...

I suggest you get a copy of this:
http://www.amazon.com/ASP-NET-3-5-D...bs_sr_3?ie=UTF8&s=books&qid=1202416148&sr=8-3
and work your way through it...

 

[Mr. R]

Don't take this the wrong way, but you really should consider a basic
introduction to ASP.NET rather than hacking around with it... You're going
to find it a huge and frustrating uphill struggle unless you gain a
fundamental understanding of how it works...

I don't take it the wrong way, not as a professional programmer. I have some
introduction Videos that I'm look at. But it takes a lot of time. It would
be beter to have a good book. So far I 've comed to the section of GridViews
and DetailesView, SQLDataSource adding and managing. Adding a SQL Database
to the projct. The videos also introduced the Master Page technique that I
find very useful for my projects. I downloaded the videos for free at
www.LearnVisualStudio.net. Paying for a crash course is out of the option at
the moment. And there aren't that many available where I live. Have to go
away for a week or two for tousands of dollars.

Any suggestion for self studies are aprecheated.

Lars

 

[Mark Rae [MVP]]

Any suggestion for self studies are aprecheated.

Buy the book I suggested, and work your way through it... It's not very
expensive, and you should be able to get through it in a weekend...

 

[Mr. R]

No you don't! You simply *NEVER EVER* use dynamic SQL built up from form
fields...

You use either parameterised queries or stored procedures...

I got the point and changed to parameterised update. However when using the
followng SQL command.

select * from [aTable] where [username] = @username;

What happens when the username has the value SOMEUSER and that user
exists.....

"SOMEUSER";DROP TABLE [aTable];--=

*) Does the SQLDataSource only send one SQL command to the server? or is
this server dependant. Shurley you would get the same program with Stored
procedures.

*) Does the SQLDataSource component generate SQL commands that are sent to
the database?

*) If so we still have the problemas if I would build Strings and send to
the server.

*) Do I have to add protection so the user can't enter invalid characters
such as ";" and how do you do that in C# and ASP.NET? As far as I see
there's no other way to be sure than to make sure the user can't enter
invalid values to the database. Does C# have any build in functions to check
this. I use the validators to verify password and for making sure the user
enters no empty (null) fields. But feel I need to a validators so that the
use can't enter an invalid parameter. For example a phone number should only
insude numbers and spaces. A string field should be alpha numeric etc. The
demo viedeos haven't discussed this (yet). But the videos are to slow. Have
to watch a video for 30 minutes just for the guy to show how to add a
FormsView. Readding the database connection string, same select commands
again and again. This is trivial to me. Upon that the guy in the videos uses
"absolute positioning", brrr.


Lars

 

[Mark Rae [MVP]]

No you don't! You simply *NEVER EVER* use dynamic SQL built up from form
fields...

You use either parameterised queries or stored procedures...

I got the point and changed to parameterised update. However when using
the followng SQL command.

select * from [aTable] where [username] = @username;

What happens when the username has the value SOMEUSER and that user
exists.....

"SOMEUSER";DROP TABLE [aTable];--=

http://msdn2.microsoft.com/en-us/library/ms998271.aspx

 

[Scott Roberts]

No you don't! You simply *NEVER EVER* use dynamic SQL built up from form
fields...

You use either parameterised queries or stored procedures...

I got the point and changed to parameterised update. However when using
the followng SQL command.

select * from [aTable] where [username] = @username;

What happens when the username has the value SOMEUSER and that user
exists.....

"SOMEUSER";DROP TABLE [aTable];--=

*) Does the SQLDataSource only send one SQL command to the server? or is
this server dependant. Shurley you would get the same program with Stored
procedures.

*) Does the SQLDataSource component generate SQL commands that are sent to
the database?

*) If so we still have the problemas if I would build Strings and send to
the server.

*) Do I have to add protection so the user can't enter invalid characters
such as ";" and how do you do that in C# and ASP.NET? As far as I see
there's no other way to be sure than to make sure the user can't enter
invalid values to the database. Does C# have any build in functions to
check this. I use the validators to verify password and for making sure
the user enters no empty (null) fields. But feel I need to a validators so
that the use can't enter an invalid parameter. For example a phone number
should only insude numbers and spaces. A string field should be alpha
numeric etc. The demo viedeos haven't discussed this (yet). But the videos
are to slow. Have to watch a video for 30 minutes just for the guy to show
how to add a FormsView. Readding the database connection string, same
select commands again and again. This is trivial to me. Upon that the guy
in the videos uses "absolute positioning", brrr.

http://msdn2.microsoft.com/en-us/library/ms161953.aspx

 

[Misbah Arefin]

Use SQL parameters for data access. You can use these parameters with stored
procedures or dynamically constructed SQL command strings. Parameter
collections such as SqlParameterCollection provide type checking and length
validation. If you use a parameters collection, input is treated as a
literal value, and SQL Server does not treat it as executable code. An
additional benefit of using a parameters collection is that you can enforce
type and length checks. Values outside of the range trigger an exception.
This is a good example of defense in depth.

To constraint the user to specific text/format use the validatoin controls
in ASP.NET e.g. RegularExpressionValidator
Also, in the rare event when the client side validation did not work
(jscript / browser) you must also validate the input in the server side code

--
Misbah Arefin

No you don't! You simply *NEVER EVER* use dynamic SQL built up from form
fields...

You use either parameterised queries or stored procedures...

I got the point and changed to parameterised update. However when using
the followng SQL command.

select * from [aTable] where [username] = @username;

What happens when the username has the value SOMEUSER and that user
exists.....

"SOMEUSER";DROP TABLE [aTable];--=

*) Does the SQLDataSource only send one SQL command to the server? or is
this server dependant. Shurley you would get the same program with Stored
procedures.

*) Does the SQLDataSource component generate SQL commands that are sent to
the database?

*) If so we still have the problemas if I would build Strings and send to
the server.

*) Do I have to add protection so the user can't enter invalid characters
such as ";" and how do you do that in C# and ASP.NET? As far as I see
there's no other way to be sure than to make sure the user can't enter
invalid values to the database. Does C# have any build in functions to
check this. I use the validators to verify password and for making sure
the user enters no empty (null) fields. But feel I need to a validators so
that the use can't enter an invalid parameter. For example a phone number
should only insude numbers and spaces. A string field should be alpha
numeric etc. The demo viedeos haven't discussed this (yet). But the videos
are to slow. Have to watch a video for 30 minutes just for the guy to show
how to add a FormsView. Readding the database connection string, same
select commands again and again. This is trivial to me. Upon that the guy
in the videos uses "absolute positioning", brrr.


Lars