A Database/Web App with User Input
The Goals for this Example
It would be nice to add user input to our previous web app.
Many web applications let the user supply input that is used to select what data to display.
Thus, we need to learn how to get user input and then use it to query an SQL Server database, with
the results then displayed on the web form.
Creating the Example
What the App Should Do
- Like the previous web app, this one will display auto parts data
on a web form for a fictitious store.
- However, this time the app has the user enter a minimum and maximum price for the range of
prices that the person wishes to have displayed. Thus this application displays selected
data items, instead of always giving all of the data.
- We will also have the web app validate the user input.
- The initial view of the application
shows a web page with a few labels and a button.
- Once the button is clicked, the web app shows the
selected auto parts data.
|
Starting the Web App
- As usual, make a new project of type Visual Basic Project and having template
ASP.NET Web Application. In the location box put in a reasonable location such as:
- https://cis3.stvincent.edu/studentc/StoreInput
- http://cis2.stvincent.edu/studentc/StoreInput
- We will assume from here on that the folder name used is StoreInput.
- Change the name of the web form, WebForm1.aspx, to home.aspx.
- From the Web Forms section of the Toolbox, add the 5 labels for the top of your
web form as shown in the initial view of the application.
- Save your work so far by using File, Save All.
|
User Input
- Add a text box, with ID MinTextBox, just below the label for the minimum. Resize it as
needed.
- To quickly add a second text box, just click on the first one. Then do Edit, Copy and Edit,
Paste. Drag the new one just below the maximum label and set the ID for the text box to MaxTextBox.
- Whenever we have user input in a web app, it is very important to validate that input
and to do so at the server end. That's because anyone on the Internet can attack your
web app by supplying malicious input. If you don't verify that the input is reasonable,
an attacker might be able to supply input that gets data from your database that the
attacker should not see. Worse yet, the attacker might be able to change the data in your
database. Even in applications, such as guestbooks, where you want users to post data, you
need to validate the data to be sure that an attacker is not able to save some nasty script
to your database, a script that would get run for some other poor user who views what is in
the guestbook. (This is called a cross-site scripting attack.) Visual Studio says that it
provides some automatic checks against these script attacks, but it is still best to explicitly
check to see that the data is reasonable yourself.
- Although checking user data at the client end (at the user's browser) is often done to assist
the user in filling in reasonable data, it is no good as a protection against an attacker.
That is because the attacker has complete control over his/her computer and can totally bypass
any JavaScript of similar code user to check the data at the browser. Thus we will use
validators, as we did in an earlier app,
since they always run on the server end (though they can in addition check at the client end
if the EnableClientScript property is set to True for each validator -- which they are by default).
- From the Web Forms section of the Toolbox, drag a RequiredFieldValidator onto your form, just
below the text box for the minimum. Change the ErrorMessage field to "Min needed" and the
ControlToValidate to MinTextBox. We thus force the user to enter a minimum, even if it is only 0.
- Copy this validator and place it underneath the text box for the maximum. Change the
ErrorMessage to "Max needed" and the ControlToValidate to MaxTextBox.
- These RequiredFieldValidators probably work best if the user's browser has JavaScript enabled
when viewing your web app. That way they can quickly check at the client end and warn the user
if one of the fields is empty. If JavaScript is turned off, then the empty strings in these boxes
will be sent back to the server. The round-trip to the server and back slows down the display
of the validator error messages.
- Drag a RangeValidator onto your form just underneath the "MinNeeded" validator. Change the
ErrorMessage to "Use a number from 0 to 50000" and set the ControlToValidate to MinTextBox.
Set the MinimumValue to 0.0 and the MaximumValue to 50000.0 so as to define the acceptable
range of numbers. Set the Type field to Currency. (If you do not do this, the type will be
String. Since we want a range of numbers, not strings, you will get unexpected results
if you leave the Type field at String.) The user input will be converted (if possible)
into a currency value and then only values that fit the specified range will be accepted.
The range validator is our main way of filtering out malicious input in this application.
- Here is an image of what we have thus far.
- Copy the last validator and drag the copy to just underneath the "MaxNeeded" validator.
Change the ControlToValidate to MaxTextBox but keep everything else the same.
- More is needed to do complete filtering of bad input as explained in this quote from the
Visual Studio help system: If the input control is empty, no validation functions are
called and validation succeeds. Use a RequiredFieldValidator control to prevent the user from
skipping an input control. Similarly, if the value of the input control cannot be converted to
the data type specified by the Type property, validation also succeeds. It is strongly
recommended that an additional CompareValidator control, with its Operator property set to
ValidationCompareOperator.DataTypeCheck, be used to verify the data type of the input value.
If you wish to try this, here are the directions:
- Drag a CompareValidator onto your form and place it near the MinTextBox.
- In this validator's properties, set the ControlToValidate (not the ControlToCompare)
field to MinTextBox.
- Change the ErrorMessage field to "Must use currency".
- In the Operator field select DataTypeCheck.
- In the Type field select Currency.
- Add a button from the Web Forms section of the Toolbox. Place this button to the right
of the text boxes, changes its ID to MatchesButton, and change its Text to "List Matches".
- It would be best to do a File, Save All at this point.
|
Security
- Since security was mentioned in the section above on user input, let's expand on some of
the security issues in web applications here.
- Many of the typical attacks directed against web apps involve malicious input (as mentioned above).
This could be a script, enclosed in script tags and entered into a text box, or perhaps it could
be an SQL command appended to expected input in a text box. (The former is a cross-site
scripting attack, while the latter would be an attempt at an SQL injection attack.)
- The usual solution to the above problems is to filter user input at the server.
- Here are some relevant quotes from the Visual Studio help system:
- By default, the Web Forms page automatically validates that malicious users
are not attempting to send script to your application. So, there is built-in
protection from the script attacks. However, one should still further filter
user input.
- Validation controls perform input checking in server code. When the user submits
a form to the server, the validation controls are invoked to review the user's input,
control by control.
- If the user is working with a browser that supports DHTML, the validation controls
can also perform validation using client script. This can substantially improve response
time in the page; errors are detected immediately and error messages are displayed as
soon as the user leaves the control containing the error. Check that
the EnableClientScript property on your validators is true if you want client-side
script to run (in addition to the server-side code for the validators).
- The page framework performs validation on the server even if the validation controls
have already performed it on the client, so that you can test for validity within your
server-based event handlers. In addition, it helps prevent users from being able to bypass
validation by disabling or changing the client script.
- Validation occurs after page initialization (that is, after view-state and postback
data have been processed) but before Change or Click event handlers are called.
You can perform the validation procedure earlier by calling the control's Validate method,
for example during page load.
- For further information on web app security, use Help, Search to look for the following topics
in Visual Studio:
- Scripting Exploits
- Protecting Against Script Exploits in a Web Application
- Overview of Web Application Security Threats
- Introduction to Web Application Security
- ASP.NET Web Application Security
- How ASP.NET Security Works
- Basic Security Practices for Web Applications
|
Getting Data from the Database
- Drag a label onto the form just below everything we have so far
and rename it as PartsLabel. The text for it should be
"List of Selected Parts". Set its visible property to false. We will make it visible when
we display the data on the form.
- The data will be looked up in the same Parts database that we used
in the previous web app.
- Drag a DataGrid from the WebForms section of the Toolbox to the form.
Place it just below the above label and resize it to fill most of the bottom half of the
screen. We will use this data grid to hold the data that we look up.
- While the data grid is selected use AutoFormat under Properties. Select Colorful1.
- Also drag another label onto the form, between the button and the DataGrid, and change its ID to
ErrorLabel. Set the Text to "Error in accessing database", change the foreground color to red, and
put False in the Visible field. We will make this label visible in the code if
an exception is detected.
- Check your work with this image of the web form.
(This image also contains a few items at the bottom that we add in the next few steps, so
do not worry about these.)
- Drag a DataView from the Data section of the Toolbox and place it at the bottom of your web form.
- Then click on the data grid on your form and change the DataSource field to DataView1.
That is, the data grid will get its data from the data view, just as in the previous app.
- Next, drag an SqlDataAdapter from the Data section of the Toolbox to the form.
This will start up a wizard.
- Select the same data connection that you used in the previous web app.
This is the one that connects to your Parts database. If for some reason that connection
does not appear, create a new one by following
the New Connection directions for the previous web app.
- Click Next.
- Select Create New Stored Procedures. Remember that we use stored procedures, not regular
SQL, for security reasons.
- Click Next.
- Select Query Builder.
- Select the Parts table. Then click on Add and Close. This is the step where you
select what table(s) to use in your database query.
- As in the previous app, check all 4 database fields (not the all columns entry)
in the top half of the window.
- Click on the field in the Sort Type column and the Price row. Use the pulldown to select
Ascending. This specifies that the results will be sorted in ascending order by price.
- Next we need to select the data that fits the correct range. Go to the field in the Criteria
column, Price row and enter
<=@Max . The @ sign indicates a parameter to the
stored procedure. You will see later in the Visual Basic code how a value is given
to a parameter in a stored procedure.
- Click in the Column column, one row beneath that Price row. Use the pulldown to select Price.
Go to the field in the Criteria column, same row and enter
>=@Min .
Notice that we have a second parameter here.
- Double check that all the rows except the last Price row have a check in the Output column.
This specifies what fields of the table will be returned as output. This should be OK
but check to be sure.
- You can see the final settings in this picture of Query Builder.
If you are familiar with SQL, you can check that the SQL in the bottom part of Query Builder
looks to be correct.
- Select OK.
- Then select Advanced Options.
- Uncheck the first box about generating insert, update, and delete statements.
- Click OK and Next.
- In the Select box on the Configuration Wizard's dialog, fill in SelectByRange as the
name of your stored procedure.
- Also be sure that the radio button is selected to have the stored procedure created
in the database for you.
- Click on Next and then on Finish.
- You also need to give the SQL or IWAM user execute permission on your stored procedure.
- As in our first database-related web app,
which database user to give execute access to the stored procedure depends on how your
server is configured. Consult your database administrator if need be.
- In Enterprise Manager, click on the Stored Procedures icon under your particular database.
- Find your SelectByRange stored procedure in the right pane and right click on it.
- Select Properties, then Permissions.
- Check the Exec box for the SQL or IWAM user as needed.
- In Visual Studio we probably need to adjust the Select Command.
- Click on the DataAdapter1 icon at the bottom of your form.
- In the Properties window for this data adapter, expand the SelectCommand item.
- Note that the CommandType is listed as StoredProcedure.
- Change the CommandText entry to yourid.SelectByRange where you replace
yourid by studentc or whatever your user ID is, the user ID that you were logged in
as when you created your stored procedure.
- Visual Studio will change the entry to look like yourid.[SelectByRange], but do not
type it in that way yourself. Let it handle this change.
- Respond Yes when asked if you want to regenerate the parameters collection for this command.
- Say Yes to the next question that warns that information may be lost and asks whether
to apply the new parameter configuration.
- You should now check to see if you can indeed get your data from the database.
- Click DataAdapter1 at the bottom of your form.
- Then in the Property window, click on the Preview Data link.
- Note that information about the 2 parameters is shown at the top right.
In the Value column fill in 10 for the @Min parameter and 50 for @Max.
- Click on the Fill Dataset button.
- You should then see your data as shown in this
picture of the preview.
- If this does not work, recheck your work on the stored procedure. You can even delete
the data adapter from your form and create a new one.
- Do File, Save All in Visual Studio before going on.
- An alternate way to check your stored procedure is available with SQL Server Enterprise Manager.
- In Enterprise Manager, select Tools and then SQL Query Analyzer.
- In the Object Browser, find your database and your particular stored procedure.
- Right click on your stored procedure, SelectByRange, and select Debug.
- In the Debug Procedure dialog box, fill in reasonable values for the input parameters
(such as 100 for @Max and 12 for @Min.)
- Here is a picture of Debug Procedure dialog box.
- Click on Execute.
- Resize the output window for this debugger and resize the components within it as
need be so that everything is visible.
- Examine this picture of the results.
- This debugger has additional features not covered here.
|
The Code-Behind File
- In Solution Explorer double click on home.aspx.vb, the "code-behind" file for your web form,
to bring it up in the editor. This is where we add the code for your web page.
- Unlike in the previous app,
we are going to try omitting the various
Imports commands.
This may make a few lines of code longer, as we will have to specify where SqlException
is found. In fact, that is probably the only place it would make a difference, so it
seems reasonable to leave out the Imports commands.
- Click on the + to show the code inside of the "Web Form Designer Generated Code" region.
- Find the section of code that sets up your labels and other items as protected fields.
- Add to the bottom of this list the following line of code so as to create a data table to use:
Protected dTable As New DataTable
- Recall that a DataTable can hold within your program the data from an actual database table.
- We will not add a click handler for the button on your form. Instead,
we will again use the fact that a postback happens whenever the user click on this button.
- Thus, we just write code to handle the postback in the Page_Load procedure.
- Make your Page_Load procedure match the following:
|
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles MyBase.Load
'Put user code to initialize the page here
'If there is no postback (e.g. the first time the page loads), nothing
'special needs to be done. But...
Dim Min, Max As String
If IsPostBack Then
'Page was reloaded, for example, when user clicked the button.
Page.Validate() ' Check all validators to see if user input is valid.
If Page.IsValid Then
MatchesButton.Visible = False
PartsLabel.Visible = True
DataGrid1.Visible = True
Min = MinTextBox.Text.ToString()
Max = MaxTextBox.Text.ToString()
Try
SqlConnection1.Open()
SqlDataAdapter1.SelectCommand.Parameters("@Min").Value = Min
SqlDataAdapter1.SelectCommand.Parameters("@Max").Value = Max
SqlDataAdapter1.Fill(dTable)
DataView1 = New DataView(dTable)
DataGrid1.DataBind()
Catch exception As System.Data.SqlClient.SqlException
ErrorLabel.Visible = True
Catch exception As Exception
ErrorLabel.Visible = True
Finally
SqlConnection1.Close()
End Try
End If
End If
End Sub
|
- Next, let's analyze the code. It begins by declaring 2 local string variables Min and Max.
- If a postback has occurred, we first use Page.Validate() to cause all of the validators on
this page to run (at the server end). This is in addition to any validation already done
at the client end. The explicit call to Validate() is needed because the validators normally
only run at the server after Page_Load has been executed and before any click handlers are run.
We want to validate the user input here before we try to use it as parameters in a query
of the database. If Page.IsValid is True, we go on to process the user input. If not, we
do nothing, since whatever validator(s) found a problem with user input will display their
Text field (error message) on the page.
- Assuming that the user input validated fine, we want to read the desired data
from the database and display it in the data grid on the web form.
- As a first small step, we make the button invisible since the user
should not be clicking on it if we have already displayed the data.
- However, we do make the PartsLabel and DataGrid1 to be visible as we do want
the user to see these.
- Since this web app has user input, we next get that user data out of the text boxes
and into the variable Min and Max. The code just uses the Text property of each box
and then converts the number to a string by using the ToString function.
- As in the previous web app, a
try..catch construct is used
when accessing the database so that we can catch an exception if one occurs.
- Inside this construct we first open the connection to the database.
- Before using the data adapter to retrieve data from the database, we must fill in the
parameter values for the data adapter to use when it calls upon the SelectByRange stored procedure
that we set up in the database. Note the use of the @Min and @Max names that we
used when we created the stored procedure.
- Then we use the data adapter (and the stored procedure) to get the data from the
database and place it into the data table here inside our program.
- The data view then gets its data from the data table.
- Then the data grid is bound with the data from the data view.
- If all works OK, the last step in the procedure is to close the connection to the database.
- If an SqlException occurs, we make the error label visible
in order to warn the user. This is the most likely exception and typically occurs
when the connection to the database cannot be opened for some reason.
- If there is no SqlException, we also catch any other kind of exception
(since type Exception matches any type of exception)
and make the error label visible in this case as well. It seems unlikely that any exception
but an SqlException would occur in this code, but we have included the extra Catch just in case.
Of course, since we check for type Exception, we could remove the Catch for SqlException.
However, we leave it here in case we might want to have different code executed for
the different types of exceptions. For the SqlException we could display an error message
saying that there was a database error, whereas for any other exception we could display
a more generic message saying just that there was some kind of error.
- Note that the code is essentially the same as the
code in the previous web application,
except that we now have to deal with the parameters for the stored procedure.
|
Build Your App
- Save all of your files in Visual Studio.
- Then use Build, Build StoreInput (or whatever you named your application).
- If the build succeeded, the Output window will give you a message to that effect.
- If not, fix any problems.
|
Testing and Debugging The App
Finding Errors
- Look at your app in a browser by going to the appropriate URL. For example, the URL might be
something like one of the following:
- http://cis3.stvincent.edu/studentc/StoreInput/home.aspx
- http://cis2.stvincent.edu/studentc/StoreInput/home.aspx
- Fill in reasonable minimum and maximum values (such as 5 and 50) and click on the
List Matches button to see what you get.
- If it does not work, you might try setting the Web.config file so that you can see
any error messages, as we have done in previous web apps.
Double click the Web.config file in Solution Explorer and edit the line that says
<customErrors mode="RemoteOnly" /> so that it instead reads as
<customErrors mode="Off" />
- Save everything, rebuild your application, and refresh the initial page in your browser.
- See if you get an error message now when you click on the button.
- Often it is a matter of getting the permissions right on the stored procedure if you
get an error at this point. Note that the stored procedure may work fine when you preview
the data in Visual Studio, yet not work when called from your web app. This is because the
stored procedure is run by one user (such as SQL or IWAM) when the web app calls it, but is no doubt run
by a different user when called from Visual Studio. Consult your system admin or another
.NET web developer if you cannot get rid of an error at this point.
- When finished with debugging, undo all of your changes to Web.config, save everything,
and rebuild your app. This will leave your app in a more secure state. If a runtime
error crops up later, you don't want everyone on the Internet to be able to read the
detailed error messages.
|
|