Application security

Parameterized SQL query over dynamic SQL query

What is a parameterized SQL query?

It is used for better performance, high efficiency and prevention of SQL injection vulnerability. Before going further, let us have a brief introduction to SQL injection.

SQL injection is a very popular and common web application vulnerability caused by improper handling of input data and its usage in database queries. This vulnerability has been part of the top vulnerabilities in the OWASP Top 10 Web Application Vulnerabilities under the "Injection" category for many years. An application vulnerable to SQL injection attack allows an attacker to execute arbitrary SQL queries, leading to database enumeration and data theft. In worst cases, it can even lead to remote code execution and system takeover. Many large organizations have been found to be vulnerable to SQL injection, proving that this is a very common vulnerability and many developers are not very aware of SQL injection prevention techniques.

11 courses, 8+ hours of training

Learn cybersecurity from Ted Harrington, the #1 best-selling author of "Hackable: How to Do Application Security Right."

See Courses

During development of an application, from the eyes of a developer it can be seen that it is prevalent to use user input as part of dynamic SQL queries. Using dynamic SQL queries in an insecure manner is the root cause of SQL injection vulnerability.

In past years, many input validation, data sanitization and input escaping techniques have been developed and implemented to prevent SQL injection. But for one or the other reason, a flaw has been found in them or in the way they have been implemented, making them vulnerable.

The best choice for preventing SQL injection has been found to be "SQL Parameterized Queries". OWASP also recommends it as the first choice of prevention techniques for this vulnerability. SQL injection prevention cheat sheet 

Let us go forward and understand what happens when handling data with database queries in both vulnerable and secure implementations.

SQL Query for fetching username and password for authentication:

SELECT username, password FROM users where username="USERNAME_INPUT" AND password="PASSWORD_INPUT" LIMIT 0, 1;

This SQL query is used to fetch a single row from the users table to authenticate a user. Note that for this SQL query to execute, it requires two fields "Username" and "Password". This input is expected from user only.

Accepting input from user and using it as an input in SQL query makes it a dynamic query. Using dynamic queries with flawed input validation, input sanitization and escaping leads to an application being vulnerable to SQL injection vulnerability.

What is a dynamic SQL query?

Dynamic SQL Query creates an SQL query with the user input all together. A dynamic query directly uses user's input into the query. It may or may not have implemented input escaping before using it in the SQL query.

A normal user authentication query should have been like this in a Dynamic SQL Query:

SELECT username, password FROM users where username="codergeek" AND password="mysecretpass" LIMIT 0, 1;

User input has been highlighted for better understanding.

With this SQL query built and then executed, it would have worked flawlessly. But an authentication query with SQL injection could have been like this:

SELECT username, password FROM users where username="codergeek" AND password="anything" or 1=1 --" LIMIT 0, 1;

This SQL query when executed would authenticate the user because the password field evaluates to TRUE because of user input containing " or 1=1 --

User input is used to build dynamic SQL query, hence it becomes a part of the logic of SQL query, leading to SQL injection vulnerability. Due to this, using dynamic queries is not a good development strategy.

SQL Parameterized Query comes to rescue here because it forces the user to implement the logic of SQL query first and then inserting user input into it. This forces the SQL query to be built before entering any user input in it.

Another advantage of using SQL parameterized query is that it forces the data type of user input for a particular field in SQL query.

For example, assume a SQL query expects a user to enter a number and then SQL is used to fetch a result depending upon that input. SQL parameterized query implementation forces the input data to be of integer type, and only then further processing will be done. Otherwise it will show an error or throw an exception (depending upon the server side language in use and how error and exception handling is done).

Taking the above example into consideration again, a SQL parameterized query would be implemented like this in Java:

SELECT username, password FROM users where username=? AND password=? LIMIT 0, 1;

ps.setString(1,user_var);

ps.setString(2,pass_var);

ps.executeQuery();

Note that this is not the complete way of implementing parameterized query in Java. It is just a small snippet of the actual code.

In the above example, SQL logic was defined as soon as the SQL query was created; afterwards it cannot be modified. The only part left in the query is user input for fields and it is safely defined afterwards. The data type of the field and its position in the query is defined.

This technique separates the SQL logic from the user input and also defines the type of data expected in the fields.

Another example showing data type security in a SQL parameterized query:

SELECT name, address, email FROM user_details where userid=? LIMIT 0, 1;

ps.setInt(1,userid_var);

ps.executeQuery();

The highlighted code in above implementation forces the query to accept userid as an integer (number) only. This rules out the possibility of a SQL query getting executed with user input containing a SQL injection string such as -1" or 1=1 --

The main difference between a Dynamic Query and a SQL Parameterized Query is that in the former, the SQL logic is built along with the user input. But in the later, SQL logic is defined first and locked, then user input is passed as parameters along with its data type defined.

Another development strategy to be kept in mind is that SQL injection vulnerability not only exists while fetching data but also when executing any database query. SQL parameterized query should be implemented in in all SELECT, INSERT, UPDATE, DELETE queries.

Below is shown the SQL parameterized query implementation in Java:

SELECT query:

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<%

Class.forName("com.mysql.jdbc.Driver");

Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root" ,"");

try

