In this article, we are going to install Microsoft SQL Server on windows machines step by step. In this tutorial, we are going to use SQL Server 2019 Developer edition and Windows Server 2019.

Pre-requisites
    Windows machine having 2 core and 4 GB RAM configuration

Download SQL Server 2019 Developer Edition
First, let’s download SQL Server installation media from the official website.

Step 1
Download installation media from this link.

Step 2
Run the downloaded file and you will see the below screen. Now select the third option – Download Media.

Step 3
Now you will see the below screen. Please select the language you prefer and select the ISO radio button to download the ISO file. In addition, select the download location of your choice. I will go with the default location. Now press the Download button.

Step 4
Now it will start downloading SQL Server installation media. It will take some time based on your internet connection speed.

Step 5
After successful download of installation media, you will see the below screen. Click the Close button.

Install SQL Server 2019 Developer Edition
Now that we have installation media, we can start the installation of the SQL Server. Let’s see how to install SQL Server step by step.

Step 1
Run install media file (ISO file) downloaded in above section by double-clicking on it. It will extract/mount all the contents in a new temporary drive.

Step 2
Once extraction is completed, double click on the setup.exe file and you will see the below screen. Click on the Installation option in the left panel and then click on New SQL Server stand-alone installation or add features to an existing installation option from the right panel.

Step 3
Now you will see the Product Key window. Select the Developer option from the dropdown and click on the Next button.

Step 4
Now you will see the License Terms window. Just select the checkbox and click on the Next button.

Step 5
Now you will see the Microsoft Update window. It is not compulsory to check for the latest updates but it is recommended. So, select the checkbox and click the Next button.

Step 6
Now it will check for updates and install them if any.

Step 7
After that, it will check some rules or prerequisites for the installation of SQL Server. Once all the rules passed, click on the Next button. Sometimes you may face an error at this stage. You can find some known errors at the end of this article.

Step 8
On the Feature Selection window, select features as shown in the below screenshot. You can also change the location for SQL Server instance installation but I will go with the default location. After feature selection please click the Next button.

        Image 1 – Features selection Image 2 - Features selection

Step 9
It will check some feature rules/prerequisites and then you will see the Instance Configuration screen. Here you can choose between Default Instance and Named Instance. Here I will go with Named Instance.

Note
Default Instance
When SQL Server is installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name. For example, HARDIK-PC.

Named Instance
A named instance is identified by the network name of the computer plus the instance you specify during the installation. The client must specify both the server name and the instance name when connecting. For example, HARDIK-PC/MSSQLSERVER.

Step 10
Next, you will see the Server Configuration window. In Service Accounts tab, select Automatic in Startup Type for SQL Server Agent, SQL Server Database Engine, and SQL Server Browser services.

In the Collation tab, select collation as per your preference.
“Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.” – Microsoft.

Step 11
Next, you will see the Database Engine Configuration window. In the Server Configuration tab, choose Mixed Mode in the authentication mode section and enter a strong password. In Specify SQL Server administrators section, your current windows user should already be added automatically. If not, click on Add Current User button.

In the Data Directories tab, specify locations for database files and backup files. By default, it saves all the files on a C drive but it is not recommended to store database files on an OS drive because if any OS-related issue occurs then we may lose our data. Therefore, I choose D drive on my local machine.

In the TempDB tab, there are configurations for the temporary database file(s). There are some best practices on how to configure temporary database files locations, the number of files, and their file sizes. Ideally, the number of the TempDB data files should match the number of logical processors. So I have a number of files to 2. If you are interested in deep dive into TempDB best practices, here is a very good article on it.

Next, in the MaxDOP tab, the maximum degree of parallelism (MAXDOP) is a server configuration option for running SQL Server on multiple CPUs. It controls the number of processors used to run a single statement in parallel plan execution. By default, the setup will suggest value based on the system configuration.

Next, in the Memory tab, we can configure how much memory SQL Server instance can consume. By default, the installation process will recommend you min and max memory allocation based on the system configuration on which it is going install. However, you can change it. Here you can find best practices for SQL Server memory configurations.

In the FILESTREAM tab, leave the checkbox unchecked because we are not going to enable this feature. FILESTREAM, in SQL Server, allows storing these large documents, images, or files onto the file system itself.

