"SQL Injection" is subset of the unverified/unsanitized user input vulnerability ("buffer overflows" are a different subset), and the idea is to convince the application to run SQL code that was not intended. If the application is creating SQL strings naively on the fly and then running them, it's straightforward to create some real surprises.
There have been instances of dropping a table, or displaying a list of users and their passwords, from the database using this kind of applications.
A simple example of such an attack, can be using a forget password page into a deadly display all records page.How? read ahead!
Usually the sites are used to generate SQL statements on the fly. So they will generate them as
WHERE field = 'Textbox value';
Here the string that the user enters into the textbox of the page is substituted. So for a page that retrieves the password, the SQL statement would be:
WHERE userid = 'txtUserID.Text.ToString()';
So if we enter abc into the text box, it is passed on to the server as
WHERE userid = 'abc';
Now if we want to gain unauthorised access to the table all we have to do is enter some malicious code into the text box. Watch it now!
If we enter anything' OR 'x' = 'x what happens? The resultant query will be:
WHERE userid = 'anything' OR 'x' = 'x'
Did you just say wow??? So next time you are designing some form, keep this thing in mind to check if the text input in the text boxes, does not complement any inbuilt sql query. You have been warned!