How to execute SQL queries from Java (and prevent SQL injections)

You need a Connection object. See the previous post to learn how to get one. With this object, simply build a new PreparedStatement, set the query parameters (NEVER USE STRING CONCATENATION OR THE APP WILL BE VULNERABLE TO SQL INJECTION ATTACKS, sorry for yelling), and run the SQL statement. Depending on whether you are modifying data or not, you call different methods to send the statement to the database.

Reading data:

try (PreparedStatement statement = connection.prepareStatement("""
            SELECT column1, column2
            FROM table_name
        """)) {
    ResultSet resultSet = statement.executeQuery();
    while (resultSet.next()) {
        String val1 = resultSet.getString(1); // by column index
        int val2 = resultSet.getInt("column2"); // by column name
        // ... use val1 and val2 ...
    }
}

Inserting, updating, or deleting data:

try (PreparedStatement statement = connection.prepareStatement("""
            INSERT INTO table_name(column1, column2)
            VALUES (?, ?)
        """)) {
    statement.setString(1, someString);
    statement.setInt(2, someInteger);
    int rowsInserted = statement.executeUpdate();
}

The setString(int, String) and setInt(int, int) methods escape special characters so attackers cannot use a malicious string that contains SQL code to perform an injection attack. There are similar methods for other Java types.

See JDBC Tutorial Part 2: Running SQL Queries, for a more detailed tutorial or watch me coding an example Java application from scratch using JDBC and a MariaDB database:


If you liked this post, consider following me on Twitter and subscribing to Programming Brain on Youtube. Happy coding!

Databases
January 12, 2022
0

Search

Popular Posts

How to call a Java method from a JavaScript function in the browser

In this video I demonstrate how to call a Java method that runs in the server f…

Testing MariaDB ColumnStore performance

MariaDB 's ColumnStore is an engine that stores data in a columnar fashion…

New YouTube channel on Programming (mostly Java)

Recently, I joined MariaDB Corporation as a Developer Advocate. My job is to h…

Recent Comments

Contact Me