Click on the Next button.

Step 12
Next, the setup will check some feature configuration rules, and then the Ready to Install window will appear. This window shows the summary of all the features and configurations which we have done in the above steps. Once review the summary and click on the Install button.

Step 13
Now, the installation will start and it may take some time based on our configurations.

Step 14
After installation, it will show you the list of features and their installation status. If any error occurred, it will show here.

Congratulations! We have successfully installed SQL Server 2019 Developer edition on Windows machines. Next, you can install SQL Server Management Studio to connect SQL Server and query SQL databases. Please check my blog to – Step by step installation of SQL Server Management Studio.

HostForLIFEASP.NET SQL Server 2019 Hosting

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance. There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint.
 
ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.
 
Let’s take a look at the three options.
 
Trace Flag4199
In dealing with any trace flag, it is vital that you understand as to what the trace flag will enable or disable and how it pertains to your individual workloads. Some trace flags are benign, such as TF3226 which removes successful backup messages from the log, others can be quite intrusive and cause havoc. Make sure you review any documentation on the respective trace flag prior to implementation.
 
The official verbiage from Microsoft on trace flag 4199 is,
 
“Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs.
 
QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 being enabled.”
 
What this really implies is that if your database is at the most current compatibility level for your version of SQL server, then the query optimizer will utilize all of the QO hotfixes up to the release to market (RTM) version but nothing beyond the RTM version.
 
For example, if you are running SQL Server 2017 CU20 the database is set at the appropriate compatibility level (140), and TF4199 is disabled, the query optimizer will only utilize any hotfixes for it up to 2017 RTM. Any query optimizer hotfix that was delivered in subsequent cumulative updates will not be utilized.
 
If you enable trace flag 4199, this instructs SQL Server to implement any query optimizer hotfix that was delivered after the RTM version. Note that enabling this trace flag will be applied across all databases. It’s an all or nothing trace flag as most trace flags are.

Sometimes while handling large databases, we get stuck between Truncate and Delete statements. Each one has its pros and cons and it's a really crucial decision to decide which one to use. Before we go ahead and understand the answer to this question, let's first understand the differences between these two statements.

Delete
Syntax

Delete from MST_Team;

1. Delete is a DML command unlike truncate. Truncate is DDL command.
2. Delete statement deletes records row by row and maintains a log of each deleted row in the transaction log.
3. A row lock is applied on the table for each row getting deleted.
4. It can also be used with where clause to delete records based on matching conditions.
5. Identity retains its value even after deletion of records.
6. Delete can be used with indexed views.
7. Delete can also be used with foreign key references, records that are not interlinked with foreign key references will get deleted.
8. Delete uses more transactional space than a truncate statement.

Truncate
Syntax
Truncate table MST_Team;

1. Truncate is a DDL command.
2. Records in the whole table deleted at the same time. A table lock is applied while executing of the truncate statement.
3. No transactional log is maintained while deleting records with Truncate statement however page deallocation log is maintained.
4. The Identity of the column is set to its seed value.
5. Truncate can not be executed with foreign key references also it can not be used with indexed views.
6. No transactional log is maintained so Truncate is faster than Delete and also it uses less transactional space.
7. No where clause can be used, the data of the whole table gets deleted together. We can delete data partially with partitions.

From the above explanation of delete and truncate, we can easily conclude on the below scenarios.

Delete all records from a table: Which statement to use? Truncate/Delete
To delete all records from the table, Truncate is the best option, however, it also has its own limitations as mentioned below.

On the Truncate table the Identity column is set to it's seed value. If you don't want this to happen, then we can not use Truncate even if you want to delete all records.
No transaction log is maintained. If you want to maintain transactional log then we can not use Truncate.
To Truncate tables we need minimum ALTER Table rights on the table. Delete rights aren't sufficient to Truncate the table. Sometimes we may not want to give ALTER rights because of security reasons.
Even if a single record has a foreign key, the Truncate statement won't get executed.

We can use delete as well but it also has its own limitations, as mentioned below:

The Delete statement as mentioned above uses more transactional space.
The Delete statement is slow as it maintains all logs.

