How to prevent basic SQL injection?

Some new web developer may still not aware that the power of SQL injection, which may just rip off your database in no time without you knowing it. SQL injection can be happen in anywhere in your web page as long as there's a textbox and it is meant to be submit and entering or passing through the database (some even able to inject by going through FORM tag). Below is one of the commonly used SQL injection:

' or 1=1--

You may try the effect of it on your login page by entering the line above in both "Username" and "Password", and the next thing you see is that you already in the designated page for valid login only, although this is not a valid one :)

What happen here is that the SQL injection actually make your user validation mulfuction. Just so you know your username and password validation should go through database for checking which looks something like:

Select * From Member where memberUsername = 'Johnny' and memberPassword = 'Bravo'

and then you check for the count to see whether such combination exist or not. However after pumping in the SQL injection, the SQL statement will look something like:

Select * From Member where memberUsername = ' ' or 1=1 --' and memberPassword = ' ' or 1=1--'

Do note that Username may not equals to empty in your database, but it has the "OR" condition, which makes the statement to be True because 1=1 is always True which will allow the intruder to pass through the validation. The -- is merely to ignore any SQL command comes after it.

To simply prevent this to happen, do bear in mind that most of the SQL injection has the single quote ( ' ) and this is the key character, hence you need to replace any single quote you see with something else so that any potential SQL injection will not work. For instance:

Me.txtUsername.Text = Replace(Me.txtUsername.Text, "'", " ")

Me.txtPassword.Text = Replace(Me.txtPassword.Text, "'", " ")

The two lines above actually replace single quote ( ' ) with empty so instead of having ' or 1=1 --, you actually get or 1=1 -- which is not a valid SQL command. But, we should also consider that some username may really having single quote in it, and how about those words that you have in forum posting like "don't", "won't", "I'd", and etc, you can store and display them as "dont", "wont", and "Id", which does look so professional. Alternative solution for this is to have lines below:

Me.txtUsername.Text = Replace(Me.txtUsername.Text, "'", "''")

Me.txtPassword.Text = Replace(Me.txtPassword.Text, "'", "''")

The new lines replaced single quote ( ' ) with 2 single quotes ( '' ) instead of an empty string, this will break the SQL injection and recognize by database as single quote ( ' ), yes no joke. So when you have your user registration or forum posting, you can keep their original entry which have the single quote ( ' ) character inserted into the table as it is, and what you see if you ever display it in the page will be the exact entries of the user.