SQL Injection – the way I learnt it

I am writing some T-SQL code for one of the assignments and am told to watch out for SQL injection as a possible attack vector.
So I took a look around to see what it is and how it really works from a very high level. A good resource to start with SQL Injection (for that matter any attack) is the OWASP.
To try hands on with how dynamic SQL executed using the Execute statement,  I created a dummy Database in my local SQL Server instance and created some tables on it. For the purpose of this write up, I will only use one of the tables in the DB that I call “ZipCodes”. There are three dummy records in the table and here is the snapshot:

I created a Stored procedure to get me the record count from this table. Here is the code:

As you see I do not do anything fancy. The Stored procedure takes some parameters and then constructs a sql statement, @SQL which is then executed.
I execute the stored procedure using the following statement to confirm that the procedure is working just fine:

To check whether the stored procedure is validating the input parameters, I inserted the following value as part of  in execute statement:

Well that irritated my SQL Server and the Stored procedure cried out the following error:

Look at the query that the SP tried to execute (that’s why I used the Print statement in the stored procedure code).
Ok I am on the right track and this procedure is a possible candidate of an injection attacks. I as an attacker will know this looking at the result above which shows that:
1. The Stored procedure is NOT validating the inputs.
2. The stored procedure is doing something by concatenation (Remember that I as an attacker will not have access to the SP code and hence it will be an analysis of the result/error above that will give me these details.)
That’s good news. So can I get all the records in this table? Lets check out using a crafted input that looks like the one below:

Once this query is run, the result that is thrown back is below:

Well that is not what the SP is supposed to do.
Lets check the query that the SP executed to get to the result above:

This is a very very very simple scenario and hopefully all the smart developers out there are not writing code like this in there Stored Procedures. But since I just started and it took me a while to get my query going, I thought of putting this here for reference.
I will comeback to this with more tricky cases. Till then its Happy Learning to me!!