How to prevent SQL Injection basics

In my career I have seen couple of ways that coders use when they dealing with databases.

• One is direct calling of sql command string coded inside of the system. (I also used to use the same way once as I can remember).
• Using stored procedures to work with the system 
• Using EF to connect the system and the database { I'm .NET guy :); }

From above methods if you use the first mechanism, because it's easy and you are lazy, your system will be end up with some serious SQL Injection capabilities.

In this case the sql command is send to the SQL server like a string and the it's build inside the SQL server and after that it's executed and provided the result. This process is same like creating a runtime sql command and executing it. Screenshot below is an example of a scenario like that.

 {So who starting coding like this :)  (I did ) ;}

Developer thinks and code for what he sees, In his point of view this is easy and does the work, Let's look at the SQL side;

This is a sample what SQL server will create for the above code. So simply it's correct but what can go wrong is some one can inject sql codes at runtime like below.

In normal scenario  variable  @login_name will equal to a name,

 like : SET @login_name='''chathura''';

And the script genarated will be like this.

SELECT [id] FROM [user_list] 

WHERE [user_name] = 'chathura'  AND [password] = ''

 this is a perfect scenario but what happens someone inserted their user_name like this;

 SET @login_name='''chathura''' +' OR 1=1'

This is gonna hurt big time, because your sql code is generating on the fly, now the result will be like this :

SELECT [id] FROM [user_list] 

WHERE [user_name] = 'chathura' OR 1=1 AND [password] = ''

So it's up to you, you want to enable SQL Injection feature to your application or not :);

Baseline : don not use sql statements like the above, either use store procedures or parameterized query;

{This is my first article so feel free to comment what I have to improve; thanks;}