SQL Injection Attack and it’s prevention

story time, hasky is web developer and he got one project to do some feature development in MySQL database so he need to do some code and send query to database and database give response with value. let’s assume that client need feature like student data management. In that hasky need to create application like add new student, update student data, soft delete on student data and view student data. after this application deployed on xyz and we know that Attacker can be anyone and can be do anything.

One day, Jon from the admin office called hasky to ask why the system was down. After inspection, hasky found that the table containing all the students’ information was missing entirely. There aren’t any backups of the database; it has been on hasky’s “to do” list for a while, but he hadn’t gotten around to it yet. hasky is in big trouble. then he show the log and see there is drop table query.

How SQL injection works

If you use user input without modification, a malicious user can pass unexpected data and fundamentally change your SQL queries.

If your code looks something like this:

UPDATE users
    SET first_name="' + req.body.first_name +  '" WHERE id=1001;

You would expect the generated SQL to be:

UPDATE users 
    SET first_name="sak" WHERE id=1001;

But if your malicious user types their first name as:

sak", last_name="threenoob"; --

The generated SQL then becomes:

UPDATE users
    SET first_name="sak", last_name="threenoob"; --" WHERE id=1001;

Now all of your users are named sak threenoob, and that’s just not cool.

SQL injection in action

If preventive measures are not taken, SQL injection attacks can cause many problems. like steal your database, also can take over on your system in some scenario when database can run some query like exec and run system command.

Prevention

The single requirement for guarding against SQL injection is to sanitize input, also known as escaping. You can escape each input individually or use a better method known as parameter binding. Parameter binding is the way I recommend, as it offers more security.

parameter binding in node-postgres

parameter binding in node-MySQL

This looks like a prepared statement in MySQL, but you should be aware that it is a user-side implementation using connection.escape() from the MySQL module to replace all of the question marks. You can read up on this method here. This gives a similar outcome but does not strictly enforce the same rules as MySQL. Here are some notes from the documentation:

• Numbers are left untouched.
• Booleans are converted to true / false.
• Date objects are converted to 'YYYY-mm-dd HH:ii:ss' strings.
• Buffers are converted to hex strings, like X'0fa5'.
• Strings are safely escaped.
• Arrays are turned into lists, e.g. ['a', 'b'] turns into 'a', 'b'.
• Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd').
• Objects are turned into key = 'val' pairs for each enumerable property of the object. If the property’s value is a function, it is skipped. If the property’s value is an object, toString() is called on it, and the returned value is used.
• undefined / null are converted to NULL.
• NaN / Infinity are left as-is. MySQL does not support these, and trying to insert them as values will trigger MySQL errors until they implement support.

Best practices and other solutions

Stored procedures are another way to protect against SQL injection. A stored procedure is a function built into your database. Using a stored procedure means you’re less susceptible to a SQL injection. This is because data isn’t passed directly as SQL.

know about more

Client-side JavaScript is NOT a solution for validating data, ever. It can be easily modified or avoided by a malicious user with even a mediocre amount of knowledge.NEVER rely on client-side JavaScript validation.

Mass Assignment Continue…