Josh-CO Dev

Solving the worlds problems one line of code at a time.


Leave a comment

Powershell – Extract Data from a Database

Another handy thing to do with powershell is extract data from a database. Like everything else Powershell, this is incredibly easy to do.

First you need to setup and connect to the database itself.

	#create our database connection
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server=ServerName;Database=DatabaseName;Integrated Security=True"
	$SqlConnection.Open()

This should be pretty straight forward. Just replace servername and databasename with your information. This connection string is for sqlserver 2012, you can substitute in the connection string for any sql server database.

Next we need to set up our command and hook it up.

	
	#create and set up our sql command
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

        $SqlCmd.CommandText = "select * from dbo.table"
	$SqlCmd.Connection = $SqlConnection
        $SqlCmd.CommandTimeout = 0

It’s worth noting here that setting the commandtimeout to 0 overrides the default timeout, which I was having trouble with.

Next, just call the execute reader and load the results in a table.

	#execute our command
	$result = $SqlCmd.ExecuteReader()
	
	#load the results of our command into a datatable
	$table = new-object System.Data.DataTable
	$table.Load($result)

If you have ever programmed in .NET before, all of this should look very familiar. Now, you can do whatever you like with the data. In my case, I loop through it and assign the values to a variable so I can use them later.

	foreach($row in $table)
	{
		#set up our variables based on the data row
		$EmailAddress = $row.Item("Email")
                ...
        }

Also, don’t forget to close out your connection after you are done. This will not happen automatically.

        #close out our sql connection to prevent it from staying open. 
	$SqlConnection.Close()
Advertisements


Leave a comment

Tools – SQLMAP

I decided to start sharing some of the tools that I use on a daily basis to perform my job, and one of my favorites is SQLMAP. SQLMAP is a command-line tool that is used for automatic SQL injection and database take over. It is built in python so it is available for any operating system that can run python.

The tool itself will support MySQL, Oracle, Microsoft SQL Server, MS Access, SQLite, and a few other database systems. Basically, if an application is important enough to have a database, SQLMAP can attack it. The tool can hit a url, a direct connection to the database, log files (such as Burp), config files, and many others. There are options to support proxies, cookies, specify injection points, etc. For a full list of options a switches consult the user manual.

It is also a great tool to use for demos. Quite often, I am invited to management level meetings to talk about application security and why it is so important. Like many things in information security, nothing is quite as convincing as giving a live demo.

If you would like to learn more about the product, go check it out at http://sqlmap.org/ or the github project at https://github.com/sqlmapproject/sqlmap.

Please note that using this tool against any database that you do not own would generally be considered illegal. Please use at your own risk. There are several platforms that you can download to test a tool like SQLMAP in your own environment, such as SQLOL or DVWA but we will leave that discussion for a later time.


Leave a comment

ORA-24338: Statement Handle Not Executed

I ran into some problems yesterday when I recompiled an application for the first time in a few months. I started getting an Oracle error stating: Statement handle not executed. Simple enough. My concern was that my database code had not changed since March, only my application code. This code is pretty complex anyway.

I have to fire off a thread that runs a big process that takes about 15 minutes and dumps the data into a table. Another thread then monitors the table and waits for the datadump to finish. After staring at my code for a while, I found this:

 PROCEDURE createReportingData (
pYear IN NUMBER,
pMonth IN NUMBER,
pDay IN NUMBER,
pUnitID IN VARCHAR2,
pGenerate IN NUMBER,
pDaily IN NUMBER)
presults OUT treturncursor)
IS
statusout INTEGER;
error_msg VARCHAR2(1000);
v_strSql VARCHAR2(4000);
v_strSql2 VARCHAR2(500);
BEGIN

The problem is the presults OUT treturncursor. My logic was expecting to output a cursor, which it used to do before the change in March. I simply commented out this line in the spec and body, and now everything works like a champ.