I still find that most of the developers are not fully aware about the SQL Injection. Whensoever, I ask questions like; what actually it is OR how can we prevent this ? , I received some mixed sort of response. So I am going flash some light on this.
What is SQL Injection
In simple terms, SQL Injection is about injecting some unwanted/malicious input in your SQL queries so that either it prevents the system to perform from what it was supposed to do OR hijack it to perform something else.
Lets take a simple example. I have a form which asks users to input some data and I wrote a query for the same.
mysql_query("INSERT INTO table (name) VALUES ('Sandeep')");
So it makes the system vulnerable enough to play with it. I would also like to clarify that if some one unknowingly made such mistakes still a malicious would be unable to run any sub query by providing another SQL query in their input. Most of the modern Databases has already handled that properly.
For example, what is written down below would not drop the table
mysql_query("INSERT INTO table (name) VALUES ('''; DROP TABLE users')");
How to Prevent an SQL Injection
You might have heard of couple of ways to avoid it. I am gonna write some of those.
1. Avoid dodgy possibilities like ";", "--", "/*" but I would say that sometimes you are expecting a description from your user which might have some word from it. I would not vouch for this.
2. Escape the special characters in your variables For eg. mysql does provide one function mysql_real_escape_string()
3. Use a parametrized query. To use the parametrized queries, you need to use MySQLi rather than the MySQL functions.
I am listing here an example from the CodeIgniter.
Case 1: $result = $this->db->query("SELECT * FROM school WHERE name = '".$_POST['name']."'");
Case 2: $result = $this->db->query("SELECT * FROM school WHERE name = '?'", array($this->input->post('name')));
Case 2 , is an perfect example of parametrized query. This will make sure no malicious input will be processed through.
Most of the frameworks provides the support to prevent & avoid SQL Injection. You just need to make sure that you are not by passing the rules that framework provides to process your data.