Mirza Husain General topics, Transact-SQL, Uncategorized

SQL Injection


sqli

SQL Injection (SQLi) refers to an injection attack wherein an attacker can execute malicious SQLstatements (also commonly referred to as a malicious payload) that control a web application’s database server. Such attack affects any website or web application.

An attacker can bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL injection can also be used to add, modify and delete records in a database.

In a 2012 study, it was observed that the average web application received 4 attack campaigns per month, and retailers received twice as many attacks as other industries.

A SQL injection needs two conditions to exists :

  • A relational database that uses SQL
  • A user controllable input which is directly used in an SQL query.

Subclasses of SQLi –

  1. Classic SQLi
  2. Blind or Inference SQLi
  3. Database management system-specific SQLi
  4. Compounded SQLi

Example—

sql-example-1

Here user need to provide user name and password, if attacker provides ‘or 0=0’ as the username and password then the query will be like this.

sql-example-3

Since the inputs provided by the attacker are valid in all circumstances, the query will return all records in the database.

And by this way an attacker will be able to view the sensitive information.

How to prevent SQLi—

  • Adopt an input validation technique where user input is checked against a set of rules.
  • Users should have least privileges on the database.
  • Don’t use ‘SA’ accounts for web applications.
  • Need to have application specific database user accounts.
  • Remove all stored procedures which are not in use.

————–

Mustafa EL-Masry Transact-SQL

Use insert into select instead of cursor


In this article I will explain by code how can you make cursor to select data from table and insert it into another table but this to learning but if you want to insert data from Table to another Table I suggest to use Insert into select instead of Cursor

— Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, ‘First Server’
UNION ALL
SELECT 2, ‘Second Server’
UNION ALL
SELECT 3, ‘Third Server’
— Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
— Insert Logic
DECLARE @Flag INT
SELECT @Flag = COUNT(*) FROM ServerTable
WHILE(@Flag > 0)
BEGIN
INSERT INTO NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
WHERE ServerID = @Flag
SET @Flag = @Flag – 1
END
SELECT ServerID, ServerName
FROM NewServerTable
— Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

Remarks

To see what happens when you run this script enter F11 to open Debug Query and enter F10 to see effect step by step