What are good ways to prevent SQL injection

Question

I have to program an application management system for my OJT company  The front end will be done in C  and the back end in SQL   Now I have never done a project of this scope before  in school we had only basic lessons about SQL  Somehow our teacher completely failed to discuss SQL injections  something which I have only now come in contact with by reading about it on the net   So anyway my question is  how do you prevent SQL injections in C   I vaguely think that it can be done by properly masking the text fields of the application so that it only accepts input in a specified format  For example  an e-mail textbox should be of the format  example examplecompany tld   Would this approach be sufficient  Or does  NET have pre-defined methods that handle stuff like this  Can I apply a filter to a textbox so it only accepts email-address format or a name textbox so it doesn t accept special chars

User · Answer

SQL injection should not be prevented by trying to validate your input; instead, that input should be properly escaped before being passed to the database.

How to escape input totally depends on what technology you are using to interface with the database. In most cases and unless you are writing bare SQL (which you should avoid as hard as you can) it will be taken care of automatically by the framework so you get bulletproof protection for free.

You should explore this question further after you have decided exactly what your interfacing technology will be.

User · Answer

SQL injection can be a tricky problem but there are ways around it   Your risk is reduced your risk simply by using an ORM like Linq2Entities  Linq2SQL  NHibrenate   However you can have SQL injection problems even with them   The main thing with SQL injection is user controlled input  as is with XSS    In the most simple example if you have a login form  I hope you never have one that just does this  that takes a username and password   SELECT   FROM Users WHERE Username        username      AND password        password         If a user were to input the following for the username Admin  -- the SQL Statement would look like this when executing against the database   SELECT   FROM Users WHERE Username    Admin  --  AND password        In this simple case using a paramaterized query  which is what an ORM does  would remove your risk   You also have a the issue of a lesser known SQL injection attack vector and that s with stored procedures   In this case even if you use a paramaterized query or an ORM you would still have a SQL injection problem   Stored procedures can contain execute commands  and those commands themselves may be suceptable to SQL injection attacks   CREATE PROCEDURE SP GetLogin  username varchar 100    password varchar 100  AS DECLARE  sql nvarchar 4000  SELECT  sql     SELECT   FROM users                    FROM Product Where username          username       AND password        password       EXECUTE sp executesql  sql   So this example would have the same SQL injection problem as the previous one even if you use paramaterized queries or an ORM  And although the example seems silly you d be surprised as to how often something like this is written   My recommendations would be to use an ORM to immediately reduce your chances of having a SQL injection problem  and then learn to spot code and stored procedures which can have the problem and work to fix them  I don t recommend using ADO NET  SqlClient  SqlCommand etc     directly unless you have to  not because it s somehow not safe to use it with parameters but because it s that much easier to get lazy and just start writing a SQL query using strings and just ignoring the parameters   ORMS do a great job of forcing you to use parameters because it s just what they do     Next Visit the OWASP site on SQL injection https   www owasp org index php SQL Injection and  use the SQL injection cheat sheet to make sure you can spot and take out any issues that will arise in your code   https   www owasp org index php SQL Injection Prevention Cheat Sheet  finally I would say put in place a good code review between you and other developers at your company where you can review each others code for things like SQL injection and XSS  A lot of times programmers miss this stuff because they re trying to rush out some feature and don t spend too much time on reviewing their code

User · Answer

By using the SqlCommand and its child collection of parameters all the pain of checking for sql injection is taken away from you and will be handled by these classes   Here is an example  taken from one of the articles above   private static void UpdateDemographics Int32 customerID      string demoXml  string connectionString           Update the demographics for a store  which is stored          in an xml column        string commandText    UPDATE Sales Store SET Demographics    demographics              WHERE CustomerID    ID         using  SqlConnection connection   new SqlConnection connectionString                 SqlCommand command   new SqlCommand commandText  connection           command Parameters Add   ID   SqlDbType Int           command Parameters   ID   Value   customerID              Use AddWithValue to assign Demographics              SQL Server will implicitly convert strings into XML          command Parameters AddWithValue   demographics   demoXml            try                       connection Open                Int32 rowsAffected   command ExecuteNonQuery                Console WriteLine  RowsAffected   0    rowsAffected                     catch  Exception ex                        Console WriteLine ex Message

User · Answer

My answer is quite easy   Use Entity Framework for communication between C  and your SQL database  That will make parameterized SQL strings that isn t vulnerable to SQL injection   As a bonus  it s very easy to work with as well

[c#] What are good ways to prevent SQL injection?

Examples related to c#

Examples related to sql

Examples related to security

Examples related to sql-injection