Database connection and query in JDBC

Let's take a closer look at how the database is accessed

package sql;

import ;
import ;
import ;

public class Conn { // create class Conn
    Connection con; // Declare Connection object
    public static String user; // declare Connection object
    public static String password; public class Connection getConnection() { // Create a method that returns a Connection value.
    public Connection getConnection() { // Create a method whose return value is Connection
        try { // Load the database driver class
            (""); ("Database Driver"); // Load the database driver class.
            ("Database driver loaded successfully"); } catch (ClassNotFoundFoundFoundFor); }
        } catch (ClassNotFoundException e) {
            (); } catch (ClassNotFoundException e) {
        }
        user = "root";// database login name
        password = "root";// password
        try { // Get the database connection object from the URL to access the database
            con = ("jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=gbk", user, password); ("Database Connection"); ("Database Connection", password = "root";//database login password = "root";//password)
            ("Database Connection Successful"); } catch (SQLException); }
        } catch (SQLException e) {
            (); } catch (SQLException e) {
        }
        return con; // Return a Connection object as requested by the method
    }
    public static void main(String[] args) { // main method, test connection
        Conn c = new Conn(); // create an object of this class
        (); // Call the method that connects to the database
    }
}

Specific usage

Let's look directly at the following code

package Main;

import .*;

public class JDBC {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
// 1.Load Driver
        ("");
// 2.User information andurl
        String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password="root";
// 3.Connection successful，database object Connection
        Connection connection = (url,username,password);
// 4.fulfillmentSQLboyfriendStatement，fulfillmentSQL的boyfriend
        Statement statement = ();
// 5.fulfillmentSQL的boyfriend去fulfillmentSQL，Returns the result set
        String sql = "SELECT *FROM studentinfo;";
        ResultSet resultSet = (sql);
        while(()){
            ("SNo="+("SNo"));
            ("SName="+("SName"));
            ("Birth="+("Birth"));
            ("SPNo="+("SPNo"));
            ("Major="+("Major"));
            ("Grade="+("Grade"));
            ("SInstructor="+("SInstructor"));
            ("SPwd="+("SPwd"));
        }
// 6.Disconnect
        ();
        ();
        ();
    }
}

Loading Database Classes

("");is used forLoad Database DriverThe. Used for our Java program to communicate with the database.

()function is used to dynamically load a class, the parameters of which are naturally the database driver class

Connecting to the database

We usually use(url,username,passwd)method to connect to the database, which requires us to fill in three parameters:

url: the URL of the database, the format is important
- "jdbc:mysql": This tells the program to use the JDBC driver to connect to the MySQL database.
- "localhost": Points to the database server on the local computer.
- "3306": The default port number for the MySQL service.
- "test1": This is the name of the database to be accessed.
- ?useUnicode=true&characterEncoding=gbk: These are query parameters that specify the configuration of the database. They indicate:
- useUnicode=true: Enable Unicode support to ensure that Unicode characters can be stored and read.
- characterEncoding=gbk: Set the character encoding to GBK, which is usually used for Chinese characters.
username: Username of the database
passwd : Database password

Instantiate a SQL object Statement

There's not much to it, it's just instantiating an object so that we can call the various methods in it

Execute SQL statements to query the database

consult (a document etc)

To perform a database query we directly use theexecuteQuery(String sql)method, and then write our sql statement on the inside, after which we can get the results of the query from the return value of theResultSet resultSet = (sql);

in the end

Got one.ResultSetAfter the result object, if you want to get the string directly use thegetString()method is fine, except for thegetLong()，getInt()etc., corresponds to the data types in it.

Then there are the arguments to these get methods, and there are two kinds of arguments:

column name: that is, you query out, directly enter the name of the list will give you the output of the query results, the name of the type of nature is the type of String
Column index: enter the index of the column directly from which the results of the query will be entered in the first column, but note ！！！！ here so is no longer from 0 to start, but from 1 to start, this is very noteworthy place, so the parameter type is naturally int

release link

For the sake of not wasting resources, you should just release them when you're done using them

        ();
        ();
        ();

Improvements to prevent sql injection

Problems found

Another careful observer will notice that the preceding code uses theStatementSpelling strings is very easy to cause SQL injection problems.

