Hi all, I was reading about sql injection attack which is explained very clearly here. Then I tried on coderanch itself on my own account.
I did this:
1. Entered my user id
2. Entered my password like this mypassword' or 'x'='x
The system reported invalid userid or password. So i came to know that SQL injection will only succeed if there are not validations done at the server side. I notice that allowing the user to enter apostrophe in the input fields cause this serious attack.
As far as I understood when ever a user is posting data to the server, before it is hitting the database we have to validate the input data.
Correct! SQL injection will only work if a server application is vulnerable for it which usually means the programmer of the application didn't know about SQL injection or wasn't careful enough!
I guess these "hacks" like yours won't work today for the more popular web sites (hopefully). This kind of security problem is well known for years and there are often ways to easily prevent such exploits. With Java/JDBC for example you should use PreparedStatements which can help to avoid that any SQL expressions given for parameter values (like in your example with the password) will lead to SQL injection exploits because all SQL statements are precompiled and therefore parameter values like x=x won't get interpreted. In the worst case an application using PreparedStatements properly will tell you that the value for a parameter is not valid, but it usually won't allow you to inject SQL snippets that easily.
Of course there is still room for plenty of other security problems. So it won't be enough to just use PreparedStatements and think that you're done and your application is highly secure ;-) Like you already said, validation is a must for publicly accessible applications like a typical web app and validation can and probably should take place in multiple places (frontend, business logic, data access layer etc.) in case of multi-tiered applications.
Thank you Mr. Marco for explaining in detail. For year I have been using Statement instead PreparedStatement when it comes to coding applications using pure jdbc instead of ORM frameworks. From now on I will use PreparedStatement-s. I thought PreparedStatement-s are used for pl/sql or while using blob or clob data types.