Friday, July 31, 2009

Build a .NET Application on the Oracle Database with Visual Studio 2005 or 2008

With the popularity of Microsoft's .NET Framework, many developers are hungry for information about the best means of integrating .NET applications with Oracle—not only in terms of basic connectivity, but also in relationship to effective and efficient application development using Visual Studio 2005 or 2008.

In this article, I'll explain the basic yet essential processes involved in building a .NET application that uses an Oracle database, including:

  • How to add project references to support Oracle class libraries in your .NET project
  • How to create Oracle Database connection strings
  • How to work with Connection, Command, and DataReader objects

You will have the opportunity to apply what you have learned in three practice labs, ranging in difficulty from the relatively simple to the more complex. The article's screenshots are taken from Visual Studio 2008, but the experience is very similar in Visual Studio 2005.

For information and labs about how to secure your application, see my article "Securing a .NET Application on the Oracle Database". (Also, see the OTN .NET Developer Center for technical articles covering a range of Oracle.NET application lifecycle issues.)

Note that the free Oracle Developer Tools for Visual Studio, available for download from OTN, provides a Visual Studio add-in that makes the development of .NET apps on Oracle much easier and more intuitive. That subject is beyond our scope here, however.

.NET Data Provider

In addition to basic Oracle client connectivity software, .NET applications require the use of what is known as a managed data provider (where "managed" refers to code managed by the .NET framework). The data provider is the layer between the .NET application code and the Oracle client connectivity software. In almost every case, the best performance is achieved by using a provider optimized for a specific database platform instead of the generic .NET OLE DB data provider.

Oracle, Microsoft, and third-party vendors all offer .NET data providers optimized for Oracle. Oracle and Microsoft make their Oracle data providers available for free. (Microsoft's provider for the .NET Framework 2.0 is included in the framework, but it still requires Oracle client software installation.) In this article, we will use of the Oracle Data Provider for .NET (ODP.NET), which is included with the Oracle Database or as a separate download.

ODP.NET provides standard ADO.NET data access, while exposing Oracle database-specific features, such as XML DB, data access performance optimizations, and Real Application Clusters connection pooling.

When ODP.NET and Oracle client software are installed, application development using Visual Studio can begin. It's a good idea to confirm client connectivity before starting development. If you can connect to Oracle using Oracle client software such as SQL*Plus on the same machine as Visual Studio, then you know that your Oracle client-side software is properly installed and configured.

If you are new to Oracle, see the section "Installing .NET Products" in the Oracle Database 2 Day Developer's Guide for background information regarding installing and configuring ODP.NET specifically, or to the Oracle Database Documentation Library for general information about Oracle Database.

Creating a Project in Visual Studio 2005 or 2008

Let's create an ODP.NET application that retrieves data from an Oracle database. Later, we'll see how to perform error handling with ODP.NET and handle an additional data retrieval scenario.

After starting Visual Studio, the first task is to create a project. You can either select File | New | Project as shown below or click the New Project button located directly under File.



A New Project dialog box appears. On the left side of the dialog box under Project Types, select the programming language of your choice. In our example, "Visual Basic" was chosen. On the right side under Visual Studio installed templates, choose a project template. To keep things simple, a "Windows Forms Application" is selected.



You'll want to specify meaningful names for the project name (we used OraWinApp) and the solution name (we used OraWinApp). A solution contains one or more projects. When a solution contains only one project, many people use the same name for both.

Adding a Reference

Because our project must connect to an Oracle database, it is necessary to add a reference to the ODP.NET DLL containing the data provider of our choice. Within the Solution Explorer, select the project name, right click and select Add Reference. Alternatively, you can go to the menu bar and select Project and then select Add Reference.



The Add Reference dialog box appears.


ODP.NET is found under the Oracle.DataAccess component name. Select Oracle.DataAccess from the list, then click OK to make the ODP.NET data provider known to your project.

Visual Basic/C# Statements

After adding references, it is standard practice to add Visual Basic Imports statements or C# using statements. Technically, these statements are not required but they do allow you to refer to database objects without using lengthy, fully qualified names.

By convention, these statements appear at or near the top of a code file, before the namespace or class declaration.

Imports Oracle.DataAccess.Client ' Visual Basic ODP.NET Oracle managed provider




using Oracle.DataAccess.Client; // C# ODP.NET Oracle managed provider
If you added the reference, Intellisense will help you complete the addition of an Imports or using statement as shown in Figure 5.


Connection Strings and Objects

An Oracle connection string is inseparable from Oracle names resolution. Suppose you had a database alias of OraDb defined in a tnsnames.ora file as follows:

OraDb=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=ORCL)
)
)
The OraDb alias defines the database address connection information for the client. To use the OraDb alias defined in the tnsnames.ora file shown above, you would use the following syntax:
Dim oradb As String = "Data Source=OraDb;User Id=scott;Password=tiger;" ' Visual Basic