What is SQL injection? We generally query the database by the relative command to achieve, if the SQL statement is to check by the character splicing out, generally no problem, but we enter some specific characters is likely to let the sql statement to do other things. In short, SQL is generally realized because of the character splicing vulnerability.

sort

So we'll have to figure out how to get around this, and one way to do that is to escape specific characters, but that's ultimately a symptomatic solution.

As we mentioned earlier, the fundamental problem is the vulnerability caused by character splicing, if we don't do character splicing, and directly pass the character to be checked, then the problem will be solved.

particle marking the following noun as a direct objectStatement exchange (sth) for (sth else)PreparedStatementpossibleAvoid SQL Injection Completelyof the problem, because thePreparedStatementAlways use?as placeholders and pass the data to the database along with the SQL itself, which ensures that the SQL statement is the same each time it is passed to the database, only the placeholder data is different, and also makes efficient use of the database's own caching of queries.

//utilizationprepareStatementconsult (a document etc)
try (Connection conn = (JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (PreparedStatement ps = ("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) {
        (1, "M"); // take note of：Indexing from1commencement
        (2, 3);
        try (ResultSet rs = ()) {
            while (()) {
                long id = ("id");
                long grade = ("grade");
                String name = ("name");
                String gender = ("gender");
            }
        }
    }
}

//utilizationStatementconsult (a document etc)
try (Connection conn = (JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (Statement stmt = ()) {
        try (ResultSet rs = ("SELECT id, grade, name, gender FROM students WHERE gender=1")) {
            while (()) {
                long id = (1); // take note of：Indexing from1commencement
                long grade = (2);
                String name = (3);
                int gender = (4);
            }
        }
    }
}

Let's take a look at the example. the top one uses prepareStatement and the bottom one uses Statement. although there is not much difference between the two, there are still things worth noting:

The sql statement inserts the function differently, the former is already inserted at prepareStatement, while the latter is inserted only at executeQuery
The prepareStatement requires the use of the setObject method to specify the character we are querying for, but the Statement does not, and we'll talk about why not later.
ResultSet (used form a nominal expression)next() method is used formoving cursor until (a time)Next line in the result setThe type of data returned by the code is undoubtedly aboolean (be) worth

Reflections from solving the problem

Once we're done analyzing it at the code level, I think a lot of people have the same question as I do.prepareStatementWhy can we do to prevent SQL injection, here we are a little bit into the SQL injection, in the detailed analysis of SQL injection is how to complete.

The Nature of SQL Injection

SQL injection vulnerabilities occur because user input is embedded directly into the query statement, which can change the structure of the entire SQL statement in the event of a carefully crafted input

For example, there is now this statement

String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

If malicious content is enteredusername = "admin' --"

SELECT * FROM users WHERE username = 'admin' --' AND password = 'password';

followed by the input of theAND password = 'password';is simply commented out so that it will only query the precedingusername = 'admin'

`prepareStatement`defense principle

The previous is not said is because the user input and query statement is not separated, then the idea is very simple, that will be separated from the two is not on the line!

placeholder separation

During the precompile phase, the structure of the SQL query is parsed and sent to the database.placeholder（?) will be treated by the database as a placeholder for parameters, not as part of the SQL statement.

Example:

String query = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement ps = (query);
(1, username);
(2, password);

It's here:

? is a placeholder, which is replaced with the actual argument when the query is executed.
(1, username) cap (a poem)(2, password) Securely binds user-entered values to the query.
When executing a query, the database knows? is just a placeholder, it doesn't parse user input as part of the SQL code, it treats it as data.

Even if a user enters malicious content, for example:

username = "admin' OR 1=1 --"
password = "password"

The constructed SQL query will also not be injected, because the database will treat this input as a normal string and not as part of the SQL statement. The SQL query at execution time will be:

SELECT * FROM users WHERE username = 'admin'' OR 1=1 --' AND password = 'password'

This query will still be passed correctly as two string values on the database side and will not be parsed as malicious SQL code

Automatic translation of user input

PreparedStatement will automatically escape special characters in parameters, such as single quotes ('), etc., so that they are correctly treated as strings in the database. This further prevents SQL injection attacks.

For example, if the user enters a username of:

admin' --

PreparedStatement will automatically escape this string:

'admin'' --'

This way, even if the user enters malicious content, the database will treat it as a normal string and will not be executed as part of the SQL statement.