Prevent Hacking through SQL Injection

How to prevent database injection and webpage hacking

Hi! Today I will discuss step by step how to prevent script insertion to database. Hackers often insert script into database. These scripts get automatically executed when the webpage is loaded and the page gets hacked.

To prevent any insert statement to insert script we create a trigger in the database.

A trigger is a SQL Program which automatically gets executed before or after insert, update or delete statement.

We create a before insert trigger and check whether the values in the columns contain "<SCRIPT>" tag. If there is any value with "<SCRIPT>" we can stop insertion or change the value so that script does not get stored in the database. In this way the hacking of website through database injection can be prevented.

We take a contact table as example.

The following SQL statement is a trigger which checks whether any column has a "<" in the value, if it finds a "<" it stores an empty string in that column.

set OLD_POS = INSTR(NEW.Email, "<");

if OLD_POS > 0 THEN

SET NEW.Email = '';

-- SIGNAL SQLSTATE '45000';

end if;

We set the value of that column to empty string

We can also prevent insertion by executing the statement SIGNAL SQLSTATE '45000';

Below is the complete code

DELIMITER $$

CREATE TRIGGER `contact_insert`

BEFORE INSERT

ON contact FOR EACH ROW

Edit trigger body code below this line. Do not edit lines above this one

BEGIN

DECLARE OLD_POS int;

set OLD_POS = INSTR(NEW.Email, "<");

if OLD_POS > 0 THEN

SET NEW.Email = '';

-- SIGNAL SQLSTATE '45000';

end if;

set OLD_POS = INSTR(NEW.FirstName, "<");

if OLD_POS > 0 THEN

SET NEW.FirstName = '';

-- SIGNAL SQLSTATE '45000';

end if;

set OLD_POS = INSTR(NEW.LastName, "<");

if OLD_POS > 0 THEN

SET NEW.LastName = '';

-- SIGNAL SQLSTATE '45000';

end if;

set OLD_POS = INSTR(NEW.Message, "<");

if OLD_POS > 0 THEN

SET NEW.Message = '';

-- SIGNAL SQLSTATE '45000';

end if;

set OLD_POS = INSTR(NEW.Subject, "<");

if OLD_POS > 0 THEN

SET NEW.Subject = '';

-- SIGNAL SQLSTATE '45000';

end if;

END;

$$

Now if in the insert statement there is a script it is truncated and when there is no script record is inserted with actual values.

INSERT INTO `contact` (`FirstName`, `LastName`, `Email`, `Subject`, `Message`) VALUES ('Rajiv', 'Ranjan', 'rajiv@gmail.com', 'Test', 'Test');

The above statement will be successfully executed

Whereas the below statement is truncated

INSERT INTO `contact` (`FirstName`, `LastName`, `Email`, `Subject`, `Message`) VALUES ('Hacked', '<SCRIPT>', '<SCRIPT>', '<SCRIPT>', '<SCRIPT>');

You can see the first statement is properly inserted but the second statement is truncated

Thanks!