MorkaLork Development

Interesting stuff I've picked up over the years...

C#/MySql 5: Insert into table

2009-04-17 07:29:18 | 865 views | csharp class method tutorial chsarp mysql security insert

When you're reading this article I will assume that you have the basic knowledge of creating a connection, creating a database and creating a table.
You know how to use the MySqlCommand class in the sense that you can create a command and execute it.

When inserting something into a table the general syntax is:
INSERT INTO tablname(col1, col2)VALUES(val1, val2)

For example:
INSERT INTO mycontacts(name, age)VALUES('Bilbo', '111')

Now, if you've worked with databases before you might know that there are some security issues to deal with when you insert values. The most horrible of horrors is the SQL injection attack where the use can enter commands through user input. The link concerning the subject is a basic description from Wikipedia and should be read if you haven't heard of this concept before.

To avoid being hacked you use something called 'parameterized queries' which is a way to avoid commands being thrown in.

This is how you might create an insertion command:


commandLine = @"INSERT INTO mycontacts(
name,
age
)
VALUES
(
'Bob',
'49'
);";
cmd.CommandText = commandLine;
cmd.ExecuteNonQuery();


This however will leave you open to attacks. To avoid it, we use parameterized queries:


commandLine = @"INSERT INTO mycontacts(
name,
age
)
VALUES
(
@name,
@age
);";

cmd.CommandText = commandLine;

cmd.Parameters.AddWithValue("@name", inputName);
cmd.Parameters.AddWithValue("@age", inputAge);
cmd.ExecuteNonQuery();


This is also faster, as we can see in the code further down, because if we need to loop the input we only have to rewrite the parameters, not the command text.
It also looks better.

The full code




using System;
using MySql.Data.MySqlClient;

namespace MorkConsTest
{
class Program
{


static void Main(string[] args)
{
MySqlConnection connect;
MySqlCommand cmd;
string connectionLine;
string commandLine;

//========================CONNECTION PART==========================
//Create a connection string
connectionLine = "Data source=localhost;UserId=root;Password=dog;";

//Instantiate the MySqlConnection class, constructor takes one connectionstring
connect = new MySqlConnection(connectionLine);
//Instantiate the MySqlCommand class
cmd = new MySqlCommand();

try
{
//Create a connection
cmd.Connection = connect;
//Open the connection
cmd.Connection.Open();
Console.WriteLine("Connection opened.");
}
catch (NullReferenceException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}

//=========================CREATE DATABASE============================
//Create a query
commandLine = "CREATE DATABASE mydb;";
//Set the command query
cmd.CommandText = commandLine;
try
{
//Execute the command query
cmd.ExecuteNonQuery();
Console.WriteLine("Database created.");
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
//Close the connection
cmd.Connection.Close();
Console.WriteLine("Connection closed.");
}

//==============================CREATE TABLE===============================
//Alter the connection string to now select a database
connectionLine = "Data source=localhost;Database=mydb;UserId=root;Password=dog;";
connect = new MySqlConnection(connectionLine);

commandLine = @"CREATE TABLE myContacts
(
name VARCHAR(150),
age INT(3)
)";

Console.WriteLine("Attempting to create table...");
try
{
//Enter the query to the CommandText
cmd.CommandText = commandLine;
//Create and open the connection previously made
cmd.Connection = connect;
cmd.Connection.Open();
Console.WriteLine("Connection opened...");
//Execute the CommandText
cmd.ExecuteNonQuery();
Console.WriteLine("Table created!");
//Always remember to close the door after you leave :)
cmd.Connection.Close();
Console.WriteLine("Connection closed...");
}
catch (MySqlException ex)
{
//It fails!
Console.WriteLine("Operation failed, table was not created...");
Console.WriteLine(ex.ToString());
}


//================INPUT IN TABLE============================
string inputName;
string inputAge;
string inputControl;
bool ender = false;
commandLine = @"INSERT INTO mycontacts(
name,
age
)
VALUES
(
@name,
@age
);";

do
{
Console.Clear();
Console.Write("Enter a friends name: ");
inputName = Console.ReadLine();
Console.Write("Enter that friends age: ");
inputAge = Console.ReadLine();

//Set the command text
cmd.CommandText = commandLine;
//Set the parameters
cmd.Parameters.AddWithValue("@name", inputName);
cmd.Parameters.AddWithValue("@age", inputAge);

try
{
//Open a connection
cmd.Connection.Open();
//Execute the command
cmd.ExecuteNonQuery();
//Close the connection
cmd.Connection.Close();

}
catch (MySqlException ex)
{
Console.WriteLine("Error: \r\n" ex.ToString());
}

//Clear parameters
cmd.Parameters.Clear();

Console.Write("If you want to quit enter \"quit\": ");
inputControl = Console.ReadLine();
if (inputControl == "quit")
{
ender = true;
}

} while (!ender);


Console.Read();
}
}
}




Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.