This article is going to focus on the different types of SQL Injection attack methods. In a previous article, we took an introductory approach to SQL Injection and simply listed the types of SQL Injection and we managed to look at some examples of SQL injection. This time we will dive into the types of SQL Injection as well as try to give real-world examples of each type.
 
SQL Injection is a popular malicious attack on websites and web applications which involves the use of SQL statements through user input. SQL Injection may be used to tamper with organizational sensitive data, identity theft and exposing organizational sensitive data. This may result in financial loss to organizations or reputational damage of the organization or the web application/website.
 
Types of SQL Injections
The three different categories of SQL Injection are,
    In-band (Classic)
    Inferential(Blind)
    Out-of-Band

In-band Injection
Error-based SQLi and Union-based SQLi are the most commonly used In-band SQLi. In-band SQLi entails that an attack is launched and the attacker uses the same channel to obtain results.
 
Error-based SQLi
The user intentionally adds characters such as ‘,”,/*, etc to the input to invoke an error from the application

Then if you go on the code-behind file, you might find code like this,

    Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("Constring").ConnectionString)  
    Using cmd1 = New SqlCommand("select  from tbl_users where user_login='" +  user_login + "'", con)  

Notice that the server shows the user the error in syntax ‘from’ hence the attacker may use these prompts to launch an attack.

If the attacker replaces the username with,

    'OR '1'='1';Select @@version  --'  

Notice how the attacker obtains database information using the In-band method of injection and he may make use of the error dialogs to view information about the internal details of the database and may end up deleting some data.  

Union-based SQLi
As the name suggests this method makes use of the UNION SQL operator, which combines two statements. When using the SQL, UNION operator the SELECT statements must be similar, with the same number of columns and having same data types.
 
Example

i.e. SELECT user_id,user_login,password,user_type FROM customers UNION SELECT perm_id,username,password,role from Sys_Permissions 

Once an attacker has got information about the database using the @@version keyword it is easier for them to now execute UNION statements trying out various table names and column combinations.
 
Inferential SQLi (Blind)
Inferential SQLi has two groups namely the Boolean-based and Time-based. They rely on the response from the server, which means it is a trial and error exercise. The attacker sends payloads to the server, waits for a response this, and is never sure of the execution of the SQL query thus the name "Blind". Unlike In-band, the attacker does not get the data from the website database.
 
Boolean-based Injection
The attacker needs to send values to the server and the server response is either TRUE/FALSE. In Boolean-based the attackers normally depend on guessing and that is why it takes a large amount of time. Unlike with In-band Injection, there is no direct response or result that the execution has been successful. The attacker has to guess number of columns and column names in the table and the underlying query, which is executing the requests. An example of Boolean-based Injection is given below,
 
Example
http://MyShopOnline/Products/?id=4401 or 1=1
 
This means the underlying query at the server-side will be as follows and this may return a TRUE/FALSE response.
    Select * from Para_Products where prodid='2 ' OR '1'='1' --;   

Depending on the backend database, the database connection settings, and operating system an attacker can achieve to read, update, or delete arbitrary data/tables from the database.
 
The user may start by sending an SQL statement, which he knows, will return False such as 1=0, and take note of the response. He will then use this false response to determine his trial once he gets any response that is different from the latter.
 
Time-based Injection
In Time-based SQL Injection the attacker uses an SQL query to force the database to wait for a given length of time before in sends a response. The attacker uses this response time to determine his success.
 
Example
    Select * from Para_Products where prodid='2 '  OR '1'='1'   WAITFOR DELAY '0:0:5'   --;   

The time factor is used by utilizing the WAITFOR DELAY operator.
 
Out-of-band Injection
Out of the three Injection Types is the least common type of Injection. Unlike In-band, when using this method it does not use the same channel to obtain information, instead it uses DNS/HTTP protocol. This means that the web application being attacked must not have any security parameters to deny DNS or HTTP outbound requests. Out-of-band is faster than Inferential attacks.
 
These are the main types of SQL Injection and in later articles; we will look at SQL Injection Protection Methods, other injection flaws, etc. 

HostForLIFEASP.NET SQL Server 2019 Hosting

A SQL cursor is a database object that is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row. This article explains everything about SQL cursors. In this article, we will learn the following

SQL Cursor Life Cycle
The following steps are involced in a SQL cursor life cycle.

    Declaring Cursor
    A cursor is declared by defining the SQL statement.
     
    Opening Cursor
    A cursor is opened for storing data retrieved from the result set.
     
    Fetching Cursor
    When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
     
    Closing Cursor
    The cursor should be closed explicitly after data manipulation.
     
    Deallocating Cursor
    Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.

Why use a SQL Cursor?
In relational databases, operations are made on a set of rows. For example, a SELECT statement returns a set of rows which is called a result set. Sometimes the application logic needs to work with one row at a time rather than the entire result set at once. This can be done using cursors.

In programming, we use a loop like FOR or WHILE to iterate through one item at a time, the cursor follows the same approach and might be preferred because it follows the same logic.
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ] FOR select_statement
 [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]

Cursor Example
The following cursor is defined for retrieving employee_id and  employee_name from Employee table. The FETCH_STATUS value is 0 until there are rows. When all rows are fetched then  FETCH_STATUS becomes 1.
use Product_Database
SET NOCOUNT ON;

DECLARE @emp_id int ,@emp_name varchar(20),
    @message varchar(max);

PRINT '-------- EMPLOYEE DETAILS --------';

DECLARE emp_cursor CURSOR FOR
SELECT emp_id,emp_name
FROM Employee
order by emp_id;

OPEN emp_cursor

FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name

print 'Employee_ID  Employee_Name'

WHILE @@FETCH_STATUS = 0
BEGIN
    print '   ' + CAST(@emp_id as varchar(10)) +'           '+
        cast(@emp_name as varchar(20))


    FETCH NEXT FROM emp_cursor
INTO @emp_id,@emp_name

END
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

What are the limitations of a SQL Cursor?

A cursor is a memory resident set of pointers -- meaning it occupies memory from your system that may be available for other processes.
Cursors can be faster than a while loop but they do have more overhead.

Another factor affecting cursor speed is the number of rows and columns brought into the cursor. Time how long it takes to open your cursor and fetch statements.
Too many columns being dragged around in memory, which are never referenced in the subsequent cursor operations, can slow things down.

The cursors are slower because they update tables row by row.

How can we replace SQL Cursors?
There's one replacement for cursors in SQL server joins.

Suppose we have to retrieve data from two tables simultaneously by comparing primary keys and foreign keys. In these types of problems, the cursor gives very poor performance as it processes through each and every column. On the other hand using joins in those conditions is feasible because it processes only those columns which meet the condition. So here joins are faster than cursors.

The following example explains the replacement of cursors through joins.

Suppose, we have two tables, ProductTable and Brand Table. The primary key of BrandTable is brand_id which is stored in ProductTable as foreign key brand_id. Now suppose, I have to retrieve brand_name from BrandTable using foreign key brand_id from ProductTable. In these situations, cursor programs will be as follows,
use Product_Database
SET NOCOUNT ON;

DECLARE @brand_id int
DECLARE @brand_name varchar(20)


PRINT '--------Brand Details --------';

DECLARE brand_cursor CURSOR FOR
SELECT distinct(brand_id)
FROM ProductTable;

OPEN brand_cursor

FETCH NEXT FROM brand_cursor
INTO @brand_id

WHILE @@FETCH_STATUS = 0
BEGIN
    select brand_id,brand_name from BrandTable where brand_id=@brand_id
--(@brand_id is of ProductTable)

    FETCH NEXT FROM brand_cursor
INTO @brand_id

END
CLOSE brand_cursor;
DEALLOCATE brand_cursor;

The same program can be done using joins as follows,

Select distinct b.brand_id,b.brand_name from BrandTable b inner join
ProductTable p on b.brand_id=p.brand_id

As we can see from the above example, using joins reduces the lines of code and gives faster performance in case huge records need to be processed.

HostForLIFEASP.NET SQL Server 2019 Hosting

The Aggregate function is used to perform calculations on a set of values and return a single value, ignoring all null values. The Aggregate functions are often used with the group by clause of the select statement in SQL. The database management aggregate function is a function in which the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning
 
All aggregate statements are deterministic In other words, aggregate functions return the same value each time that they are called when called with a specific set of input values.
 
See Deterministic and Nondeterministic Functions for more information about function determinism The over clause may follow all aggregate functions, except the STRING_AGG, grouping or GROUPING_ID functions
 
Aggregate functions can we use the expressions only in the following situations
    The select list of a select statement (either a subquery or an outer query).
    A having clause.

Functions 
SQL provides the following aggregate functions based off of the following Student table
 
Syntax
   SELECT * FROM Student;

Example

APPROX_COUNT_DISTINCT()
This statement returns the approximate number of unique non-null values in a group.
 
Approx_count_distinct expression evaluates an expression for each row in a group, and returns the approximate number of unique non-null values in a group.
 
This function is designed to provide aggregations across large data sets where responsiveness is more critical than absolute precision.
 
Approx_count_distinct is designed for use in big data scenarios and is optimized for the following conditions

    Access of data sets that are millions of rows or higher and
    Aggregation of a column or columns that have many distinct values

This function implementation guarantees up to a 2% error rate within a 97% probability. Approx_count_distinct requires less memory than an exhaustive count distinct operation, given the smaller memory footprint
 
Approx_count_distinct is less likely to spill memory to disk compared to a precise count distinct operation.
 
To learn more about the algorithm used to achieve this, see hyperloglog.
 
Syntax
    SELECT Count (StudentId) AS Approx_Distinct_OrderKey        
    FROM Student;     

Example

AVG () 
The AVG>statement calculates the average of non-null values in a set.  
The AVG is the data type of expression is an alias data type, the return type is also of the alias data type.
However, if the base data type of the alias data type is promoted, for example from tinyint to int, the return value will take the promoted data type, and not the alias data type
AVG () computes the average of a set of values by dividing the sum of those values by the count of nonnull values
 
If the sum exceeds the maximum value for the data type of the return value, AVG() will return an error. AVG is a deterministic function when used without the over and orderby clauses. It is nondeterministic when specified with the over and order by clauses. for more information.
 
Syntax
    SELECT AVG(StudentAge) FROM Student;      

Example

CHECKSUM_AGG()
This statement returns the checksum of the values in a group. CHECKSUM_AGG ignores null values.
The over clause can follow CHECKSUM_AGG
CHECKSUM_AGG can detect changes in a table
The CHECKSUM_AGG result does not depend on the order of the rows in the table. Also, CHECKSUM_AGG functions allow the use of the Distinct keyword and the Group By clause
 
If an expression list value changes, the list checksum value list will also probably change. However, a small possibility exists that the calculated checksum will not change
 
Syntax   

    SELECT CHECKSUM_AGG(CAST(StudentID AS int))          
    FROM Student;          
    GO  

Example  

COUNT()  
It is used to count the number of rows returned in a select statement.
Count function returns the number of items found in a group. count  operates like the COUNT_BIG function
These functions differ only in the data types of their return values.
Count always returns an int data type value.COUNT_BIG always returns a bigint data type value.
 
Syntax
    SELECT COUNT (StudentName) from Student   

Example  

COUNT_BIG ()
This statement returns the number of items found in a group.COUNT_BIG operates like the count function. These functions differ only in the data types of their return values.
COUNT_BIG always returns a bigint data type value. Count always returns an int data type value. 
The COUNT_BIG(*) returns the number of items in a group. This includes null values and duplicates.
The  COUNT_BIG (all expression) evaluates expression for each row in a group, and returns the number of nonnull values.
COUNT_BIG (distinct expression) evaluates expression for each row in a group, and returns the number of unique, nonnull values.
 
Syntax
    SELECT COUNT(*)          
    FROM Student          
    GO     

Example 

GROUPING ()
The Grouping statement indicates whether a specified column expression in a Group by  list is aggregated or not Grouping returns 1 for aggregated or 0 for not
 
Aggregated in the result set. Grouping can be used only in the SELECT <select> list, HAVING and ORDER BY clauses when group by is specified.
 
Grouping is used to distinguish the null values that are returned by Rollup, cube or Grouping sets from standard null values.
 
The null returned as the result of a Rollup, Cube or grouping sets operation is a special use of null. This acts as a column placeholder in the result set and means all.
 
Syntax
    SELECT GROUPING(StudentName) AS 'Grouping'          
    FROM Student          
    GROUP BY StudentName  WITH ROLLUP;          
    GO   

 Example

GROUPING_ID statement ()
This function computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is
specified.
 
Syntax
    SELECT GROUPING_ID(StudentId, StudentName)          
    FROM Student           
    GROUP BY CUBE(StudentId, StudentName)   

Example

In this article, you learned how to use a SQL Aggregate statement with various options.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will learn how to implement the Audit feature in SQL server. This includes storing all the Audit information in a local table with respect to operations such as creating, altering, and dropping of all tables at the server level.
 
Why is Auditing essential?
Auditing is indeed required to keep track of all the changes to objects that took place within your SQL Server. It has all information such as event type, changed time, server name, login name, database name, object name and actual SQL script used to make the change.
 
How to achieve Auditing in SQL?
To capture DDL activities locally, you need to achieve the below steps,
    Creation of Trigger
    We need to create a trigger for all DDL activities such as Create, Alter and Drop operations at server level.

    Use of Event Data function
    This function helps to capture data associated with a DDL operation in an XML form which will be later used to store in a local table.

    Creation of a local Audit table
    We will be using a local table to store Audit data from the data returned by EventData function.

    Creation of Stored procedure
    A simple stored procedure is required to capture audit data into an XML parameter using EventData function and storing the same data into the local Audit table.

Detailed steps with SQL query to perform Auditing
 
First, we will create a local table named ‘AuditChanges’ to capture Audit changes into it using the below SQL script,
    CREATE TABLE [dbo].[AuditChanges]  
    (  
        [DatabaseName] [nchar](50) NULL,  
        [TableName] [nchar](50) NULL,  
        [EventType] [nchar](50) NULL,  
        [LoginName] [nchar](50) NULL,  
        [SchemaName] [nchar](50) NULL,  
        [SQLCommand] [nchar](50) NULL,  
        [CaptureTime] [nchar](50) NULL  
    )   

Now we have to create a trigger named ‘Audit_Trigger’ on server level which will store the event data as well as storing the information into AuditChanges table. Here [1] refers to the top 1 and datatype varchar means we are storing it into string.
    ALTER Trigger Audit_Trigger  
    ON ALL SERVER  
    FOR Create_Table, Alter_Table, Drop_Table  
    As Begin  
    DECLARE @EventData XML  
    SELECT @EventData = EVENTDATA()  
    INSERT INTO Test.dbo.AuditChanges  
    (DatabaseName, TableName, EventType, LoginName, SchemaName, SQLCommand, CaptureTime)  
    Values   
    (  
       @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/EventType)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(50)'),  
       @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(50)'),  
       GETDATE()  
    )  
    END  

All setup is  done. Now we will be creating a table named 'Student' to verify if event data relating to its creation is getting stored in AuditChanges table or not. Below is the query to create the table student.
    CREATE TABLE [dbo].[Student]  
    (  
        [ID] [int] IDENTITY(1,1) NOT NULL,  
        [Name] [char](50) NULL  
    )  

Now as soon as the table is created, its event data will be stored in AuditChanges. Query the Audit table to get the result. As seen below, the Audit table has successfully captured the Student dbo creation details.

HostForLIFEASP.NET SQL Server 2019 Hosting

You might be asking why on earth would you want to get a database into an undesirable state, more specifically into a Recovery Pending state.  Well, in my case, I had a client database that had entered into this state due to a failure of storage.  Thankfully, this database was not being used for production, so I had some time to determine the best way to fix the issue.

A phrase that was often used during my time in the fire service, was "Try Before You Pry".  Does the front door of the house need to be kicked in?  It may be unlocked and by trying before prying (with my boot) I can prevent damage to the door.  In these types of scenarios, this philosophy holds true.  Try things out on non-critical databases will help prevent any further damage.

In this instance, I want to try it before forcing something that might be damaging.  This meant I had to get a test database into a recovering state.  Once it is in the state I need, then I can attempt different methods to recover the database properly.  Once a successful solution is determined, I can then confidently pry against the damaged database in production knowing that I am using a validated solution.

DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.

You have been warned.

DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.

You have been warned. Again.
How do you get a database into a recovery pending state?

Here is how I did this:

    Start a new transaction.
    Create a new table.
    Stop the SQL Server service
    Rename/Delete the database log file.
    Restart the SQL Server Service

The database will be in a recovery pending state upon the restart of the SQL Server service.
Why is the database in recovery pending?

When the database attempts to come back online, it will be put into a recovery pending state because the log file is not present but there was an open transaction when the service was shut down.  In normal operations, even with an open transaction, SQL Server would go through the recovery phase of the transaction log.  In the rollback phase of recovery, SQL Server would attempt to rollback and transaction that was open at the point of restart and undo the changes.  Since the log file no longer exists, it is unable to do so.

Therefore, the database is now in the recovery pending status.  It’s pending recovery because there was an open transaction, but SQL Server is unable to bring the database into a consistent state.

When this occurs, you will see something like this in the error log:

If the database is shut down cleanly and the transaction log file remove/renamed/etc., SQL Server will just rebuild the log file for you.

Summary

Sometimes it is useful to be able to put a database into a specific state of being so that you can validate solutions before attempting to perform an action in a Production environment.  Just remember to try it before prying it.  Not doing so could just make things worse so being overly cautious is not a bad thing.

HostForLIFEASP.NET SQL Server 2019 Hosting