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
5. DECLARE @SQL varchar(200);
7. SET @SQL=‘select PROFILE from EMPLOYEE where ‘;
9. IF LEN(@Name) > 0 AND LEN(@Passwd) > 0
12. SELECT @SQL=@SQL+‘NAME=“‘+@Name+“‘ and ‘;
13. SELECT @SQL=@SQL+‘PASSWD=“‘+@Passwd+““;
19. SELECT @SQL=@SQL+‘NAME=“Guest“‘;
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.