Delete a few records from the table: Which statement to use? Truncate/Delete
The answer to this question is very straight forward. It's the "DELETE" statement only, but hold on, there is some trick to delete partial records with Truncate.

We can delete partial data from the table with a Truncate statement using partitions. If logical partitions are applied on a table and if you want to delete a few partitions amongst all of them, then we can do the same.

We can import the .NET DLL or group of DLLs into SQL Server. Once the DLL is imported then, we can access the methods in the DLL inside the Stored Procedure, User-Defined function, or executed via TSQL. It is called CLR Assembly. In this article, we are going to explore how to create a custom CLR assembly and how to access it in the SQL Server.

CLR Assembly
An assembly is DLL files that are written by one of the managed code languages hosted by the Microsoft.NET Framework common language runtime (CLR) and it can be used in the stored procedures, triggers, user-defined functions, etc.
An assembly is a good option to expand the native functionality of the SQL server.

How to Create CLR Assembly
Create the Class Library using C#
Create Assembly from File
Create User-Defined Function for Access the Assembly
Invoke User-Defined Function

Create the Class Library using C#
Consider the below "DateConvertion" class which has the "DateConvert" function. It is used to convert date strings to the required format.
Both class and function are static and public.
I have used .NET Framework 4.5 and SQL server 2012. Because each Microsoft SQL Server is statically linked to a particular version of the CLR (not to a particular version of the .NET Framework).
SQL Server 2005, 2008, and 2008 R2 are linked to CLR version 2.0 which handles .NET Framework versions 2.0, 3.0, and 3.5, while SQL Server 2012 and 2014 are linked to CLR version 4.0 which handles .NET Framework versions 4.0, 4.5.x, 4.6.x, etc.
using System;
using System.Globalization;

namespace CLRDateConvertAssembly
{
    public static class DateConvertion
    {
        public static string DateConvert(string date, string format)
        {
            try
            {
                CultureInfo provider = new CultureInfo("en-US");

                string[] inputFormats = {
                "MM/dd/yyyy", "M/d/yyyy", "M/dd/yyyy","dd/MM/yyyy",
                "MM/d/yyyy", "M/d/yyyy h:mm:ss tt", "MM/d/yyyy h:mm:ss tt",
                "M/d/yyyy h:mm tt", "MM/dd/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
                "M/d/yyyy hh:mm tt", "M/d/yyyy hh tt", "M/d/yyyy h:mm", "M/d/yyyy h:mm",
                "MM/dd/yyyy hh:mm", "M/dd/yyyy hh:mm",
                "MM-dd-yyyy", "M-d-yyyy", "M-dd-yyyy","dd-MM-yyyy",
                "MM-d-yyyy", "M-d-yyyy h:mm:ss tt", "MM-d-yyyy h:mm:ss tt",
                "M-d-yyyy h:mm tt", "MM-dd-yyyy hh:mm:ss", "M-d-yyyy h:mm:ss",
                "M-d-yyyy hh:mm tt", "M-d-yyyy hh tt", "M-d-yyyy h:mm", "M-d-yyyy h:mm",
                "MM-dd-yyyy hh:mm", "M-dd-yyyy hh:mm"};

                DateTime dt = DateTime.ParseExact(date, inputFormats, provider, DateTimeStyles.None);

                return dt.ToString(format);
            }
            catch
            {
                return string.Empty;
            }
        }
    }
}

Sign the project using an asymmetric key.
Right, Click the Project -> Properties.
Go to the "Signing" Tab.
Check the "Sign the assembly" check box.
Select the "New" option in the "strong name key file" drop-down.

Enter the key file name and uncheck the password option and click "OK".

Build the project and copy the "CLRDateConvertAssembly.dll" file into the required folder.

Create Assembly from File
To create an assembly, we need to follow the below steps. Execute steps 1,2,3 queries are in the "master" database and step 4 and 5 in our database.

Step 1
Create Asymmetric Key from Assembly File.

USE master;
GO
CREATE ASYMMETRIC KEY CLRDateConvertionKey FROM EXECUTABLE FILE = 'D:\GC\Blog\Content\Assembly\DLL\CLRDateConvertAssembly.dll';
GO