string oradb = "Data Source=OraDb;User Id=scott;Password=tiger;"; // C#
You can modify the connection string to obviate the need for the tnsnames.ora file, however. Simply replace the name of the alias with how it would be defined in a tnsnames.ora file.
' Visual Basic
Dim oradb As String = "Data Source=(DESCRIPTION=" _
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521)))" _
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));" _
+ "User Id=scott;Password=tiger;"

// C#
string oradb = "Data Source=(DESCRIPTION="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
+ "User Id=scott;Password=tiger;";
As you can see above, the username and password are embedded in the connection string in clear text. This is the simplest approach to creating a connection string. However, the clear text approach is undesirable from a security perspective. In particular, you must understand that compiled .NET application code is only marginally more secure than the clear text source code files. It is very easy to decompile .NET DLL and EXE files and view the original clear text contents. (Encryption is in fact the appropriate solution, but that subject would be a quite lengthy digression from our discussion here.)

Next, you must instantiate a connection object from the connection class. The connection string must be associated with the connection object.

Dim conn As New OracleConnection(oradb) ' Visual Basic

OracleConnection conn = new OracleConnection(oradb); // C#
Notice that the connection string is associated with the connection object by being passed through the object's constructor, which is overloaded. The constructor's other overload allows the following alternative syntax:
Dim conn As New OracleConnection() ' Visual Basic
conn.ConnectionString = oradb

OracleConnection conn = new OracleConnection(); // C#
conn.ConnectionString = oradb;
After associating a connection string with a connection object, use the Open method to make the actual connection.
conn.Open() ' Visual Basic

conn.Open(); // C#
We'll cover error handling later.

Command Object

The Command object is used to specify the SQL command text that is executed, either a SQL string or a stored procedure. Similar to the Connection object, it must be instantiated from its class and it has an overloaded constructor. In this sample, ODP.NET will perform a SQL query to return the department name (DNAME) from the departments table (DEPT) where the department number (DEPTNO) is 10.

Dim sql As String = "select dname from dept where deptno = 10" ' Visual Basic
Dim cmd As New OracleCommand(sql, conn)
cmd.CommandType = CommandType.Text

string sql = "select dname from dept where deptno = 10"; // C#
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
Using different overloads, the syntax can be structured slightly differently. The Command object has methods for executing the command text, which will be seen in the next section. Different methods are appropriate for different types of SQL commands.

Retrieving a Scalar Value

Retrieving data from the database can be accomplished by instantiating an OracleDataReader object and using the ExecuteReader method, which returns an OracleDataReader object. Returned data is accessible by passing either the column name or zero-based column ordinal to the OracleDataReader.

Dim dr As OracleDataReader = cmd.ExecuteReader() ' Visual Basic
dr.Read()

Label1.Text = dr.Item("dname") ' retrieve by column name
Label1.Text = dr.Item(0) ' retrieve the first column in the select list
Label1.Text = dr.GetString(0) ' return a .NET data type
Label1.Text = dr.GetOracleString(0) ' return an Oracle data type
There are typed accessors for returning .NET native data types and others for returning native Oracle data types, all of which are available in C#, Visual Basic, or any other .NET language. Zero-based ordinals are passed to the accessors to specify which column to return.
OracleDataReader dr = cmd.ExecuteReader(); // C#
dr.Read();

