Stored procedures are also vulnerable for SQL injection attacks

Stored procedures are also vulnerable for SQL injection attacks

Stored procedures are also vulnerable for SQL injection attacks

You know inline SQL coding is a great security risk that’s why advanced web developers prefer SQL stored procedures but think again before using it.. Is it secure ?

Notice that is the code written bellow, there is an EXEC system function which allow the user to  dynamically build a SQL statement in string format and later execute  it. This feature is supported in most other business database products  also. Dynamically built SQL statements provide great user flexibility  but also face a great threat from SQLIAs. The process of building an SQL  statement could be used by the attacker to change the original intended  semantics of the SQL statement.

1. CREATE PROCEDURE [EMP].[RetrieveProfile] @Name varchar(50),@Passwd varchar(50)

2. WITH EXECUTE AS CALLER

3. AS

4. BEGIN

5. DECLARE @SQL varchar(200);

6. …

7. SET @SQL=‘select PROFILE from EMPLOYEE where ‘;

8. …

9. IF LEN(@Name) > 0 AND LEN(@Passwd) > 0

10. BEGIN

11. …

12. SELECT @SQL=@SQL+‘NAME=“‘+@Name+“‘ and ‘;

13. SELECT @SQL=@SQL+‘PASSWD=“‘+@Passwd+““;

14. …

15. END

16. ELSE

17. BEGIN

18. …

19. SELECT @SQL=@SQL+‘NAME=“Guest“‘;

20. …

21. END

22. …

23. EXEC(@SQL)

24. …

25. END

If the stored procedure in Code is called with no values for @Name and @Passwd variables, the following query

would get executed:

select PROFILE from EMPLOYEE where NAME=‘Guest‘

When user inputs are provided for @Name and @Passwd, the following query would get executed:

select PROFILE from EMPLOYEE where NAME=‘name‘ and PASSWD=‘passwd‘

In this scenario, suppose a user gives input for variable @Name as ”‘ OR 1=1 −−” and any string, say ”null”, for

the variable @Passwd the query would take the form:

select PROFILE from EMPLOYEE where NAME=“ or 1=1 −−‘ and PASS=‘null‘

The characters ”−−” mark the beginning of a comment in SQL, and everything after that is ignored. The query as interpreted by the database is a tautology and hence will always be satisfied, and the database would return information about all users. Thus an attacker can bypass all authentication modules in place and gain unrestricted access to critical data on the web server.

abhijeet on Facebookabhijeet on Linkedinabhijeet on Twitter
abhijeet
abhijeet
Abhijeet specializes in developing software. A full-stack developer and Entrepreneur, he takes an idea and crafts it into a beautiful product - front to back. He develops on the LAMP Stack (PHP, MVC, Web API, Perl, Python, Azure, AWS, Google Cloud) and utilizes AngularJS and Angular Material for a structured client. Abhijeet is a self-starter with experience working in remote, agile environments mainly focusing on the security constraints. This is the developer, Project Manager and Consultant you are looking for.