Answer Happy

Example of How it works: You have a web page for a user login. The user enters the login name and password from HTML input elements. In SQL, the SELECT statement will pull records the match the criteria in the WHERE clause, so you need to pull the record based on the correct login and password. Here is what the SQL may look like: SELECT Statement keywords SELECT Account Number FROM Users WHERE Username = "Bonnie" and Password = "zebra"; . But, of course, you need to use the values entered into the textboxes which will become variable in your server-side code. So your string SQL statement might look like this: . SELECT -- this is followed by the fields to display or retrieve. In this case, Account Number • FROM -- this is followed by the table name. In this case -- User WHERE -- this is like if logic that is true or false. Notice the Logical AND operator. Whatever records are true based on the condition in the WHERE clause will be retrieved. this starts a comment in SQL String SQLStatement; SQLStatement = "SELECT Account Number FROM Users WHERE Username = + userName + "' and Password = + password +"'; "; I hope you have coded string concatenation in the past. I do this all the time as a database programmer. In C#, + can mean add if the operands are numeric. It also means concatenation if the operands are string. Since the content of the variables userName and password are strings -- they need to be in quotes in the parsed SQL statement, this is the reason for the single quotes nested in the double quotes. OR 1=1 -- User Name: Password: The password can be literally anything. It won't matter. Once the C# processing the statement with the above input.

Step 1: Substitute the strings in: SQL Statement = "SELECT Account Number FROM Users WHERE Username = "" + ' OR 1=1 --' +" and Password = + Anything in the world! +"'; "; Step 2: Concatenate: SQLStatement = "SELECT Account Number FROM Users WHERE Username = OR 1=1 --' and Password = + Anything in the world! '; "; Step 3: Note the green that is a comment so all of that is ignored --> The SQL statement becomes: SELECT Account Number FROM Users WHERE Username = OR 1=1 ; Since 1=1 is always TRUE for every record (no == operator in SQL) then every record has the Account Number retrieved! NOTE: There are other ways to trap SQL injection when user input is used to build the query . parameterized queries stored procedures .

TO DO: For this lab, you will write a regular expression that checks for characters you do not want for SQL Injection. You should validate the the userNameTextbox does not contain the following characters (from Microsoft 2): Input character Meaning in Transact-SQL i Query delimiter Character data string delimiter. -- Single-line comment delimiter. Text following -- until the end of that line is not evaluated by the server. * ... */ Comment delimiters. Text between /* and */ is not evaluated by the server. XP- Used at the start of the name of catalog-extended stored procedures, such as xp_cmdshell. Before starting -- since I am not providing the regular expression, you need to design the regular expression that works. You may want to use regular expression 101 -- to test your patterns e to test. I created a pattern that would test positive if any of the characters above are in the input string. One point for each correct item in the pattern Items that should NOT be in the username string: • /* */ . хр.

Create an empty ASP.NET web site and add the following: • Two web forms o Login.aspx o Pagetwo.aspx -- just add some text to this page • On Login.aspx add: o <%@ Import namespace = "System.Text.RegularExpressions" %> on line 2 Web.config Login.aspx + x <%@ Page Language="C#" %> <%@ Import namespace = "System.Text.RegularExpressions" %> 3 o label and textbox for username • set maximum length to 40 o label and textbox for password · set the TextMode property to Password • set maximum length to 20 o Required field validators for the textboxes above with appropriate error message o Submit button o Label which will display after the submission is the input is not valid • Add the following to the web.config file <appSettings> <add key="ValidationSettings:UnobtrusiveValidationMode" value="None"></add> </appSettings> O

Web.config + x Login.aspx <?xml version="1.0"?> <!-- For more information on how to configure your ASP.NET application, please visit https://go.microsoft.com/fwlink/?LinkId=169433 <configuration> <appSettings> <add key="ValidationSettings:UnobtrusiveValidationMode" value="None"></add> </appSettings> <system.web> <compilation debug="true" targetFramework="4.8" /> <httpRuntime targetFramework="4.8" /> </system.web> <system.codedom> <compilers> <compiler language="c#ics;csharp" extension=".cs" type="Microsoft.CodeDom. Provi <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsof </compilers> </system.codedom> </configuration Create a validateUser method return TRUE if none of the invalid characters are in the username textbox · return FALSE if any of the invalid characters are in tthe username textbox Create a click event handler for the button · get the input from the textboxes and assign it to a variable - if the username input is valid (no bad characters) then create the SQL string • you will not access a database but that would be the next step to code after creating the SQL string · assume you have accessed the database and the username and password are correct at this point - if the username and password are valid and the result of the validateUser method is true the redirect user to page two • if the above is not true then display the error message on the page.