label1.Text = dr["dname"].ToString(); // C# retrieve by column name
label1.Text = dr.GetString(0).ToString(); // return a .NET data type
label1.Text = dr.GetOracleString(0).ToString(); // return an Oracle data type
In this simplified example, the returned value of DNAME is a string and is used to set the value of the label control's text property, which is also a string. But if DEPTNO, which is not a string, had been retrieved instead, there would be a data type mismatch. The .NET runtime attempts to implicitly convert from one data type to another when the source and destination data types don't match. Sometimes the data types are incompatible and the implicit conversion fails, throwing an exception. But even when it works, it's still better to use explicit data type conversions instead of implicit data type conversion.

An explicit cast to integer is shown below:

Label1.Text = CStr(dr.Item("deptno")) ' Visual Basic integer to string cast

C# is not as forgiving as Visual Basic on implicit conversions. You'll find yourself doing explicit conversions:

label1.Text = dr.GetInt16("deptno").ToString(); // C#
You can explicitly cast scalar values as well as arrays.

Close and Dispose

Either the connection object's Close or the Dispose method should be called to close the connection to the database. The Dispose method calls the Close method.

conn.Close()   ' Visual Basic
conn.Dispose() ' Visual Basic

conn.Close(); // C#
conn.Dispose(); // C#
You don't have to explicitly call Close or Dispose if you use VB's Using keyword or C#'s using keyword.
using (OracleConnection conn = new OracleConnection(oradb)) // C#
{
conn.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select dname from dept where deptno = 10";
cmd.CommandType = CommandType.Text;

OracleDataReader dr = cmd.ExecuteReader();
dr.Read();

label1.Text = dr.GetString(0);
}
In addition, OracleCommand includes a Dispose method; OracleDataReader includes a Close and Dispose method. Closing and disposing .NET objects free up system resources, ensuring more efficient application performance, which is especially important under high load conditions. You can experiment with some of the concepts we've learned here in Lab 1 (Retrieving Data from the Database) and Lab 2 (Adding Interactivity).

Error Handling

When an error occurs, .NET applications should gracefully handle the error and inform the user with a meaningful message. Try-Catch-Finally structured error handling is a part of .NET languages; here is a relatively minimalist example of using the Try-Catch-Finally syntax:

' Visual Basic
Try
conn.Open()

Dim cmd As New OracleCommand
cmd.Connection = conn
cmd.CommandText = "select dname from dept where deptno = " + TextBox1.Text
cmd.CommandType = CommandType.Text

If dr.Read() Then
Label1.Text = dr.Item("dname") ' or use dr.Item(0)
End If
Catch ex As Exception ' catches any error
MessageBox.Show(ex.Message.ToString())
Finally
' In a real application, put cleanup code here.
End Try

// C#
try
{
conn.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select dname from dept where deptno = " + textBox1.Text;
cmd.CommandType = CommandType.Text;

if (dr.Read()) // C#
{
label1.Text = dr["dname"].ToString();
// or use dr.GetOracleString(0).ToString()
}
}
catch (Exception ex) // catches any error
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
// In a real application, put cleanup code here.



}
Although this approach will gracefully capture any errors in attempting to get data from the database, it is not user friendly. For example, look at the following message displayed when the database is unavailable:


An ORA-12545 is quite meaningful for an Oracle DBA or developer, but not for an end user. A better solution is to add an additional Catch statement to trap for the most common database errors and provide user-friendly messages.
Catch ex As OracleException ' catches only Oracle errors
Select Case ex.Number
Case 1
MessageBox.Show("Error attempting to insert duplicate data.")
Case 12545
MessageBox.Show("The database is unavailable.")
Case Else
MessageBox.Show("Database error: " + ex.Message.ToString())
End Select
Catch ex As Exception ' catches any error
MessageBox.Show(ex.Message.ToString())

