Prevent sql injection with dynamic sql

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All SQL Server Tutorial Videos

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

In this video we will discuss, how to prevent SQL injection when using dynamic SQL.

In Part 140, we have implemented "Search Page" using dynamic SQL. Since we have used parameters to build our dynamic SQL statements, it is not prone to SQL Injection attack. This is an example of good dynamic SQL implementation.

I have seen lot of software developers, not just the beginners but even experienced developers, buidling their dynamic sql queries by concatenating strings instead of using parameters without realizing that they are opening the doors for SQL Injection.

Please check my blog at the following link for code used in the demo.

Since we are concatenating the user input values to build the dynamic sql statement, the end user can very easily inject sql. Imagine, what happens for example, if the user enters the following in the "Firstname" textbox.

' Drop database SalesDB --

With the above SQL injected into the "Firstname" textbox, if you click the "Search" button, the following is the query which is sent to SQL server. This will drop the SalesDB.

Select * from Employees where 1 = 1 AND FirstName = '' Drop database SalesDB --'

On the other hand, if you use parameters to build your dynamic SQL statements, SQL Injection is not an issue. The following input in the "Firstname" textbox, would not drop the SalesDB database.

' Drop database SalesDB --

The text the user has provided in the "Firstname" textbox is treated as the value for @Firstname parameter. The following is the query that is generated and executed.

We don't have this problem of sql injection if we are using stored procedures. "SearchPageWithoutDynamicSQL.aspx" is using the stored procedure "spSearchEmployees" instead of dynamic SQL. The same input in the "Firstname" texbox on this page, would generate the following. Notice, whatever text we typed in the "Firstname" textbox is treated as the value for @FirstName parameter.

exec spSearchEmployees @FirstName=N''' Drop database SalesDB --'

An important point to keep in mind here is that if you have dynamic SQL in your stored procedure, and you are concatenating strings in that stored procedure to build your dynamic sql statements instead of using parameters, it is still prone to SQL injection. If this is not clear at the moment don't worry, we will discuss an example of this in out next video.

So in summary, while dynamic sql provides great flexibility when implementing complicated logic with lot of permutations and combinations, if not properly implemented it may open doors for sql injection. Always use parameters to build dynamic sql statements, instead of concatenating user input values.

Another benefit of using parameters to build dynamic sql statements is that it allows cached query plans to be reused, which greatly increases the performance. We will discuss an example of this in our upcoming videos.

[ashishvishwakarma]

Dear Venkat
I m huge fan of yours.
U r sharing ur lot of precious knowlenge with us.
U r really great sir.

I think instead of joining other classes if we watch ur video and regualry practice of that
Than we can be Excellent SOFTWARE DEVELOPER.

Dear Venkat Sir,
The way you teach,
The knowledge you share,
The care & love you shower,
Makes you
The world's best teacher.

Thank

[raqibul]

The First job of mine after wake up is to check Ur tutorials.Thanks a Trillions.

[ihsanullah]

Thank you sir for sharing knowledge and very useful information and technology updates.
Thanks again sir for your giving your precious time to record and upload the video
We are waiting for your everyday video and checking the networks many time a day.Regards

[aliasadhassan]

very nice ...i really appreciate your work venkat ..infact you are a perfect teacher..

[IbrahimEl-khalilMAdams]

My plan to prevent SI is to use a dropdownlists control instead of text boxes and setting the datasource of each dropdownlist to the corresponding column. Enabling outopostback for the DDL will further simplify inserting search parameters.

[saurabhchauhan]

Thanks Sir. Can we have series on Regex or Some more real time MVC example with Jquery ?

[byamakesh]

Hi venkat, please record a video on "sql service broker", we need it very much. Thanks 😀

[battulasuresh]

At 8:38 time
Why one extra single quote character is there?
2nd single quote

[raqibul]

Plz dont forget to hit the like button.

[ymtan]

I would like to ask regarding building dynamic SQL statements by concatenating user input values. What if I just type Drop Database SalesDB into the "Firstname" textbox without the single quote at the beginning and the comment character at the end, will the database SalesDB get dropped ???

[harshitvishwakarma]

Does dynamic sql work if table name pass as parameter ? Without concatenation ?

[dushyanthkandiah]

Instead we can use string.replace("'", "''");
it make one apostrophe to two

[mdelias]

very helpful video, I need data insert via store procedure in asp.net, please give me link if you have

[raviprasad]

Every tutorial explains how to avoid SQL injection but none of them explains how the hacker came to know the table or dB name to destroy them??!!

[shahidwani]

Please Venkat sir create video on "Create SEO friendly URLs in .net MVC.

[jun_]

How can we prevent this by assigning roles in SQL Server? SA account can delete a database or table but a web user should not be able to. Pls comment on this. Thanks

[MINHAJ_MJ]

why not use LINQ, and there is no problem