Working with Databases | Internet Forensics

There are a lot of ways to work with databases in PowerShell because, under the hood, it uses ADO.NET. ADO.NET provides a few different ways to work with databases. It's important for you to know that no one way is better than any other. In fact, the only reason different ways exist is that some of them are easier for certain tasks than others. For example, it's possible to create a "database" entirely in memory in PowerShell without an actual physical database existing underneath. The following example is not a script, it's just being run interactively at the command-line:

 PS C:\> $table = new-object system.data.datatable "Machines" PS C:\> $col1 = new-object system.data.datacolumn userid,([string]) PS C:\> $col2 = new-object system.data.datacolumn comp,([string]) PS C:\> $table.columns.add($col1) PS C:\> $table.columns.add($col2) PS C:\> $row1 = $table.newrow() PS C:\> $row1.userid = "Don" PS C:\> $row1.comp = "DON-PC" PS C:\> $table.rows.add($row1) PS C:\> PS C:\> $table userid                                  comp ------                                  ---- Don                                     DON-PC PS C:\> ($table.rows)[0] userid                                  comp ------                                  ---- Don                                     DON-PC PS C:\>

Even though this isn't complicated, it is unlike anything else we've done in PowerShell. So, let's walk through it one line at a time. First, we'll use the New-Object cmdlet to create a new .NET object, the System.Data.Datatable object:

 PS C:\> $table = new-object system.data.datatable "Machines"

Next, we'll create two new columns for the table, naming them UserID and Comp. We'll also make sure they're of the String type (Appendix B lists more types you can use):

 PS C:\> $col1 = new-object system.data.datacolumn userid,([string]) PS C:\> $col2 = new-object system.data.datacolumn comp,([string])

With the columns created, we need to attach them to the table itself:

 PS C:\> $table.columns.add($col1) PS C:\> $table.columns.add($col2)

Now we'll ask the table to create a new row. Note that this row exists entirely independently of the table at this point. Now we'll populate both columns with some data:

 PS C:\> $row1 = $table.newrow() PS C:\> $row1.userid = "Don" PS C:\> $row1.comp = "DON-PC"

Next we append the row to the actual table:

 PS C:\> $table.rows.add($row1) PS C:\>

Viewing the table displays all the rows we've added:

 PS C:\> $table userid                                  comp ------                                  ---- Don                                     DON-PC

We can refer to a specific row like this:

 PS C:\> ($table.rows)[0] userid                                  comp ------                                  ---- Don                                     DON-PC PS C:\>

So that's pretty straightforward. However, it doesn't show you how to make PowerShell utilize an existing database such as a SQL Server database or an Access database. However, it is useful to understand this technique of creating a DataTable in memory and using it without a real database underneath. For example, DataTables have methods that let you save and load data from XML files (the ReadXml and WriteXml methods). This gives you the ability to use portable data sources in your scripts. You can read more about the DataTable in Microsoft's .NET SDK documentation that is available online at http://library.msdn.com.

When you create a DataTable in this fashion, keep in mind it only lasts for the duration of the scope. So, the DataTable we created interactively in the shell in this example will exist until the shell is closed. If we put this database into a script, it will last until the script finishes running.

Persisting DataTables

Here's another example to start with:

 PS C:\> $table = new-object system.data.datatable "Machines" PS C:\> $comp = new-object system.data.datacolumn id,([string]) PS C:\> $ver = new-object system.data.datacolumn ver,([string]) PS C:\> $table.columns.add($comp) PS C:\> $table.columns.add($ver) PS C:\> $row = $table.newrow() PS C:\> $row.id = "DON-PC" PS C:\> $row.ver = "2" PS C:\> $table.rows.add($row) PS C:\> $row2 = $table.newrow() PS C:\> $row2.id = "CHRIS-PC" PS C:\> $row2.ver = "3" PS C:\> $table.rows.add($row2) PS C:\> $table id                                      ver --                                      --- DON-PC                                  2 CHRIS-PC                                3

Okay, so we have a DataTable with two rows. Now we'll save it in a file to persist the data so it lasts beyond our current PowerShell session:

 PS C:\> $table.writexml("c:\mydata.xml")

