How to Handle SQL Injection in JDBC using PreparedStatement?

In this article, we can learn how to prevent SQL injection in JDBC by using Java programming. Basically, SQL injection is a type of security vulnerability It occurs when hackers or attackers can change or manipulate the SQL query, and this can lead to unauthorized access to resources and also perform different malicious activities once get access by using SQL injection.

To avoid this situation, we have one solution in JDBC with Java which is PreparedStatement.

Handle SQL injection using PreparedStatement

Now, we will explain how to prevent SQL injection in JDBC by using PreparedStatement.

This PreparedStatement can be able to allow us to write SQL statements in the form of placeholders, The placeholders always represented by using the symbol “?”. For a better understanding of the concept, we will explain this with one example.

PreparedStatement syntax:

PreparedStatement preparedStatement = connection.prepareStatement(sql_query)

Note: While working with JDBC and MySQL you need to add mysql-connector jar file to your project.

Example to Handle SQL injection in JDBC Using PreparedStatement

In this example, we insert some rows in MySQL Database. For this first you need to create one database after that create one table in that database, for that we have created one database called books. In this Database, we create one table That is book, Now, we have provided the images for Table schema.

The above picture is help us to describe the table.

• In this Table, there is four fields namely id, author, name, price.
• Here the id is the primary key which auto increment also After that author field indicates name of the author.
• The name field indicates name of book, The final field is price is indicating price of the book.

In the below Java code, we have taken input from user after that we have inserted that data into Table.

Java

// Java Program to Handle SQL injection in
// JDBC Using PreparedStatement
package geeksforgeeks;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Random;
import java.util.Scanner;

// Driver Class
public class PreparedStatementExample {

    public static int generateId() {
        Random random = new Random();
        int bookId = 1000 + random.nextInt(9000);
        return bookId;
    }

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password");
            Scanner input = new Scanner(System.in);
            if (con != null) {

                System.out.println("Enter Author Name");
                String authorName = input.nextLine();

                System.out.println("Enter Book Name");
                String bookName = input.nextLine();

                System.out.println("Enter Book Price");
                String bookPrice = input.nextLine();

                String insertQuery = "INSERT INTO book VALUES (?,?,?,?)";
                PreparedStatement statement = con.prepareStatement(insertQuery);
                statement.setInt(1, generateId());
                statement.setString(2, authorName);
                statement.setString(3, bookName);
                statement.setString(4, bookPrice);

                int rowsAffected = statement.executeUpdate();

                if (rowsAffected > 0) {
                    System.out.println("Record Inserted !...");
                } else {
                    System.out.println("Record not Inserted !...");
                }

                // Close PreparedStatement and Connection
                statement.close();
                con.close();
            } else {
                System.out.println("Not Connected...");
            }

        } catch (Exception e) {
            System.out.println("Exception is " + e.getMessage());
        }
    }
}

Output:

• After running this program, it will ask user inputs in the Java console.
• Like above picture once we provide all details the row is inserted into table you can observe the success message on the console.

Now we have provided picture for data in the table. Initially we have one row in database after we have inserted one more row. After this the output will look like below:

Explanation of the above Program:

• In the above code, we have created one generateId() for set id for each row while inserting.
• After that we have created required connection by using Connection class in Java.
• Then we provide some values for connection like Database name, username, password and others.
• After that we have taken inputs from user for inserting data into table by using PreparedStatement.
• After that we called executeUpdate() for save the data by using PreparedStatement object.
• Finally close the statement and connection.