Get up to 80 % extra points for free! More info:

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.


 

Previous article
Connected Database Applications in C# .NET
All articles in this section
Databases in C# .NET - ADO.NET
Article has been written for you by Filip Smolík
Avatar
User rating:
No one has rated this quite yet, be the first one!
Activities