how safe are parameters and what is the best way to completely secure your database

Question

first off i have found several topics which are similar but do not quite answer all my questions.

my first question if i use code like this:

MySqlCommand cmd = new MySqlCommand("SELECT `productnummer`, `NAAM`, `TYPE` `OMSCHRIJVING`, `Product-ID`, `Barcode` FROM `orders`.`producten` where (`productnummer` like(@variable) or `naam` like @variable or `type` like @variable or `omschrijving` like @variable or `product-id` like @variable or `barcode` like @variable) "and `uit assortiment` = 0");
cmd.Parameters.Add(new MySqlParameter("@variable", '%' + textBox1.Text + '%'));

how can parameters be safe if i can define my sql variable with % which (for as far as i know is an sql syntax). does this not mean that if a user would enter a % or * or something them selves it would work?

my 2nd question:

MySqlCommand cmd = new MySqlCommand("SELECT `user-id` FROM `orders`.`werknemers` WHERE username = @username and `password` = @password");
            cmd.Parameters.Add(new MySqlParameter("@username", username));
            cmd.Parameters.Add(new MySqlParameter("@password", password));

if i have a database with a table that contains usernames and passwords (hashes of passwords obviously). my application has a textbox in which to type a username and a password by the user. The password will be hashed and this data will be send to the database as seen above. if the database returns a user-id i know this user exists and i can use the user-id to communicate further, if it doesn't well obviously something was typed in wrong

is this a safe way to do this? or are there better ways?

in general it all comes down to this: what is the safest way for communicating with a database in c#?

You are doing it right. prepared statements with parameters prevent any type of SQL injection. — Robot Mess, Oct 11 '13 at 13:13

score 1 · Accepted Answer · edited May 23 '17 at 12:10

You need to look at the root problem in the query safety: non-parameterized queries present threats because the data that end-users plug into them as strings gets re-interpreted as code in a programming language (namely, a code in SQL). Parameterized queries stop that from happening: the interpretation ends with the declaration of a query parameter. Whatever gets plugged into that parameter as a value is interpreted as an ordinary sequence of characters. It never makes it into SQL interpreter (unless you make a grave mistake of using SQL's exec facility, which you should never do with data that comes close to anything entered by end-users).

As far as hashing passwords goes, no, what your code does is not safe. It is open to offline attacks, because your hash is not salted. But this is a subject of a separate answer.

how safe are parameters and what is the best way to completely secure your database

1 Answers1