catch (OracleException ex) // catches only Oracle errors
{
switch (ex.Number)
{
case 1:
MessageBox.Show("Error attempting to insert duplicate data.");
break;
case 12545:
MessageBox.Show("The database is unavailable.");
break;
default:
MessageBox.Show("Database error: " + ex.Message.ToString());
break;
}
}
catch (Exception ex) // catches any error not previously caught
{
MessageBox.Show(ex.Message.ToString());



}
Notice the two Catch statements in the code sample above. If there aren't any Oracle errors to catch, the first statement branch is skipped, leaving any other non-Oracle error to be caught by the second statement. Catch statements must be ordered in the code from most specific to most general. After implementing the user-friendly exception handling code, the ORA-12545 error message appears as follows:


The Finally code block is always executed regardless of whether or not an error occurred. It is where cleanup code belongs. If you don't use Using or using, you should dispose your connection and other objects in the Finally code block.

Retrieving Multiple Values Using a DataReader

So far our examples have only showed how to retrieve a single value. An OracleDataReader can retrieve values for multiple columns and multiple rows. First consider a multiple column, single row query:

select deptno, dname, loc from dept where deptno = 10
To obtain the values of the columns, either zero-based ordinals or column names can be used. Ordinals are relative to the order in the query. Thus, the LOC column's value can be retrieved in Visual Basic by using either dr.Item(2) or dr.Item("loc").

Here is a code snippet that concatenates the DNAME and LOC columns from the previous query:

Label1.Text = "The " + dr.Item("dname") + " department is in " + dr.Item("loc") ' VB

label1.Text = "The " + dr["dname"].ToString() + " department is in " +
dr["loc"].ToString(); // C#
Now consider a query that returns multiple rows:
select deptno, dname, loc from dept
To process multiple rows returned from an OracleDataReader, some type of looping construct is needed. Furthermore, a control that can display multiple rows is desirable. An OracleDataReader is a forward-only, read-only cursor, so it can't be bound to an updateable or fully scrollable control such as a Windows Forms DataGrid control. An OracleDataReader is compatible with a ListBox control, as the following code snippet illustrates:
While dr.Read() ' Visual Basic
ListBox1.Items.Add("The " + dr.Item("dname") + " department is in " + dr.Item("loc"))
End While

while (dr.Read()) // C#
{
listBox1.Items.Add("The " + dr["dname"].ToString() + " department is in " +
dr["loc"].ToString());



}
Lab 3 (Retrieve Multiple Columns and Rows with an OracleDataReader) highlights some of these concepts.

Building and Running on x64

When running Visual Studio 2008 on an x64 operating system, the Active solution platform defaults to Any CPU. Change that to x86 before building your project.



Conclusion

This article has introduced you to the process of accessing Oracle databases using .NET programming languages. You should now have the capability to connect to the database and retrieve multiple columns and rows.


the next post is about the lab section of our post .

you can find the original source at Oracle's website .


Thursday, July 23, 2009

Arp Cache poisoning revealed

Anatomy of an ARP Poisoning Attack
by Corey Nachreiner, WatchGuard Network Security Analyst

Hackers lie. Skillful hackers lie well. And well-rounded hackers can lie both to people and to machines.

Lying to people, known as "social engineering," involves tactics (detailed at length by convicted hacker Kevin Mitnick) such as posing as a company's employee so the company's real employees will blab secrets freely. Lying to machines involves lots of different techniques, and a commonly used one -- ARP Cache Poisoning -- is the focus of this article. ARP poisoning enables local hackers to cause general networking mayhem. Because it's mostly "incurable," every administrator should be aware of how this attack works.
ARP Refresher

In Foundations: What Are NIC, MAC, and ARP?, we explained that Address Resolution Protocol (ARP) is how network devices associate MAC addresses with IP Addresses so that devices on the local network can find each other. ARP is basically a form of networking roll call.

ARP, a very simple protocol, consists of merely four basic message types:

1.

An ARP Request. Computer A asks the network, "Who has this IP address?"
2.

An ARP Reply. Computer B tells Computer A, "I have that IP. My MAC address is [whatever it is]."
3.

A Reverse ARP Request (RARP). Same concept as ARP Request, but Computer A asks, "Who has this MAC address?"
4.

A RARP Reply. Computer B tells Computer A, "I have that MAC. My IP address is [whatever it is]"