Easy! If you open the resulting file, you will see something like this:

 <?xml version="1.0" standalone="yes"?> <DocumentElement>   <Machines>     <id>DON-PC</id>     <ver>2</ver>   </Machines>   <Machines>     <id>CHRIS-PC</id>     <ver>3</ver>   </Machines> </DocumentElement>

This is useful for a number of reasons. First, if you're familiar with XML Style Sheets (XSL/XSLT), you can write a corresponding style sheet and have a Web browser like Internet Explorer display your XML file as formatted data. This is a perfect way to create a nice report! However, as-is this file is missing important schema information about how the table is structured. You can also persist that:

 PS C:\> $table.writexmlschema("c:\mydata.xsd")

This creates a file that describes how the table is structured like this:

 <?xml version="1.0" standalone="yes"?> <xs:schema  xmlns=""  xmlns:xs="http://www.w3.org/2001/XMLSchema"  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">   <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="Machines" msdata:UseCurrentLocale="true">     <xs:complexType>       <xs:choice minOccurs="0" maxOccurs="unbounded">         <xs:element name="Machines">           <xs:complexType>             <xs:sequence>               <xs:element name="id" type="xs:string" minOccurs="0" />               <xs:element name="ver" type="xs:string" minOccurs="0" />             </xs:sequence>           </xs:complexType>         </xs:element>       </xs:choice>     </xs:complexType>   </xs:element> </xs:schema>

With the XML data and the XSD schema, you can re-create the DataTable in a future PowerShell session by loading the files in:

 PS C:\> $table2.readxmlschema("c:\mydata.xsd") PS C:\> $table2.readxml("c:\mydata.xml")

Here are the results:

 PS C:\> $table2 id                                      ver --                                      --- DON-PC                                  2 CHRIS-PC                                3

Extending this idea, below is a more useful example. Datatable.ps1 reads a file named C:\Computers.txt. This file is expected to contain one username per line. For each computer, it attempts to retrieve the current service pack version, and the writes all of that information into an XML file. Note that there's no error handling since each computer is expected to be online and accessible. Keep in mind that you must have permission to remotely use WMI on each computer listed.

Datatable.ps1

 #create table structure $table = new-object system.data.datatable "SPInfo" $col1 = new-object system.data.datacolumn machine,([string]) $col2 = new-object system.data.datacolumn ver,([string]) $table.columns.add($col1) $table.columns.add($col2) #get computer names from a file $names = get-content "c:\computers.txt" #go through names foreach ($name in $names) {   $sp = get-wmiobject Win32_OperatingSystem `         -property ServicePackMajorVersion `         -computername $name   $row = $table.newrow()   $row.machine = $name   $row.ver = $sp.servicepackmajorversion   $table.rows.add($row) } #save table $table.writexml("c:\versions.xml") $table.writexmlschema("c:\versions.xsd")

Our test output looks like this:

  <?xml version="1.0" standalone="yes" ?> <DocumentElement> <SPInfo>  <machine>don-pc</machine>  <ver>2</ver>  </SPInfo> <SPInfo>  <machine>testbed</machine>  <ver>1</ver>  </SPInfo>  </DocumentElement>

However, the real power in database scripting is being able to connect to a real database such as SQL Server or Access.

Connecting to a Database

If you want to work with an existing database, we prefer a different technique - using a DataReader. However, before you can do that, you need to tell PowerShell where the database resides. There are two ways to connect to a database:

If you're using a SQL Server database, which has special support in .NET, you'll use a SqlConnection object.

For other databases, you'll use an OleDbConnection object.

