things to prevent sql injection

You can use SqlCommand to retrieve record from database.

Hi mikha e,

I generally follow couple of basic rules.
1. Filter user input (On Client Side as well as Server Side)
2. If the number of column names are known i.e. you are not building something that is supposed to be truly dynamic in nature - maintain a list of allowed column names and filter user input against them. You can also enforce UX so that user has to select columns from a list instead of entering their own text.
3. Use Parameterized Stored Procedure, please check https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet
4. For Oracle specific guidelines, please check https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Oracle_Escaping

If you don't mind spending some time reading, this one here https://www.owasp.org/index.php/SQL_Injection is an excellent guide to understand SQL Injection and what you can do to be safe against it.

Regards,
Chinmay.

Bind parameter is the safe way to prevent injections:

Something like this:

command.CommandType = CommandType.Text;
command.CommandText = "select * from table where column like "%:pValue%"";

command.Parameters.Add(":pValue", userinput);
command.Prepare();
dr = command.ExecuteReader();

Rules
1. Assume everything entering your script is a hack attempt
2. Never use any data from the wild in its raw form
3. Always check for existence before using
4. Where possible validate input (emails, phone numbers, integer values etc) - make sure the data pattern matches what you expect
5. Never insert variables directly into a query
6. For queries either escape the input (real_escape_string or similar) or use a prepared statement. Prepared statements are by far the better option and should be your defacto use case for all queries involving external input.
7. When saving data make sure you escape your input before writing it to the DB to avoid XSS attacks
8. Salt your passwords and use a cryptographically secure method for hashing them - were possible use provided library functions that do salt + hash for you rather than rolling your own.
9. Consider using CSRF tokens on your forms to verify that a form was loaded from your site before accepting a submission.
10. Go back and learn rule 1.

I always do a simple text replace on the input and remove all expected ways used to inject

the most common is -- for remark out and = signs

also limit the amount of the input so if your data is only looking for 10 characters, make sure you only allow 10 in the input

but the safest is as mentioned , use parameters

You have not mentioned the platform, WinForms / ASPx ?

@gmgenius - yes this is a winforms .

If thats the case. Set the text box with a mask and only allow text or numbers and no special chars like ' -- etc

If thats the case. Set the text box with a mask and only allow text or numbers and no special chars like ' -- etc

Just checking if you are suggesting that the validation be done in the browser?
If so this is bad advice - browser validation is for convenience only - not for security.
Not sure if there is a .Net component to this I may not be aware of so just making sure - because browser side validation is not protection against SQL injection or any other attack.

Its not a browser application, the OP posted its a Winforms application

I see that winforms was mentioned in a subsequent post - it was not in the original post.

Thank you all and I apologize for the confusion.