When we write a sql queries, there is a possibility of inducing any unwanted query or conditions in the query. These induced query/ conditions will be syntactically correct. This may lead to unwanted or wrong result of the query. This is known as SQL injection. Usually these type of mistakes are seen when queries are written in application programs. This happens because queries written are not validated by the applications written or inadequate knowledge of the developer about SQL etc. As far as these queries are syntactically correct, it will get executed in the DB.
For example, suppose we have created a view with no check option. Hence if we use any DML statements on this view, it will not check for any data validity or constraint and directly insert the data into the table with which view is created. This will create redundancy in the table. But for the application, syntax for executing the DML on view would be correct and the developer will not know that this view is without check option. Hence this leads to SQL injection.
Another example would be while comparing the part of the string using LIKE operator. Consider below query :
SELECT * FROM PUBLISHER WHERE PUBLISHER_NAME LIKE ‘O'Re%’; -- Checking for O'Reilly Publisher
This query would look correct to anyone or application. But when executed in DB, it will fail to execute. Reason being the string that we are comparing has single quote within it. When it is executed in the database, it indicates end of the string to be compared. It does not consider it as a part of string. Hence the query will fail. This is another type of SQL injection in SQL. This can be avoided by using double single quotes, whenever the string itself has single quote.
SELECT * FROM PUBLISHER WHERE PUB_NAME LIKE 'O''Re%';
No records with such Publisher are present and it did not display any records, but query executed without any error.
Other types of sql injections can occur while executing DML statements, DDLs and accessing databases using DB links. One can issue wrong DML statement or DDLs, or connect to database with wrong DB links. These issues can be avoided by using dynamic queries, where it will build the query at run time using the variable and values available at run time rather than using static queries which can be wrong most of the times.