All network devices have an ARP table, a short-term memory of all the IP addresses and MAC addresses the device has already matched together. The ARP table ensures that the device doesn't have to repeat ARP Requests for devices it has already communicated with.

Here's an example of a normal ARP communication. Jessica, the receptionist, tells Word to print the latest company contact list. This is her first print job today. Her computer (IP address 192.168.0.16) wants to send the print job to the office's HP LaserJet printer (IP address 192.168.0.45). So Jessica's computer broadcasts an ARP Request to the entire local network asking, "Who has the IP address, 192.168.0.45?" as seen in Diagram 1.

All the devices on the network ignore this ARP Request, except for the HP LaserJet printer. The printer recognizes its own IP in the request and sends an ARP Reply: "Hey, my IP address is 192.168.0.45. Here is my MAC address: 00:90:7F:12:DE:7F," as in Diagram 2.

Now Jessica's computer knows the printer's MAC address. It sends the print job to the correct device, and it also associates the printer's MAC address of 00:90:7F:12:DE:7F with the printer's IP address of 192.168.0.45 in its ARP table.
Hey ARP, Did You Know Gullible Is Not in the Dictionary?

The founders of networking probably simplified the communication process for ARP so that it would function efficiently. Unfortunately, this simplicity also leads to major insecurity. Know why my short description of ARP doesn't mention any sort of authentication method? Because in ARP, there is none.

ARP is very trusting, as in, gullible. When a networked device sends an ARP request, it simply trusts that when the ARP reply comes in, it really does come from the correct device. ARP provides no way to verify that the responding device is really who it says it is. In fact, many operating systems implement ARP so trustingly that devices that have not made an ARP request still accept ARP replies from other devices.

OK, so think like a malicious hacker. You just learned that the ARP protocol has no way of verifying ARP replies. You've learned many devices accept ARP replies before even requesting them. Hmmm. Well, why don't I craft a perfectly valid, yet malicious, ARP reply containing any arbitrary IP and MAC address I choose? Since my victim's computer will blindly accept the ARP entry into its ARP table, I can force my victim's gullible computer into thinking any IP is related to any MAC address I want. Better yet, I can broadcast my faked ARP reply to my victim's entire network and fool all his computers. Muahahahahaa!

Back to reality. Now you probably understand why this common technique is called ARP Cache Poisoning (or just ARP Poisoning): the attacker lies to a device on your network, corrupting or "poisoning" its understanding of where other devices are. This frighteningly simple procedure enables the hacker to cause a variety of networking woes, described next.
All Your ARP Are Belong To Us!

The ability to associate any IP address with any MAC address provides hackers with many attack vectors, including Denial of Service, Man in the Middle, and MAC Flooding.
Denial of Service

A hacker can easily associate an operationally significant IP address to a false MAC address. For instance, a hacker can send an ARP reply associating your network router's IP address with a MAC address that doesn't exist. Your computers believe they know where your default gateway is, but in reality they're sending any packet whose destination is not on the local segment, into the Great Bit Bucket in the Sky. In one move, the hacker has cut off your network from the Internet.
Man in the Middle

A hacker can exploit ARP Cache Poisoning to intercept network traffic between two devices in your network. For instance, let's say the hacker wants to see all the traffic between your computer, 192.168.0.12, and your Internet router, 192.168.0.1. The hacker begins by sending a malicious ARP "reply" (for which there was no previous request) to your router, associating his computer's MAC address with 192.168.0.12 (see Diagram 3).

Now your router thinks the hacker's computer is your computer.

Next, the hacker sends a malicious ARP reply to your computer, associating his MAC Address with 192.168.0.1 (see Diagram 4).

Now your machine thinks the hacker's computer is your router.

Finally, the hacker turns on an operating system feature called IP forwarding. This feature enables the hacker's machine to forward any network traffic it receives from your computer to the router (shown in Diagram 5).

Now, whenever you try to go to the Internet, your computer sends the network traffic to the hacker's machine, which it then forwards to the real router. Since the hacker is still forwarding your traffic to the Internet router, you remain unaware that he is intercepting all your network traffic and perhaps also sniffing your clear text passwords or hijacking your secured Internet sessions.
MAC Flooding