{

String user = request.getParameter("user");

String pass = request.getParameter("pass");

PreparedStatement ps = (PreparedStatement) con.prepareStatement("SELECT username, password FROM users WHERE username=? AND password=? limit 0,1");

    ps.setString(1,user);

    ps.setString(2,pass);

    ResultSet rs=ps.executeQuery();

if(rs.next())

{

request.getSession(true);

session.setAttribute("username", rs.getString("username"));

session.setAttribute("loggedin","1");

response.sendRedirect("account.jsp");

}

else

{

response.sendRedirect("index.jsp?error=1");

}

}

catch(Exception e)

{

System.out.println(e.getMessage());

}

%>

INSERT query:

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<%

Class.forName("com.mysql.jdbc.Driver");

Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root" ,"");

try

{

String user = request.getParameter("user");

String pass = request.getParameter("pass");

String email = request.getParameter("email");

PreparedStatement ps = (PreparedStatement) con.prepareStatement("INSERT INTO users VALUES(?,?,?)");

    ps.setString(1,user);

    ps.setString(2,pass);

    ps.setString(3,email);

    int rs=ps.executeUpdate();

if(rs > 0)

{

out.println("Account registered");

}

else

{

out.println("Account registration failed");

}

}

catch(Exception e)

{

System.out.println(e.getMessage());

}

%>

UPDATE query:

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<%

Class.forName("com.mysql.jdbc.Driver");

Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root" ,"");

try

{

String user = request.getParameter("user");

String email = request.getParameter("email");

PreparedStatement ps = (PreparedStatement) con.prepareStatement("UPDATE users SET email=? WHERE user=?");

    ps.setString(1,email);

    ps.setString(2,user);

    int rs=ps.executeUpdate();

if(rs > 0)

{

out.println("Account updated");

}

else

{

out.println("Account update failed");

}

}

catch(Exception e)

{

System.out.println(e.getMessage());

}

%>

DELETE query:

<%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<%

Class.forName("com.mysql.jdbc.Driver");

Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root" ,"");

try

{

String user = request.getParameter("user");

PreparedStatement ps = (PreparedStatement) con.prepareStatement("DELETE FROM users WHERE user=?");

    ps.setString(1,user);

    int rs=ps.executeUpdate();

if(rs > 0)

{

out.println("Account deleted");

}

else

{

out.println("Account deletion failed");

}

}

catch(Exception e)

{

System.out.println(e.getMessage());

}

%>

Security Note: The above code should not be used as it is for production use. This code is for demonstration of Parameterized SQL Query only and requires further security implementations to be used in production environment.

Dynamic Query for SELECT query:

%@page import="java.sql.*"%>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<%

Class.forName("com.mysql.jdbc.Driver");

Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root" ,"");

try

{

String user = request.getParameter("user");

String pass = request.getParameter("pass");

Statement stmt = (Statement) con.createStatement("SELECT username, password FROM users WHERE username='" + user + "' AND password='" + pass + "' limit 0,1");

    ResultSet rs = stmt.executeQuery();

if(rs.next())

{

request.getSession(true);

session.setAttribute("username", rs.getString("username"));

session.setAttribute("loggedin","1");

response.sendRedirect("account.jsp");

}

else

{

response.sendRedirect("index.jsp?error=1");

}

}

catch(Exception e)

{

System.out.println(e.getMessage());

}

%>

As shown in the example above for Dynamic Query, user input is part of the SQL query. And it completes the SQL logic there only with the user input. This feature of dynamic query makes it vulnerable to attacks like SQL injection. Using dynamic queries with user input allows user to tamper with the SQL logic. The only solution to this problem is to validate and sanitize the data before using it as an input in SQL queries. But it creates an overhead of work in application development and one cannot be 100% sure if it is free of flaws and cannot be bypassed.

On the other hand, SQL Parameterized Query separates the SQL query from the user input. It allows the developer to define the SQL logic first and then bind parameters which specify the user input to it. This allows a developer to secure the SQL logic and denies user input to tamper with the SQL logic.

Another advantage of using SQL parameterized query is that you do not have to define the SQL query again and again for further queries (like you would do with dynamic queries). To execute the query for multiple data, one just has to bind the parameters again to execute it.

For example:

int rs = 0;

PreparedStatement ps = (PreparedStatement) con.prepareStatement("INSERT INTO users VALUES(?,?,?)");

ps.setString(1, "user1");

ps.setString(2, "mypass1");

ps.setString(3, "user1@gmail.com");

rs = ps.executeUpdate();

ps.setString(1, "user2");

ps.setString(2, "mypass2");

ps.setString(3, "user2@gmail.com");

rs = ps.executeUpdate();

The above technique makes the application more robust and increases its efficiency. This proves the discussion we had in the beginning which said that parameterized queries increase efficiency and performance.

Conclusion

To conclude this topic we can summarize Parameterized SQL Queries vs Dynamic SQL Queries as below:

Parameterized SQL Queries' key features:

• Protection from SQL injection
• Improved efficiency
• Higher reliability
• Higher performance
• Modular implementation

11 courses, 8+ hours of training

Learn cybersecurity from Ted Harrington, the #1 best-selling author of "Hackable: How to Do Application Security Right."

See Courses

Parameterized SQL Query has proven to be the best prevention technique for SQL injection and has shown how we can create more efficient, reliable and secure applications.