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 $$