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()


Leave a comment

Powershell – Scrubbing Data From a Webpage

One function that I have found a lot of use for is to write a script to go out to a website and scrub out data. With traditional programming languages, this was always a little tricky without calling a webservice or something like that to process the request. Powershell makes this surprisingly easy, especially if you are familiar with HTML and know what to look for. I am attaching a commented script below that should give you the basics of how to scrub a site for data. In this case, I am hitting the NIST NVE and passing in a CVE as a parameter to get more information.

#This function is responsible for connecting to the NIST NVE and retrieving the CVE data from the database.
#Params:
#$Link - the url to the cve entry 
function GetCVEDataFromNIST($Link)
{
	#assign our parameter to a local variable
	$url = $Link
	
	#create a new webrequest to go out and fetch the html contents
	$result = Invoke-WebRequest $url
	
	#$html = $result.ParsedHtml.getElementsByTagName("span") | Where "classname" -match "^label" | Select -ExpandProperty InnerText
	
	#parse the html contents to find 

tags with a class of row. The inner text will contain the CVE Summary $Summary = $result.ParsedHtml.getElementsByTagName("p") | Where "classname" -match "^row" | Select -ExpandProperty InnerText #parse the html contents to find

tags with a class of row. The inner text will contain the published date and cve score $Published = $result.ParsedHtml.getElementsByTagName("div") | Where "classname" -match "^row" | Select -ExpandProperty InnerText #Start the concatenation of data. This will take the summary and add a new line $AllCVEData = ("{0} `n" -f $Summary) #parse through the published and cve score and separate them out into two lines then concatenate into allcvedata foreach($element in $Published) { #write-host $element $AllCVEData += "`n {0}" -f $element } #write-host $AllCVEData #return the summary, published date, and cve score return $AllCVEData }

A couple of notes about this. First, if you work for a corporation, you probably need to go through a proxy. See my other article for that. Second, you have to know the html of the site. In the code above, we are looking for

and

elements that have a specific class and this tells us where our data is.

In this script, I am defining link in another function and passing it in to this one. For the curious minded, this is how I am building the url.

$Link = ("http://web.nvd.nist.gov/view/vuln/search-results?query={0}&search_type=all&cves=on" -f $CVE)

Again, you have to research the site. In this case, the url takes a parameter in the query string of the cve. You can simply sub this out with your own input and get custom results back. For the evil-minded out there, please don’t try to fuzz the inputs on NIST’s site, it likely won’t end well for you.


Leave a comment

Powershell – Sending an Email

Another task that I am commonly automating in Powershell is the sending of emails. As long as you have some SMTP credentials, this is relatively easy. Please note that I have removed any sensitive information.

#this function is responsible for all mail handling
#Params:
#$to - the email address of the recipient
#$subject - the subject of the email
#$body - the body of the email
function sendMail($to, $subject, $body)
{
     Write-Host "Sending Email"

     #SMTP server name
     $smtpServer = "xxx.xxxxxxx.com"     

     #Creating a Mail object
     $msg = new-object Net.Mail.MailMessage

     #Creating SMTP server object
     $smtp = new-object Net.Mail.SmtpClient($smtpServer)

     #Email structure
	 #all emails will come from the XXXXXX mailbox. 
     $msg.From = "no-reply@noreply.com"
     $msg.ReplyTo = "no-reply@noreply.com"
     $msg.To.Add($to)
     $msg.subject = $subject
     $msg.body = $body

     #send the mail
     $smtp.Send($msg)
}


Leave a comment

Powershell – Set a Proxy

I’ve been using Powershell A LOT lately and I thought I would start sharing some code snippets of things that I have had to learn along the way. The first thing is how to set up powershell to use a proxy. A lot of the scripts I have been writing involve going out to the web and scrubbing HTML as a data source. It is pretty simple. Just add this to your script (I always put it at the top out of habit, not sure if this is necessary).

#Set our proxy information. We are hardcoding the proxy server, but the credentials will pull from the OS
$global:PSDefaultParameterValues = @{
        'Invoke-RestMethod:Proxy'='http://proxyServer:proxyPort'
        'Invoke-WebRequest:Proxy'='http://proxyServer:proxyPort'
        '*:ProxyUseDefaultCredentials'=$true
    }