Step 2
Create SQL Server Login linked to the Asymmetric Key.
USE master;
GO
CREATE LOGIN CLRDateConvertionKeyLogin FROM ASYMMETRIC KEY CLRDateConvertionKey;
GO

Step 3
Grant UNSAFE assembly permission to the login created.
USE master;
GO
GRANT UNSAFE ASSEMBLY TO CLRDateConvertionKeyLogin;
GO

Step 4
Create a SQL Server database user for the SQL Server login created.
USE TestDB;
GO
CREATE USER CLRDateConvertionKeyLogin FOR LOGIN CLRDateConvertionKeyLogin;
GO

Step 5
Create CLR Assembly.
USE TestDB;
GO
CREATE ASSEMBLY DateConvertAssembly FROM 'D:\GC\Blog\Content\Assembly\DLL\CLRDateConvertAssembly.dll' WITH PERMISSION_SET = SAFE;
GO

When creating an assembly in the MS SQLServer database, you can specify one of three different levels of security.
SAFE
EXTERNAL_ACCESS
UNSAFE

SAFE is the default permission set and works for the majority of scenarios.

EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables.

UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.

Any one of the below conditions must be met for creating an EXTERNAL_ACCESS or UNSAFE assembly.

The assembly is a strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.

We have created the assembly with SAFE permission. So no need to disable the CLR Strict Security feature and enable the Database Trustworthy feature in the MS SQL Server.