Both methods are used in conjunction with a connection string, which is essentially a set of information that tells .NET where the database lives and how to log in, if necessary. Here's an example of a SQL Server connection:

 PS C:\> $conn = new-object system.data.sqlclient.sqlconnection PS C:\> $conn.connectionstring = "server=don-pc;database=mydata;" ` >> + "trusted_connection=true;" >> PS C:\> $conn.open()

If you're wondering where we got the connection string - well, we cheated. Point your Web browser to www.connectionstrings.com, select the type of database you want, and you'll find sample connection strings. You'll typically want to look for the example named "SqlConnection" or "OleDbConnection" unless you specifically know otherwise. You will need to know a bit about your database in order to connect. For instance, in the above example, we knew that the SQL Server was using integrated authentication, which is why we added "trusted_connection=true" to the connection string. For non-integrated authentication, you would add "user id=username;password=pass;" to the connection string, filling in the appropriate username and password.

Okay, now that you know how to establish a SQL Server connection, we'll show you how to connect to a database an administrator might use more often such as Microsoft Access. Here's an example of that you'll notice looks very similar, but uses the OleDbConnection object:

 PS C:\> $conn = new-object system.data.oledb.oledbconnection PS C:\> $conn.connectionstring = "provider=microsoft.jet.oledb.4.0;" ` >> + "data source=c:\mydata.mdb;" >> PS C:\> $conn.open() PS C:\>

Again, we swiped the part of the connection string that changes from database to database from www.connectionstrings.com.

Defining a Query

With an open connection to the database, you can issue queries. Some queries return data, while others modify existing data in some fashion. There are four basic types of queries that you will write in the industrystandard SQL (structured query language) syntax:

SELECT
INSERT
UPDATE
DELETE

This chapter is not intended to be an exhaustive reference to the SQL syntax. Instead, we'll give you a crash course and enough examples to let you be effective.

Querying Existing Data

To query data from the database, you'll need to know the table name, the column names you want, and any criteria you want to use to limit the data returned. For example, for a table named MyTable, I can query just columns Col1 and Col2 like this:

 SELECT Col1,Col2 FROM MyTable

Note

This isn't case-sensitive, however in this book we will capitalize SQL keywords to make them stand out a bit more.

This query returns every row from the table. However, if you want the rows where the value in Col1 is "Don," you would do this instead:

 SELECT Col1,Col2 FROM MyTable WHERE Col1= 'Don'

Notice that the string value is in single quotation marks. Numeric values don't go inside quotes or anything else. For most databases, dates also go into single quotes:

 SELECT Col1,Col2 FROM MyTable WHERE Col1 = '1/1/2006'

However, for Access, hash marks are used around dates:

 SELECT Col1,Col2 FROM MyTable WHERE Col1 = #1/1/2006#

The WHERE clause can include any normal operators such as:

= equality
> greater than
< less than
>= greater than or equal to
<= less than or equal to

So, to query a range where Col1 contains values between 5 and 7 inclusive:

 SELECT Col1,Col2 FROM MyTable WHERE Col1 >= 5 AND Col1 <= 7

Alternatively, you can use the special BETWEEN operator that works with most major database systems including SQL Server and Access:

 SELECT Col1,Col2 FROM MyTable WHERE Col1 BETWEEN 5 AND 7

That's it - a crash course in querying data. Of course, in PowerShell you'll want to put your query into a variable to make it a bit easier to use:

 PS C:\> $query = "select col1,col2 from mytable"

The only other tricky part is when you want to refer to a string value that contains the ' character. Since the SQL syntax uses the single quote as a string delimiter, having the same character appear inside a string confuses things. For example:

 $q = "SELECT Col1 FROM MyTable WHERE Col2 = 'Don's'"

As you can imagine, this will cause problems for you. To include a single quote within a string, you need to double up on the single quotes:

 $q = "SELECT Col1 FROM MyTable WHERE Col2 = 'Don''s'"

Doing so makes "Don's" a literal value that the database can handle. You can do this with a variable, too:

 $query = [string]$var.replace("'","''")

This replaces all instances of a single quote with two single quotes. Even though this takes some practice, you will get used to it.

Adding New Data

An INSERT query is used to add new data to a table. You specify the list of columns that you're adding, and then a list of values, one value per column. In most cases, you need to specify every column in the table or the database may reject your change. Here's an example:

 INSERT INTO MyTable (col1,col2) VALUES('Don','DON-PC')

