What is SQL Injection? What is the best way to avoid it or is there something out there that I can follow?

If you're not careful, malicious users may be able to obtain data, change server settings, or even take over your server thanks to a general class of assaults known as SQL injection. SQL injection is an issue with poorly built apps rather than SQL Server. However, you're sure to encounter such an application at some point in your career, so you should be aware of the issue and its solution. When a user enters SQL code into a Web page as user input, the SQL code is subsequently run in the database, which is known as SQL injection (or a SQL injection attack).

Make use of secure input and output handling techniques, such as:

• Input validation
• Escaping dangerous characters.
  
• Input encoding
• Output encoding
• Other coding practices which are not prone to code injection vulnerabilities, such as "parameterized SQL queries" (also known as "prepared statements" and sometimes "bind variables").
• Modular shell disassociation from kernel

The solutions mentioned here mostly concern web-based server-side application injection of HTML or script code. When dealing with user code injection on the user machine, which leads to privilege elevation attacks, alternative strategies must be used. The following are some techniques for identifying and isolating managed and unmanaged code injections:

• Runtime image hash validation - a hash of a portion or the entire executable put into memory, then compare it to the hash that was previously stored and the predicted hash.
• NX Bit - All user data is kept in a separate memory area that has been designated as non-executable. The CPU is informed that there is no code in that area of memory and will not run any programs located there.

The NX bit, which stands for No eXecute, is a feature often found only in Harvard architecture processors that divides memory space into sections for usage by either storing processor instructions (or code) or for storing data. However, typical von Neumann architecture CPUs are increasingly using the NX bit for security purposes.

A operating system that supports the NX bit may designate specific memory locations as non-executable. After then, the CPU will not run any code stored in these regions of memory. By inserting their code into another program's data storage area and running their own code from within this section, malicious software known as buffer overflow attacks can't take control of computers thanks to the general technique known as executable space protection.

SQL injection: advantage of the syntax of SQL to inject commands that can read or modify a database, or compromise the meaning of the original query.

For example, consider a web page has two fields to allow users to enter a user name and a password. The code behind the page will generate a SQL query to check the password against the list of user names:

SELECT Username
FROM User
WHERE User.Username = 'Username'
AND User.Password = 'Password'

If this query returns any rows, then access is granted. However, if the malicious user enters a valid Username and injects some valid code ("password' OR '1'='1") in the Password field, then the resulting query will look like this:

SELECT User.Username
FROM User
WHERE User.Username = 'Username'
AND User.Password = 'password' OR '1'='1'

In the example above, "Password" is assumed to be blank or some innocuous string. "'1'='1'" will always be true and many rows will be returned, thereby allowing access.

The technique may be refined to allow multiple statements to run, or even to load up and run external programs.

Or
For example, consider the following login script:

sql = "SELECT * FROM User WHERE Username = '%UserName' and Password = '%password'";

Now consider the form with the following inputs:

User Name : jomgrg
Password : 'or 1 = 1--

Now the statement that is executed in the database is the following:

SELECT * FROM User WHERE Username= 'jomgrg' and Password= ''or 1 = 1--'

The results of this query will include all users because 1=1 is a constant. (Note that the final quotation has been deleted.) Therefore, access is granted in the script above since sqlsrv_has_rows is true.

Because user input and the SQL code that was run are concatenated in this situation, SQL injection is feasible. Such writing should never be used in scripts. There are a lot more malicious attacks that can be launched using SQL injection than what is shown in the aforementioned example.

A Common Attempt at Prevention

The most typical advice I've read for avoiding SQL injection is to try to extract or escape any potential SQL code from user input before concatenating it with the SQL code that will actually be executed.

The Right Way to Prevent SQL Injection

By employing parameterized queries, SQL injection can be avoided in the proper method. This entails specifying the SQL code to be executed with placeholders for parameter values, inserting the parameter values programmatically, and then running the query. By doing this, the server may develop an execution plan for the query and stop any "injected" SQL from running. This will be better explained with an example. Let's employ the identical script while adding placeholders for the SQL query's parameters:

Use SP or with out use

SP = "SELECT * FROM User WHERE Username = @UserName and Password = @Password";
DbCommand = "SP";
DbCommand dbCommand = dataBase.GetStoredProcCommand(sqlCommand);

dataBase.AddInParameter(dbCommand, "@UserName ", DbType.String, txtUserName.text.toString());
dataBase.AddInParameter(dbCommand, "@Password", DbType.String, Password.text.toString());

Then Execute this.
OR
Use Direct Inline query with passing parameters using C#.

Whenever sqlsrv_query is called, an execution plan is established on the server prior to the query is executed. The plan only allows our original query to be executed. Parameter values (even if they are injected SQL) won’t be executed considering they are not part of the plan. So, if I provide a password like I did in the example above ('or 1=1--), it will be treated as user input, not SQL code. In other words, the query will look for a user with this password instead of executing unexpected SQL code.

Use sqlsrv_prepare and sqlsrv_execute if you anticipate running a query several times with varied parameter values. The sqlsrv_execute function performs the query with various parameter values each time it is called after the sqlsrv_prepare function creates an execution plan on the server once.

Here, parameterized queries are demonstrated using SQL Server Express and the sqlsrv API, although this method may (and should) be used with any database and extension.

The best approach, in my opinion, is to use parametrized SQL queries.