MAC Flooding is an ARP Cache Poisoning technique aimed at network switches. (If you need a reminder about the difference between a hub and a switch, see this sidebar.) When certain switches are overloaded they often drop into a "hub" mode. In "hub" mode, the switch is too busy to enforce its port security features and just broadcasts all network traffic to every computer in your network. By flooding a switch's ARP table with a ton of spoofed ARP replies, a hacker can overload many vendor's switches and then packet sniff your network while the switch is in "hub" mode.
Scared? Good, Now Calm Down!

This is scary stuff. ARP Cache Poisoning is trivial to exploit yet it can result in very significant network compromise. However, before you jump to Defcon-7, notice the major mitigating factor: only local attackers can exploit ARP's insecurities. A hacker would need either physical access to your network, or control of a machine on your local network, in order to deliver an ARP Cache Poisoning attack. ARP's insecurities can't be exploited remotely.

That said, hackers have been known to gain local access to networks. Good network administrators should be aware of ARP Cache Poisoning techniques.

Since ARP Cache Poisoning results from a lack of security in a protocol that is required for TCP/IP networking to function, you can't fix it. But you can help prevent ARP attacks using the following techniques.
For Small Networks

If you manage a small network, you might try using static IP addresses and static ARP tables. Using CLI commands, such as "ipconfig /all" in Windows or "ifconfig" in 'NIX, you can learn the IP address and MAC address of every device in your network. Then using the "arp -s" command, you can add static ARP entries for all your known devices. "Static" means unchanging; this prevents hackers from adding spoofed ARP entries for devices in your network. You can even create a login script that would add these static entries to your PCs as they boot.

However, static ARP entries are hard to maintain; impossible in large networks. That's because every device you add to your network has to be manually added to your ARP script or entered into each machine's ARP table. But if you manage fewer than two dozen devices, this technique might work for you.
For Large Networks

If you manage a large network, research your network switch's "Port Security" features. One "Port Security" feature lets you force your switch to allow only one MAC address for each physical port on the switch. This feature prevents hackers from changing the MAC address of their machine or from trying to map more than one MAC address to their machine. It can often help prevent ARP-based Man-in-the-Middle attacks.
For All Networks

Your best defense is understanding ARP Poisoning and monitoring for it. I'd highly recommend deploying an ARP monitoring tool, such as ARPwatch, to alert you when unusual ARP communication occurs. This kind of vigilance is still the greatest weapon against all kinds of attack -- for, as Robert Louis Stevenson wrote, "The cruelest lies are often told in silence."

Tuesday, July 14, 2009

Which Programming language !?

A general question "which programming language I must choose !?"
this is a question that can say everyone questioned for become a programmer .
well, there are 2 factors that must be considered as well, first is interest and second is the purpose .
the most important part is first section, interest .
when you are interested in a programming language then you will try as you can for learning and working with it and if you don't interested in your programming language that you work on it, it's a bit crucial ...
the second factor is your purpose, all the works in the world have a purpose that is in the background of it .
simply ask this question of yourself, " what thing I want from a programming language !? ", when you ask it of yourself and answer it truthful then you got the purpose .
for example a person wants a Programming language for only database programming and writing automation softwares, then the best choice is Delphi .
another person wants to write System programs, the the best is C++, another one wants to write applications that can be shown in the web, ASP.net / PHP is one of these choice and etc ...
every programming language have Good parts and bad parts, and none of them is unqualified .
you can choose the language that you want with considering and investigating these 2 factors,
and in my opinion here is a Good selection list :
DataBase Programmming : Delphi with .net support .
Network Programming : C#.net
Web Programming : ASP.Net / PHP
simple applications like simple automation softwares : Visual basic with .Net support or VB.Net
Secure web applications : Java
System Programs : C++
Ring0 and Driver Programming : Pure C

there's Good resource about the list that I mentioned out there,
one of the best place you can find great books for free is Flazx and PdfChm .
try that now, nJoy ;)

feedback !?