Lesson 5 - SqlDataReader and Connected Databases in C# .NET
In the previous lesson, Connected Database Applications in C# .NET, we connected to a database and printed the number of rows in the database.
In today's C# .NET tutorial, we'll learn to read individual rows from a database.
Data Processing
If we need to run a query over a database that returns multiple records (our
last one returned only one number), we must use the *DataReader
class to process the query's result. The ExecuteReader()
method of
the Command
class returns an instance of this class as the result.
The *DataReader
class allows us to browse the resulting set of data
by individual records. The Read()
method is used for this. We can
access individual attributes of a record using the index or column name
specified in brackets, or using Get...()
methods, which convert
values directly to the required data type.
Example 4
Print all the words on the screen. For the words we'll want to print these
columns: Id
, English
and Spanish
.
The Solution
SqlCommand command = new SqlCommand("SELECT Id, English, Spanish FROM Word", connection); connection.Open(); SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) // until we go through all the records { Console.WriteLine("{0} {1} {2}", dataReader[0], // the Id column index dataReader["English"], // the column name dataReader.GetString(2)); // the Spanish column index converted to the required data type }
The result:
Console application
1 computer computadora
2 ball pelota
3 dog perro
4 I yo
5 like gustar
6 ictdemy ictdemy
Instead of counting, the SQL query now lists the columns we're selecting. The
SqlDataReader
reads row by row what the database returned and
prints the results to the console.
Passing Parameters
Let's make our application a real dictionary and let the user enter a word, which we'll then translate for him.
SQL Injection
First, let's see how NOT to do this. Naively, we could let the user enter a word and then we'd add it directly as a search phrase to a query. The application's source code would look like this:
// this source code is dangerous string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=VocabularyDB;Integrated Security=True"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); Console.WriteLine("Enter an English word to translate"); string word = Console.ReadLine(); SqlCommand command = new SqlCommand("SELECT Spanish FROM Word WHERE English='" + word + "'", connection); SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) // until we go through all the records { Console.WriteLine("Translation: {0}", dataReader["Spanish"]); } } Console.ReadKey();
The SQL query is similar to the previous one. However, we're no longer
interested in all rows, but only in those where the English
column
has a certain value. In SQL, we write a condition using a WHERE
clause.
Although the application seems to work correctly:
Console application
Enter an English word to translate
computer
Translation: computadora
Think about what happens when the user enters this string to translate:
'; DROP TABLE Word --
The malicious code is added directly to the query and runs over the database. We give the attacker full control over our data, in this case he'll permanently delete the entire table. This is still a relatively innocent attack, it could take our users' passwords etc..
Passing Parameters
Of course, the security issue is caused by inserting values directly into text of an SQL query. The attack is therefore called SQL injection, like inserting foreign SQL code into ours.
The principle is that we must take into account that malicious code can be in every parameter that we add to a query. You can't rely on this variable probably not containing anything from the user.
The application changes and over time only a part of a value entered by the user could get into a variable. And trust me, users are inventive. Some of them will test our application and try putting these values in there.
In the past, parameters were sanitized with a special function, which "blocked" malicious characters. Modern queries are written using Prepared Statements. These work by inserting only special tags into a query instead of parameters. Parameters are then passed separately. Let's show how to add parameters to a query correctly:
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=VocabularyDB;Integrated Security=True"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); Console.WriteLine("Enter an English word to translate"); string word = Console.ReadLine(); SqlCommand command = new SqlCommand("SELECT Spanish FROM Word WHERE English=@word", connection); command.Parameters.AddWithValue("@word", word); SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) // until we go through all the records { Console.WriteLine("Translation: {0}", dataReader["Spanish"]); } } Console.ReadKey();
Note that there's only a wildcard in SQL queries, which is
denoted by an at sign @
and any name. We don't write apostrophes
around it, as they're used later according to the sign's type. Before calling
the ExecuteReader()
method, we'll add parameters to the query, in
our case the @word
parameter and its value will be the variable
word. The database will take care of the parameters and we don't have to worry
about someone hacking into our application.