Create User-Defined Function for Access the Assembly
Create the user-defined function for consuming the above assembly. I have created below "dbo.DateConvert" function.
CREATE FUNCTION dbo.DateConvert (@date NVARCHAR(100), @format NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME [DateConvertAssembly].[CLRDateConvertAssembly.DateConvertion].[DateConvert];
GO

Invoke User-Defined Function
Call the above user-defined function to consume the assembly for date conversion.
SELECT dbo.DateConvert ('10/01/2020', 'dd-MMM-yyyy') as [Date];

If you want to drop an assembly, run the below query.
Use TestDB;
GO
Drop ASSEMBLY DateConvertAssembly;
GO

I hope you have liked this article and know about CLR assembly in MS SQL Server.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, you will see the Complete Reference - Table Scan, Index Scan, and Index Seek in MSSQL.

Background
While working with MS SQL database and SQL, a good knowledge of how the index works and how to use them to improve the SQL query performance is very important.

Prerequisites
Preliminary understanding is required about what the index is and how to create and drop an index.

Difference between Scan and Seek Operation

Table and Index Access
Now, let us understand each one, one by one, with practical examples.

Table Scan

• It is a very simple process. While performing table scan, the query engine starts from the physical beginning of the table and it goes through every row into the table. If a row matches with the criteria then it includes that into the result set.
• It is the fastest way to retrieve the data especially when there is quite a small table.
• For a small table, a query engine can load all the data in a one-shot but from a large table it is not possible i.e. more IO and more time will be required to process those large data.
• Generally, a full table scan is used when a query doesn’t have a WHERE clause i.e. all data.

For example, an Employee table with no index and the following query will use the Table scan.
SELECT * FROM Employee

Index Scan

• When you have a clustered index and your query needs all or most of the records (i.e. query without where or having clause) then it uses an index scan.
• Index scan works similar to the table scan during the query optimization process. The query optimizer takes look at the available index and chooses one of the best, based on JOINs and WHERE clauses.
• As the right index is being chosen, the SQL query processing engine will navigate the tree structure to the pointer of the data which matches the criteria and further extracts only the needed/required records.
• The key difference between Table Scan and Index Scan is that data is stored in the index tree, the query processor knows it when reaches the end of the current it is looking for. Then it can send the query or move on to the next range of data.
• An index scan is slightly faster than the Table scan but significantly slower than an Index.

For example, Employee table with clustered index and the following query will use the Index scan,

SELECT * FROM Employee

Index Seek

• When the search criterion matches the index well enough which can navigate directly to particular points into the data, this is known as the Index seek.
• The index seeks the fastest way to retrieve the data in the database.
• For example, the following query will use the Index seek which can be confirmed by checking the execution plan of the query
• The query optimizer can use an index directly to go to the 3rd employee id and fetch the data.

Query execution plan can show the same as it uses an index seek through created EmployeeId index,

SELECT name FROM Employee WHERE id=5

Difference between Table Scan, Index Scan and Index Seek

Now, I hope you understand the key important things about Table Scan, Index Scan, and Index Seek in MSSQL.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will see 'how to create a SQL Server Database backup'. Database backup using SQL Server Management Studio, SQL Server provides a large number of options to create backups. We will discuss here FULL BACKUP and DIFFERENTIAL BACKUP.
 
FULL BACKUP
The most common type of backup is the full backup. In this type of backup, it creates a full backup of your database and also includes the transaction logs into the backup.
 
We can create it by using SSMS (SQL Server Management Studio).
 
Let's see how.
 
Full Backup using T-SQL (Transact-SQL)
This will create a full (Complete) backup into the .bak file of the given database,
BACKUP DATABASE DotNet TO DISK='D:\DotNet\Backup\DotNet.BAK'
 
Full Backup using SSMS
Open your SSMS and select the desired database and right-click on the database name click on Tasks > Back Up,

Select Full as Backup Type, select Disk as the destination, and then click on Add button to add the directory where the backup will be stored on your disk.

Select the destination for the backup,

Click on ok and again ok on the next screen and the backup progress will be started.

This process will take some time that depends upon your database size.
 
DIFFERENTIAL BACKUP
Another option to create a backup is Differential backup. Differential Backup is the backup of the changes that have been made after a recent full/ complete database backup.
 
Let's see how.
 
Differential Backup using T-SQL (Transact-SQL)
This will create a differential backup with .DIF file extension of the given database,
 
BACKUP DATABASE DotNetKida TO DISK='D:\DotNet\Backup\DotNet.DIF' WITH DIFFERENTIAL
 
Differential Backup using SSMS
Open your SSMS and select the desired database and right-click on the database name click on Tasks > Back Up,

Select Differential as Backup Type, select Disk as the destination, and then click on Add button to add the directory where the backup will be stored on your disk.

Select the destination for the backup,

Click on ok and again ok on the next screen and the backup progress will be started.

A binary value or type in SQL Server is a series of bytes (known as a byte array in some programming languages). Just like char/varchar, there are fixed-length types, binary(1-8000), and variable-length ones, varbinary(1-8000) and varbinary(max).
Sometimes we store this type of data in SQL Tables and lose the source files. This type of data is stored in a binary format which is system formatted.
 
A PDF file is converted and stored in this format. And the text looks similar to this,
 "0x255044462D312E340A25C3A4C3BCC3B6C39F0A322030206F626A0A3C3C2F4C656E67746820332030
20522F46696C7465722F466C6174654465636F64653E3E0A73747265616D0A789C358CBD1240401083FB
7D8AD48A75B71CABD728551EC0F8291C43E3F5ED0D26453"
 
Now to recreate a file from this data is not something that can be created by simple copy-paste.
 
Here is the process which can be used to get the source file.
DECLARE @outPutPath varchar(50) = 'C:\ExtractedFiles'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max)
, @folderPath  varchar(max)

--Get Data into temp Table variable so that we can iterate over it
DECLARE @Doctable TABLE (id int identity(1,1), [FileName]  varchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable( [FileName],[Doc_Content])
Select [RecordID],[FileUpload] FROM  [dbo].[tbl_AuthorConferenceList]

--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable

WHILE @i >= 1
BEGIN

       SELECT
        @data = [Doc_Content],
        @fPath = @outPutPath +  '\' +[FileName] +'.pdf',
        @folderPath = @outPutPath
       FROM @Doctable WHERE id = @i

  --Create folder first

  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance created
  EXEC sp_OASetProperty @init, 'Type', 1;
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources

  print 'Document Generated at - '+  @fPath

--Reset the variables for next use
SELECT @data = NULL
, @init = NULL
, @fPath = NULL
, @folderPath = NULL
SET @i -= 1
END

Now you might end up getting compilation issues.
 
The reason is all the System Stored Procedures are unavailable.
To solve this we have to enable the Facets Property "OleAutomatedEnabled"

HostForLIFEASP.NET SQL Server 2019 Hosting

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