SQL Injection
- A SQL Injection is an attack that consist of insertion ot injection of a SQL query via the input data from the client to the applicaiton.
- SQL Injection is the code injection technique where the malicious SQL queries are inserted into an entry field and then executed by the application.
A successful SQL injection expert can easily exploit this vulnerability to gain access to the database. They can:
- read sensitive data from database,
- modify database data (insert, update, delete),
- and even execute arbitrary SQL commands such as administrative operations (such as shut own the DBMS).
Types of SQL Injection
-
Classic SQL Injection
- Error based SQL Injection
- It relies on error message thrown by the database server to obtain information about the structure of the database.
- Union based SQL Injection
- It forces
UNION
SQL operator to combine the results of one or more select statements into a single results which is then returned as a response.
- It forces
- Error based SQL Injection
-
Blind SQL Injection
- Boolean Based
- It forces the application to return a different result depending on whether the query returns
True
orFalse
result.
- It forces the application to return a different result depending on whether the query returns
- Boolean Based
Prevention technique
-
Prepared Statement (With parameterized query)
- Paramaterized query force the developer to first refine all the SQL code and pass each parameter to query later.
- This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.
-
Prepared Statements ensure that an attacker is not able to change the intent of the query, even if SQL commands are inserted by an attackers.
-
Explaination: Can't be able to change the paramter later.
-
Stored Procedure
- Stored procedure are not always safe from SQL injection, however it tries to maintain certain standard of security.
- Stored Procedure programming constructs have the same effect as the use of parameterized queries when incremented safely which is the norm for most stored procedure languages.
-
White List Input Valdation
-
Whitelist validation is the practice of only accepting input that is known to be good.
This can involve validating compliance with the expected type, length or size, numeric range, or other format standards before accepting the input for further processing.
-
-
Escaping all user supplied input.
- Escaping all user supplied input is the practice of escaping all user supplied input before passing it to the database.
- While escaping, the input is converted to a format that is safe for the database to process.
- The escaped input doesn't perform any operation on the database, even if the input contains malicious SQL commands.
Steps to Build JDBC Application using Prepared Statement
- Load and Register driver class.
Class.forName("Oracle.jdbc.OracleDriver");
- Establish Conection
Connection con = Driver.Manager.getConnection( url , DbUsername , DbPassword );
- Creation of statement Object
String INSERT_USERS_SQL = "INSERT INTO employee" + " (id, first_name, last_name) VALUES " + " (?, ?, ?);" PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL) preparedStatement.setInt(1, 1); preparedStatement.setString(2, "Arjun"); preparedStatement.setString(3, "Adhikari");
- Send and Execute SQL Query
Result rs = preparedStatement.executeUpdate();
-
Process Result from ResultSet.
If rs is result from Select Query, then we can use while loop to iterate through the result set.If rs is int (Non-Select Query).while(rs.next()) { System.out.Println(rs.getInt(1)); }
System.out.Println(rs + " rows affected");
-
Close connection
con.close();
Types of SQL Commands
- DDL(Data Definition Language)
CREATE TABLE, ALTER, DROP
- DQL(Data Query Language)
SELECT
- DCL(Data Control Language)
ALTER, PASSWORD, GRANT ACCESS
- DML(Data Manipulation Language)
INSERT, DELETE, UPDATE
- (Data Administration)
START AUDIT
- (Data Transaction)
COMMIT, ROLLBACK, SAVEPOINT
What should I know as Java Developer ?
Select Queries (DQL)
Returns Result Set
Non-Select Queries (DML)
Returns Numeric Value
Methods to Execute SQL Query
executeQuery()
-
To execute
SELECT
queries.public result executeQuery(String sqlQuery) throws SQLException
Eg:
Result rs = st.executeQuery("SELECT * FROM employess"); while(rs.next()){ System.out.println(" ID : " rs.getInt(1)); }
-
executeUpdate()
- Applicable for non-select operation(DML)
public int executeUpdate(String sqlQuery) throws SQLException
Eg:
int affectedRow = st.executeUpdate("INSERT INTO employees(`id`) VALUES ('1')");
Returns the afftected rows.
execute()
- For both
SELECT
and NON-SELECT
operations. - If you don't know
SQL
query until run-time, we can use execute(). - Return type =>
boolean
🚀 True -SELECT
Query
🚀 false - Non-SELECT
Query
public boolean execute (String query) throws SQLException
Eg:
boolean b = st.execute(Dynamically provide SQLQuery);
if(b == true) {
ResultSet rs = st.getResultSet();
}
else
{
int rowCount = st.getUpdateCount();
}