- Introduction To SQL
- SQL Syntax
- SQL SELECT Statement
- SQL SELECT DISTINCT Statement
- SQL WHERE Clause
- SQL AND, OR And NOT Operators
- SQL ORDER BY Keyword
- SQL INSERT INTO Statement
- SQL NULL Values
- SQL UPDATE Statement
- SQL DELETE Statement
- SQL TOP, LIMIT, FETCH FIRST Or ROWNUM Clause
- SQL MIN() And MAX() Functions
- SQL COUNT(), AVG() And SUM() Functions
- SQL LIKE Operator
- SQL Wildcards
- SQL IN Operator
- SQL BETWEEN Operator
- SQL Aliases
- SQL Joins
- SQL INNER JOIN Keyword
- SQL LEFT JOIN Keyword
- SQL RIGHT JOIN Keyword
- SQL FULL OUTER JOIN Keyword
- SQL Self Join
- SQL UNION Operator
- SQL GROUP BY Statement
- SQL HAVING Clause
- SQL EXISTS Operator
- SQL ANY And ALL Operators
- SQL SELECT INTO Statement
- SQL INSERT INTO SELECT Statement
- SQL CASE Statement
- SQL NULL Functions
- SQL Stored Procedures For SQL Server
- SQL Comments
- SQL Operators
- SQL CREATE DATABASE Statement
- SQL DROP DATABASE Statement
- SQL BACKUP DATABASE For SQL Server
- SQL CREATE TABLE Statement
- SQL DROP TABLE Statement
- SQL ALTER TABLE Statement
- SQL Constraints
- SQL NOT NULL Constraint
- SQL UNIQUE Constraint
- SQL PRIMARY KEY Constraint
- SQL FOREIGN KEY Constraint
- ALTER TABLE Orders DROP CONSTRAINT FK_PersonOrder;
- SQL DEFAULT Constraint
- SQL CREATE INDEX Statement
- SQL AUTO INCREMENT Field
- SQL Working With Dates
- SQL Views
- SQL Injection
- SQL Hosting
- SQL Data Types For MySQL, SQL Server, And MS Access
SQL Injection
SQL Injection
SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.
Practice Excercise Practice now
SQL In Web Pages
SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.
Look at the following example which creates a SELECT statement by adding a variable (txtUserId) to a select string. The variable is fetched from user input (getRequestString):
Example
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
The rest of this chapter describes the potential dangers of using user input in SQL statements.
Practice Excercise Practice now
SQL Injection Based On 1=1 Is Always True
Look at the example above again. The original purpose of the code was to create an SQL statement to select a user, with a given user id.
If there is nothing to prevent a user from entering "wrong" input, the user can enter some "smart" input like this:
UserId:
Then, the SQL statement will look like this:
The SQL above is valid and will return ALL rows from the "Users" table, since OR 1=1 is always TRUE.
Does the example above look dangerous? What if the "Users" table contains names and passwords?
The SQL statement above is much the same as this:
A hacker might get access to all the user names and passwords in a database, by simply inserting 105 OR 1=1 into the input field.
Practice Excercise Practice now
SQL Injection Based On ""="" Is Always True
Here is an example of a user login on a web site:
Username:
Password:
Example
uPass = getRequestString("userpassword");
sql = 'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"'
Result
A hacker might get access to user names and passwords in a database by simply inserting " OR ""=" into the user name or password text box:
User Name:
Password:
The code at the server will create a valid SQL statement like this:
Result
The SQL above is valid and will return all rows from the "Users" table, since OR ""="" is always TRUE.
Practice Excercise Practice now
SQL Injection Based On Batched SQL Statements
Most databases support batched SQL statement.
A batch of SQL statements is a group of two or more SQL statements, separated by semicolons.
The SQL statement below will return all rows from the "Users" table, then delete the "Suppliers" table.
Example
Look at the following example:
Example
txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
And the following input:
User id:
The valid SQL statement would look like this:
Result
Practice Excercise Practice now
Use SQL Parameters For Protection
To protect a web site from SQL injection, you can use SQL parameters.
SQL parameters are values that are added to an SQL query at execution time, in a controlled manner.
ASP.NET Razor Example
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
Note that parameters are represented in the SQL statement by a @ marker.
The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.
Another Example
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);
Examples
The following examples shows how to build parameterized queries in some common web languages.
SELECT STATEMENT IN ASP.NET:
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserId);
command.ExecuteReader();
INSERT INTO STATEMENT IN ASP.NET:
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();
INSERT INTO STATEMENT IN PHP:
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();
Practice Excercise Practice now
Products
Partner
Copyright © RVR Innovations LLP 2024 | All rights reserved - Mytat.co is the venture of RVR Innovations LLP