Week 5 Flashcards
What is the difference between a simple statement and a prepared statement in JDBC?
A simple statement is used for executing a single SQL query directly. A prepared statement is precompiled and can be reused with different parameters, improving performance and security.
Write an example of using a prepared statement in Java to insert data into a database.
PreparedStatement pstmt = connection.prepareStatement(“INSERT INTO users (name, email) VALUES (?, ?)”);
pstmt.setString(1, “John Doe”);
pstmt.setString(2, “john@example.com”);
pstmt.executeUpdate();
Explain how prepared statements help in preventing SQL injection attacks.
Prepared statements use parameterized queries, which separate SQL code from data, preventing malicious input from altering the query structure.
How do prepared statements help in improving performance?
Prepared statements are precompiled and can be executed multiple times with different parameters without the overhead of compiling the SQL statement each time.
Can you reuse a prepared statement in JDBC? How?
Yes, prepared statements can be reused. Once prepared, you can set different parameters and execute the statement multiple times.
Write an example of using a simple statement to execute a query in Java.
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT * FROM users”);
What are some scenarios where you would prefer using a simple statement over a prepared statement?
You might use a simple statement for executing static queries or when performance is not a concern. However, prepared statements are preferred for dynamic user inputs.
What is parameter binding in prepared statements, and how does it work?
Parameter binding allows you to specify placeholders in the SQL query (e.g., ?) and then set values for these placeholders using methods like setString(), setInt(), etc.
Why is it not recommended to use simple statements with dynamic SQL queries?
Concatenating user inputs in SQL queries can lead to SQL injection, as malicious users can manipulate the input to alter the query logic.
How does a prepared statement handle queries with user input compared to a simple statement?
Prepared statements ensure that user input is treated as data, not executable code, thus protecting against SQL injection.
What common information is stored in a JDBC properties file?
JDBC properties files typically include database URL, username, password, driver class name, and any additional connection properties.
What is a JDBC properties file, and why is it used?
A JDBC properties file is a configuration file that contains database connection parameters (like URL, username, and password) to simplify database access in Java applications.
How can you load a JDBC properties file in a Java application?
Properties props = new Properties();
try (InputStream input = new FileInputStream(“db.properties”)) {
props.load(input);
}
How can you retrieve database connection details from a JDBC properties file in Java?
String url = props.getProperty(“db.url”);
String user = props.getProperty(“db.user”);
String password = props.getProperty(“db.password”);
How does using a properties file simplify database configuration?
It centralizes configuration settings, making it easier to modify connection parameters without changing the code.
Write a sample JDBC properties file for a MySQL database connection.
db.url=jdbc:mysql://localhost:3306/mydb
db.user=root
db.password=secret
db.driver=com.mysql.jdbc.Driver
How can you change the database configuration dynamically using a properties file?
You can use encryption for sensitive data or restrict access to the properties file through file permissions.
Can you secure sensitive information in a JDBC properties file? If so, how?
What are some best practices for maintaining and using JDBC properties files?
By editing the properties file, you can change connection settings without recompiling the application.
How can you handle exceptions when loading a JDBC properties file?
Keep the properties file outside the source code, avoid hardcoding sensitive information, and document configuration options.
What is a utility class in the context of JDBC, and why is it useful?
Use try-catch blocks when loading the properties file to handle potential IOException.
How do you set up a utility class for managing database connections in Java?
A utility class can be set up by creating a class with methods for opening and closing database connections.
public class DBUtil {
public static Connection getConnection() { /* implementation */ }
public static void closeConnection(Connection conn) { /* implementation */ } }
What methods should a JDBC utility class contain to manage connections effectively?
Methods for getting a connection, closing connections, handling transactions, and error logging.
Write a sample utility class that provides a method to get a database connection.
public class DBUtil {
private static final String URL = “jdbc:mysql://localhost:3306/mydb”;
private static final String USER = “root”;
private static final String PASSWORD = “secret”;
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) { /* handle exception */ }
}
} }
What is the purpose of a closeConnection method in a utility class?
It releases database resources, preventing memory leaks and connection exhaustion.
How can you ensure thread safety when using a utility class for database connections?
Use synchronized methods or implement a connection pool (like HikariCP) to manage multiple concurrent connections safely.
Explain how connection pooling can be integrated into a JDBC utility class.
Use a connection pooling library that maintains a pool of connections to be reused, reducing overhead for frequent connections.
How can you improve the reusability of a utility class in large-scale applications?
Design utility methods to be general-purpose, allowing them to be used across various parts of the application.
How can you implement exception handling in a JDBC utility class?
Include try-catch blocks to handle SQL exceptions in your utility methods and log errors appropriately.
What are the benefits of using a utility class to manage JDBC resources?
A utility class abstracts database operations, leading to cleaner code, easier maintenance, and better separation of concerns.
What is SQL injection, and how does it occur?
SQL injection is a code injection technique where an attacker can manipulate SQL queries by injecting malicious input.
What is cumulative SQL injection, and how does it differ from a simple SQL injection?
This refers to multiple sequential injections that build upon each other to exploit vulnerabilities, leading to more complex attacks.
How can cumulative SQL injection be prevented in Java applications?
Use prepared statements, parameterized queries, input validation, and whitelisting to prevent cumulative SQL injection.
Provide an example of a vulnerable query that is susceptible to cumulative SQL injection.
String query = “SELECT * FROM users WHERE username = ‘” + username + “’”;
How can you use prepared statements to prevent cumulative SQL injection?
PreparedStatement pstmt = connection.prepareStatement(“SELECT * FROM users WHERE username = ?”);
pstmt.setString(1, username);
What are some common indicators of SQL injection attacks in database logs?
Unusual database error messages, unexpected query results, or logs showing suspicious query patterns.
Explain how user input can be sanitized to prevent SQL injection.
Validate user inputs by checking against expected formats or allowed values before processing them.
What role does input validation play in preventing cumulative SQL injection?
It ensures that only valid data is accepted, preventing potentially malicious input from being executed in SQL queries.
How can you test a web application for SQL injection vulnerabilities?
Use automated tools or manual testing methods to input typical SQL injection payloads into forms or API endpoints.
What are the consequences of cumulative SQL injection attacks?
Data breaches, unauthorized access, data corruption, and potential loss of reputation for organizations.
What are the best practices to prevent SQL injection in Java applications?
Use prepared statements, parameterized queries, input validation, stored procedures, and ORM frameworks to reduce risks.
Why is it important to avoid concatenating user inputs in SQL queries?
Concatenating user inputs in SQL queries can allow attackers to inject SQL code; always use parameterized queries instead.
What is the role of parameterized queries in preventing SQL injection?
They separate SQL code from data, ensuring that user input is treated as data rather than executable code.
How do ORM tools (like Hibernate) help in preventing SQL injection?
ORM frameworks automatically handle input sanitization and parameter binding, minimizing SQL injection risks.
Explain how whitelisting input can prevent SQL injection attacks.
Define and enforce acceptable input patterns, rejecting any input that does not conform to the defined criteria.
How can you use regular expressions to validate user input and prevent SQL injection?
Use regex patterns to ensure that user inputs conform to expected formats (e.g., email addresses).
What security practices can be implemented at the database level to prevent SQL injection?
Implement role-based access control, least privilege principle, and avoid using administrative accounts for application access.
How can stored procedures help in preventing SQL injection?
Use stored procedures to encapsulate SQL logic, as they can help mitigate injection risks if properly implemented.
