Writing a String Variable To Database

Question

I am trying to write the value of 3 variables (Username, Email, Password) to an SQLite Database. I can write text to the database but not a variable. How would I do this?

My Code:

SQCommand.CommandText = "INSERT INTO login_data (username, password, email) VALUES (Username, Password, Email)"

Answer 1

The bad way: Concatenate the string with your values by using String.Format() method.

The better way: Use a Parameterized Query. Check the link: http://www.4guysfromrolla.com/webtech/092601-1.shtml

Answer 2

I would take a step back and look at your application architecture a little more. In most cases it makes sense to use an Object Relational Mapper (ORM) tool when accessing a database. If you want to use SQLite (which is a great little database) for a simple domain model like the one you are describing I would suggest that you take a look at Subsonic's SimpleRepository data access system. It should make short work of your problem.

Answer 3

I'm not a VB guy so I'm guessing here...

SQCommand.CommandText = "INSERT INTO login_data (username, password, email) VALUES ("  + Username + "," + Password + "," + Email + ")"

There are better ways to do this, but for quick and dirty this should get the job done.

Edit:---- Because of all of the drive-bys saying this is bad practice I guess I should add a caveat here.

IFF the contents of these variables were provided by the user, AND they have not already been either Escaped or validated in some way. They might contain SQL commands, so a simple concatenation at this point could expose your database to a SQL Injection attack.

I should also tip my hat to all of the programmers who can tell from looking at only a single line of code and knowing nothing else about the program that simple concatenation is clearly the wrong thing to do here. Your understand of code is clearly magical.