Notice that the VALUES list goes in the same order as the column list you provided: "Don" goes into Col1 because they occupy the same position in their respective lists. Making this into a string variable makes it easier to use in PowerShell:

 $q = "INSERT INTO MyTable (col1,col2) VALUES('Don','DON-PC')"

However, you're more likely to have the values inside variables. In this case these values are "Don" and "DON-PC". So, you can let PowerShell insert them for you:

 $q = "INSERT INTO MyTable (col1,col2) VALUES('$val1','$val2')"

Because the string being placed into $q is delimited by double quotes, PowerShell evaluates any variables inside. This brief example proves that the contents of $val and $val1 are going into $q:

 PS C:\> $val1="Don" PS C:\> $val2="DON-PC" PS C:\> $q = "INSERT INTO MyTable (col1,col2) VALUES('$val1','$val2')" PS C:\> $q INSERT INTO MyTable (col1,col2) VALUES('Don','DON-PC') PS C:\>

Updating Data

The UPDATE query changes existing data. Typically, you'll include a WHERE clause to limit the effects to just a few rows. For example:

 UPDATE MyTable SET Col1 = 'JOE' WHERE Col2 = 'JOES-PC'

Again, placing this into a string variable using the technique discussed in the previous section makes it easier to use within PowerShell.

Deleting Data

Last but not least, the DELETE query will remove data. You'll nearly always use this with a WHERE clause, otherwise you'll delete all of the data in your table!

 DELETE FROM MyTable WHERE Col1 = 'UNKNOWN'

Place the query into a variable to make it easier to use.

Executing the Query

Once you've defined a query, it can be executed. We'll assume you've put it into a variable named $query for the following examples. To execute this query you need a SqlCommand or OleDbCommand object. Be sure to pick the one that matches the type of connection you created.

In the case of INSERT, UPDATE, and DELETE queries, you won't get any data back, so you'll use the command object's ExecuteNonQuery method. However, first you need to connect the new command object to the open database connection. Here's a complete example:

 PS C:\> $cmd = new-object system.data.oledb.oledbcommand PS C:\> $cmd.connection = $conn PS C:\> $cmd.commandtext = $query PS C:\> $cmd.executenonquery()

This assumes that $conn is already an open OleDbConnection object (from our previous example), and that $query contains an INSERT, UPDATE, or DELETE query. If you were doing this with a SQL Server connection (that is, a SqlConnection object), substitute SqlCommand for OleDbCommand.

If you're trying to retrieve data you need to use the ExecuteReader method, which returns a SqlDataReader or OleDbDataReader object:

 PS C:\> $dr = $cmd.executereader()

Once the query executes, $dr will be an OleDbDataReader that you can work with in a script or interactively. Note that the DataReader doesn't allow you to modify data; you can only view it. To modify the data, you need to issue a separate INSERT, UPDATE, or DELETE query as shown.

Reading Data

A DataReader is basically a big list of database rows, not unlike an Excel spreadsheet. A pointer keeps track of the current row. When you attempt to read data form a column, you'll be doing so with the current row in the DataReader. The DataReader starts with the pointer just before the first row. So you'll issue the Read method to move the pointer onto the first row. It will return True if there are still additional rows to be read:

 PS C:\> $dr.read() False

A few properties such as HasRows and FieldCount give you information about the DataReader:

 PS C:\> $dr.fieldcount 2 PS C:\> $dr.hasrows True PS C:\>

Remember in the SELECT query how you specified the columns you wanted? That list is important because it determines how you'll access the data. The first column is number 0, the second is number 1, and so forth. To get the value from the first column:

 $dr.getvalue(0)

You can also put all of this into a loop:

 while ($dr.Read()) {   $dr.getvalue(0) }

All of this is a bit different from working with the DataTable object we introduced earlier. However, it provides a more interactive, controllable experience and it's completely workable from the PowerShell prompt without even writing a script.

Finishing Up

When you're finished working with a DataReader or a connection, you should close them:

 $dr.close() $conn.close()

If a connection is used to populate a DataReader, the connection cannot be used for anything else while the DataReader remains open. If you want to issue other queries at the same time, you need to create a second connection for that purpose.