Dynamic SQL

[Misbah Arefin]

User Input Validation in ASP.NET
http://msdn2.microsoft.com/en-us/library/ms972961.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

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...

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

 

[Mr. R]

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...

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

Mark, it's funny how this explains another problem I had. In another
database I have URLs stored in the database. To make things easy I thought
I'd enter the html code it self and view that in a ViewList. But what I got
out was the chtml code written with html excape sequences. The document you
now showed me explains why. When I enter the < and " into the Data Editor it
replaces it with escape characters like in a C++ string "\\" or "\n".

If I get it correct using .NET Parameterised inputs the parser replaces for
example ; with an excape sequence leaving my tables safe.

Of course if you have a large database used by many people there should be
one user as administrator or root if you like. The ordinary user such as
affiliates should only be allowed update, insert and delte user data. Not
delete the table it self.

Since this project is mainly to lear ASP.NET and not databases this managing
in the database sin't my main goal.

Quote from the page
==========
Start by constraining input in the server-side code for your ASP.NET Web
pages. Do not rely on client-side validation because it can be easily
bypassed. Use client-side validation only to reduce round trips and to
improve the user experience.
======

I have said this many times. If the user can do wrong some one sooner or
later will do so.

When Binding a RegularExpressionValidator what does the following mean.
Eval("company", "{0}")

I only saw examples how to format date, time, numbers etc and no way of
format strings. Does this mean that any string you enter is legal. How do
you evaluate for alpha numerical characters spaces and underscores. Is there
a way to let the validator test for a cet of characters. For example
"#0123456789"?

When I tested to write ";;;;;;;;;;;;;;;;" in a TextBox it stored
";;;;;;;;;;;;;;;;" in the database.


Lars

 

[Mr. R]

Great page, pretty much what I needed.

Tried this

<tr>

<td align="right" class="style6">Username</td>

<td class="style7">
<asp:TextBox ID="tbInsertUsername" runat="server"
class="style_datafield" Text='<%# Bind("username") %>' />
<asp:RegularExpressionValidator ID="RegularExpressionValidator1"
runat="server"
ControlToValidate="tbInsertCompany" ErrorMessage="Comapny
can only be characters and blanks"
ValidationExpression="&quot;a-zA-Z
0-9{6,50}&quot;">*</asp:RegularExpressionValidator>
</td>
<td>
<asp:RequiredFieldValidator ID="rfvInsertUsername" runat="server"
ErrorMessage="Username is iequired"
ControlToValidate="tbInsertUsername">*</asp:RequiredFieldValidator>
</td>
</tr>

Questions:

Can I use both RequiredFieldValidator and RegularExpressionValidator for the
same Control. If RequiredFieldValidator works correctly users shouldn't be
allowed to enter empty tbInsertUsername.

It doesn't seam to do what I want. I can still enter )#/¤#=

BTW

In stead of using InsertCostomer<TextBox> I us <tb>InsertCustomer naming
style. An old habit from a crash course I took in SQL Windows 11 years ago.
Still use the same for all my projects. lbl for Labels.



Lars

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...

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

Mark, it's funny how this explains another problem I had. In another
database I have URLs stored in the database. To make things easy I thought
I'd enter the html code it self and view that in a ViewList. But what I
got out was the chtml code written with html excape sequences. The
document you now showed me explains why. When I enter the < and " into the
Data Editor it replaces it with escape characters like in a C++ string
"\\" or "\n".

If I get it correct using .NET Parameterised inputs the parser replaces
for example ; with an excape sequence leaving my tables safe.

Of course if you have a large database used by many people there should be
one user as administrator or root if you like. The ordinary user such as
affiliates should only be allowed update, insert and delte user data. Not
delete the table it self.

Since this project is mainly to lear ASP.NET and not databases this
managing in the database sin't my main goal.

Quote from the page
==========
Start by constraining input in the server-side code for your ASP.NET Web
pages. Do not rely on client-side validation because it can be easily
bypassed. Use client-side validation only to reduce round trips and to
improve the user experience.
======

I have said this many times. If the user can do wrong some one sooner or
later will do so.

When Binding a RegularExpressionValidator what does the following mean.
Eval("company", "{0}")

I only saw examples how to format date, time, numbers etc and no way of
format strings. Does this mean that any string you enter is legal. How do
you evaluate for alpha numerical characters spaces and underscores. Is
there a way to let the validator test for a cet of characters. For example
"#0123456789"?

When I tested to write ";;;;;;;;;;;;;;;;" in a TextBox it stored
";;;;;;;;;;;;;;;;" in the database.


Lars