How can we prevent SQL-Injection from MySQL?

Question

We all know that we can prevent SQL-Injection in PHP by Prepared Statement of query or mysqli_real_escape_string() function. But if i want to prevent it from MySQL side ? Do you have any idea? How can i achieve it ?

Answer 1

You can use stored procedure to query the database. The stored procedure checks the data type and parameters supplied, if there is a mismatch a query is not executed.

Here is a sample of stored procedure you can use to insert a record in mysql -

DELIMITER $$
CREATE PROCEDURE book_Insert (
in title varchar(30),
in isbn varchar(30),
out bookID tinyint(3) unsigned
)
BEGIN
insert into books (title, isbn) 
    values(title, isbn);
set bookID =last_insert_id();
END $$

Answer 2

As the comment from @Ferrybig says, on the MySQL side there's no way to be sure that an SQL query is legitimate or the result of SQL injection.

Suppose the database server receives this query:

SELECT col1, col2, col3 FROM MyTable WHERE account_id = 1 
UNION SELECT user, password, NULL FROM mysql.user

This looks pretty suspicious. Why would the app want to read all passwords, and append it to the query we expect to see? There's a strong chance this is an attempt at hacking.

Another example:

SELECT col1, col2, col3 FROM MyTable WHERE account_id = 1
OR account_id = 473

Is this legitimate? Or is it illicit? Is the query executed for a user who should have privileges to read data for account 473? How can you know? On the MySQL side, the query looks exactly the same whether it was the result of SQL injection or not.

It might have been code like the following PHP, which is vulnerable to SQL injection (this is not a failing of PHP, because similar vulnerable code can be written in any programming language):

$sql = "SELECT col1, col2, col3 FROM MyTable WHERE account_id = " . $_GET['id'];

If the attacker caused the input parameter to be: "1 OR account_id = 473"

The point is, once the query is formatted in the app and submitted to MySQL, MySQL can't tell how it was formatted. MySQL can only trust that the developer of the code did format the query in a safe way.

One method of blocking illicit queries is by using a type of Web Application Firewall (WAF) that you design to recognize legitimate inputs and block requests that are not legitimate. In other words, you need to program the WAF with a whitelist or set of patterns to recognize safe requests. This list will be unique for each app, so you need to be very familiar with the app. Any modification to the app may require you to update the WAF's whitelist.

The WAF is typically a proxy at the http layer, to prevent illicit request from reaching the web app. There are also proxy solutions to protect the request between the app and the database. You program the proxy to recognize which SQL queries are expected, and it blocks any queries that have unexpected terms. It would

An example of a database firewall is https://www.oracle.com/database/technologies/security/audit-vault-firewall.html

They aren't perfect. They may block queries you want to allow. And they don't work for dynamic queries run in stored procedures.

Ultimately, you should just establish safe programming standards in your application code. Use query parameters whenever you combine unsafe data with your SQL queries.

Answer 3

You can use assuming that you input a parameter $bookID

DELIMITER $$

CREATE PROCEDURE `spGetBook`(
in bookID int)
BEGIN
   SELECT *
   FROM categories
   where bookID=bookID;
END $$