If you do not use object relational mapping frameworks such as Entity Framework then this article is for you. Whether you write desktop or web applications, you should never use you use raw SQL statements when passing data input or parameters to the database. Web based applications and websites could be compromised via SQL Injection. Desktop applications can fail to work if a user enters a string that contains a single quote. One of the solutions to the above problem is sanitizing the user input via custom functions. The efficient solution to the above problem is using prepared statements. Prepared statements have two major components;
  • Prepare statement – the insert, update or delete query is sent to the database engine with field names only without values. The database engine parses, compiles and optimizes the query.
  • Execute – the prepared statement is supplied with the values which it binds to the prepared statement field and executes them. It doesn’t matter if the supplied data input or parameters contain illegal characters or crafted SQL Injection statements. They will be treated as input and won’t have any affect.
The diagram below shows how prepared statements work. prepared statements

Advantages of prepared statements

  • They are compiled once and repeatedly re-used – this makes them more efficient for frequently used queries.
  • Protection against SQL Injection – the data input is automatically sanitized
  • Increases productivity – you do not have to write data sanitizing functions which can be time consuming.
For a practical tutorial on how to use the prepared statements, visit the URL below. Feel free to ask questions via the comments section if you are stuck with the practical tutorial or you would like some clarifications.

.Net Prepared Statements Practical Tutorial