Connect to SQL Database and Output data to CSV File from Table using Powershell

This is sample Powershell code for connecting to SQL Database and outputting table data to a CSV file.

Code below was tested by me.

Reference: 
https://stackoverflow.com/questions/25682703/connect-to-sql-server-database-from-powershell

Powershell Code to Connect to SQL Server & Output to CSV File

$SQLServer = "aaaa.database.windows.net"
$SQLDBName = "Database"
$uid ="john"
$pwd = "pwd123"
$SqlQuery = "SELECT * from table;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$DataSet.Tables[0] | out-file "C:\Scripts\xxxx.csv"

3 Ways to Reverse a String using JavaScript

3 JavaScript Code Examples for Reversing a String

function reverseString1(str) {    
  //This way uses an array to reverse the string
  var strArr = str.split("");
  var reverseStringArray = strArr.reverse();
  var reversedString = reverseStringArray.join("");
  return reversedString;
}

reverseString1("hello")

//*******************************************
function reverseString2(str) {
  //Single Line does it all
  return str.split("").reverse().join();
}

reverseString2("hello")


//*******************************************
function reverseString3(str) {    
  var final = "";
  for (var i = str.length - 1; i >= 0; i--) {
   final += str[i]
  }
  return final;
}

reverseString3("hello")

MVC 5 Attribute Routing Made Only Slightly Easier to Understand

MVC 5 Attribute Routing

I’m studying the Microsoft MVC 5 Framework.  As part of my study practice, I force myself to write an article on the subject of whatever module I happen to be on.  This helps me cement the idea in my own head while sharing my perspective on the lesson.
Hope this helps someone…..anyone. 😉
In the Model View Controller (MVC) framework, Routes determine which controller and method to execute for a specific URL.
A slightly easier way to state this is…  routes tie specific set of code to website URL.

New in MVC 5, Microsoft introduced a cleaner way to do custom routes
This is an example of the old way to do routes, you might see it somewhere on a project.
RouteConfig.cs
routes.MapRoute(
    “MoviesByReleaseDate”,
    “movies/released/{year}/{month}”,
    new { controller = “Movies”, action=”ByReleaseDate”},
    new { year = @”2017|2018″, month = @”\d{2}” });
Controller code is not connected to the RouteConfig so if action name change is made in either one but not both, we have a big problem.
Instead of creating a messy RouteConfig file with lots of custom routes in it,  we can now add custom routes by adding an attribute to the corresponding action.
To enable attribute routing we have to add a line to the RouteConfig file.
// EnableAttribute Routing 
routes.MapMvcAttributeRoutes();

The entire file with new and old code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Routing;

namespace Vidly
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute(“{resource}.axd/{*pathInfo}”);

            // Enable Attribute Routing 
            routes.MapMvcAttributeRoutes();

            routes.MapRoute(
                “MoviesByReleaseDate”,
                “movies/released/{year}/{month}”,
                new { controller = “Movies”, action=”ByReleaseDate”},
                new { year = @”2017|2018″, month = @”\d{2}” });

           routes.MapRoute(
                name: “Default”,
                url: “{controller}/{action}/{id}”,
                defaults: new { controller = “Home”, action = “Index”, id = UrlParameter.Optional }
            );
        }
    }
}
Attribute Routes are added to the controller. In this case the MoviesController.cs file.
Example Attribute Route:
//Attribute Routes
[Route(“moives/released/{year}/{month:regex(\\d{2}):range(1, 12)}”)]
public ActionResult ByReleaseYear(int year, int month)
{
    return Content(year + “/” + month);

}

In the example above, the month has a regex applied to it to match a pattern.
Will accept  2 digits  \\d{2} and between a range of 1 to 12
There are other constraints we can apply that are supported by the framework

  • Min
  • Max
  • Minlength
  • Maxlength
  • Int
  • Float
  • Guid

Google the term, “ASP.NET MVC Attribute Route Constraints” and you’ll find lots of online resources to help you.

Adventures in Web Development: How to Build a Simple Cloud Hosted Web Server using Ubuntu and GOLang

Sometimes you want to learn something new but you don’t know where to start.

I’ve been working in the Microsoft world & IIS stack so long, I forget how many other web stacks are out there and how many have past us by. OMG!

I wanted to challenge myself to learn how to setup a Cloud hosted, Linux based, web server to host a website. 

Where do I start? Well, I just started looking stuff up and decided to blog my learning process along the way.

I hope this helps somebody else with the same desire to learn this topic as quickly and painlessly as possible.

Here we go…. 

First off, I was not sure what programming language I wanted to use.

This project will use a Linode cloud hosted server and Ubuntu as our operating system.

I started off thinking about a web server using a LAMP stack. I thought it would be OK to consider other options too.

I hate to assume people know tech jargon so I will stop and explain things as best I can along the way. My intent is to inform, not to annoy.

That being said, what is a stack? 

A stack is just a term used to describe a collection of all the moving parts required to have a modern functional website. Includes server operating system, web server software, a database and a framework/language.

When we hear the term, “Full Stack Developer”, someone is talking about a developer with experience in all of the aspects of the stack.
For example,a LAMP stack is Linux OS + Apache Web Server + MySQL Database + PHP language/framework. 

Other server options are replacing Apache with NginX (Pronounced engine x) or building our own using the GO programming language (GoLang). 

I started off with Apache then went to NginX.

I got NginX to work as a server so I’m leaving that information in this article at the bottom.

The rest of this article is focused on trying to get a very basic Linux Ubuntu + GOLang stack up and running.  No database.

If you and I can get a server up and running, I’m going to celebrate then come back later and add the database.

Let’s do this!

What you need before you start:
If you’re using Windows to connect to your Linux server, use Putty to connect via SSH. Details are in the video. Get Putty here.

SSH is a client/server program that enables secure connection to the SSH server on a remote machine. The SSH command is used for remote execution of login, file transfer between machines and executing other remote commands.
Step 1. Go to https://www.Linode.com and create an account. You will need a credit card. A web server can be run on Linode for about .50 a day so learning can be inexpensive. You can delete the server anytime and just rebuild it in about 5 minutes.

Once you have your account, watch and follow along with this video.

Video: Setting up You Linode Ubuntu Server

Command line commands:
– sudo apt-get update
– sudo apt-get upgrade
– nano /etc/hosts

2. Once, You’ve installed your server. You should take some time to secure it. Watch this video and follow along.

Video: Securing Your Linode Server

Command line commands:
– adduser example_user
– adduser example_user sudo
– ssh-keygen -b 4096 

Installing GO and Use It to Setup a Super Simple Web Server

I tried several times and failed to get Apache running quickly so I moved on to NginX and GOLang since this was supposed to be easy.
Use the two videos above to walk you thru building your server operating system and securing it.  
Then go to https://www.linode.com/docs/development/go/install-go-on-ubuntu/ to get the instruction on how to install GO on Ubuntu.
Then watch and follow along with this video to see how to write your own web server and web application using GO.  It has all the moving parts built in, you won’t believe how easy it is. Give it a try!

It may be working when you click. Here is a simple page up and running.

http://74.207.244.122:8000/about/

 I 

**** Learning Note ****

This is where I’ll stop and relate some Microsoft stack items to what we just did.

When we build sites on the Microsoft stack, we already have a server, Internet Information Services (IIS), it runs on Windows as a background service.  If we’re having issues or make an update to the web.config file, we restart the IIS service.

So we just built a super simple IIS web server using a GO net/http library but we have not learned how to run in in the background as a service on Ubuntu. 

**** Learning Note ****

Once you get your simple server working, you will realize it is only running when you run it at the command line.  If you exit, your server stops.

We can cheat a bit by using the Linux command nohup which is short for “No Hangups”.

We don’t run nohup by itself, it is a supplemental command that tells Linux not to stop the main command, even if the user logs out. 

I won’t cover nohup in detail here I recommend this resource for learning more about the nohup command

We can fool our friends in to thinking we’ve built our server by launching our GO app using nohup but this is not the right way to do this. It needs to be running as a background service (daemon).

You will want to learn how to make your GO server run in the background all the time and restart automatically if the server restarts.

I was stuck on this part for a day or so but thank goodness I know the founder of chapbook.com, who is using GOLang so I hit them up and was  pointed to the link below.

https://fabianlee.org/2017/05/21/golang-running-a-go-binary-as-a-systemd-service-on-ubuntu-16-04/

This link is a good example of how to setup a background service called a daemon in Linux terms. 

We’ll do the example on the site above as an exercise to get some daemon code working before we move on to the next task of making our own server code run as a daemon.

It took me a while to get it working but I did. Took longer than I expected as I’m not used to doing everything remotely via SSH and with the nano text editor.  Feels like bootcamp.

Here is a screenshot of my successful attempt.

Installing NginX Web Server on Ubuntu

This is an older part of blog where I was going to go the NginX route and had a simple working server up. I’ll come back to update this later.

I started trying to do this using a LAMP stack but had too many problems getting the Apache2 web server to work and was getting tired and frustrated.

After staying up until 0230 failing to get the Apache2 server installed on Ubuntu, exhaustion was setting in and I was feeling the need a confidence boost so I started my Linode server over from scratch and tried this video on getting NginX web server installed and had success. Use this video first, it was much easier.

Video: Setting up a Linode Server and Hosting a Website using NginX Web Server

As next step, I followed these instructions to install the GO language and get a hello world app to work.

Next we figure out how to use GO to handle business logic on the web server.

How to Do a Basic 802.11 Wireless DeAuth Attack using Kali Linux to Disrupt Wireless Device Connections

I’ve been working in IT now for over 20 years and spend a percentage of my professional development time on InfoSec and IT Security related items as I feel its important to know how to use technology but also how criminals use the same technology to do bad things.

This article will be focusing on how to do a very basic wireless “DeAuth” Attack.

You can check out the Wikipedia description of a DeAuth Attack.
A “DeAuth” attack is considered a denial of service attack.  Service will be denied to WiFi devices connected and listening to messages from a specific SSID where a “man in the middle” spoofs the SSID and transmits a message to all devices to disconnect from the WiFi network.

DeAuth are often part of a larger attack like those used to force clients to connect to an “Evil twin access point” where network packets can be captured.

Some WiFi password attacks on WPA & WPA2 use brute force techniques along with DeAuth attacks to force a device offline then sniff out the WAP 4-way handshake when it reconnects.

Other password attacks are phishing in style as they also start with a DeAuth attack but then use a man-in-the-middle to collect passwords supplied by an unwitting user. 

So how is this done?

First, the bad guy needs look around for a target.

Next, they turn their WiFi receiver in to a WiFi Transmitter.  With the WiFi transmitter, they spoof the targets wireless SSID in broadcast mode. 

Attacker sends out a broadcast using the spoofed SSID and transmits a “DeAuth” frame telling all the devices connected to the spoofed WiFi SSID to disconnect immediately.

**** IT IS ILLEGAL TO HACK OUTSIDE YOUR SANDBOX ****
**** DON’T DO IT & DON’T TALK ABOUT WHAT YOU DO ****

**** THIS INFORMATION IS FOR EDUCATIONAL PURPOSES ONLY ****

The information, I share below was gathered from publicly available resources and is intended as important and educational in the field of InfoSec.

I won’t cover what Kali Linux is or how to install it. I will provide some helpful links below.

This article will focus on how to use the tools in Kali Linux to go through the process of target selection and spoofing of WiFi SSID in order to launch a “DeAuth” attack denying connection to a specific WiFi signal for a period of time.

Razor Views in C# MVC – C# Code and HTML Coexisting Together

Razor Views in C# MVC

How C# Code and HTML Coexist Together

Tonight’s study topic is Razor Views in C# MVC 5.

Here are two good resources for this topic.

Paid access to Mosh Hamedani’s Complete ASP.net MVC 5 Course. This is covered in video 16 of his course.

Also helpful, was this YouTube video that is part of a larger MVC tutorial that I really like and refer to often

VIDEO: Razor View Syntax

What are Razor Views and What Do They Do?

Specific to C# MVC, Razor Views are code snippets with special syntax made up of C# code and HTML/CSS. The C# logic can interact with and output HTML and CSS elements/attributes dynamically.

With Razor Views, we use the @ symbol to switch between C# code and HTML. 

A simple example of printing numbers from 1 to 10 using Razor.

@for {int i = 1; i <=10; i++)
{
 <b>@i</b>
}

The Output would be: 1 2 3 4 5 6 7 8 9 10
Inside the brackets, Razor sees the <b>@i</b> and knows to render the C# variable i when its proceeded with an @ character then Razor sees the angle brackets and switches back in to HTML parsing mode.

If we didn’t want to use HTML we could change the <b> tag to <text> and output would just be text without the HTML.

The most important thing to understand and remember about Razor Views is the context switching in the parser is based on detection of specific characters.

@ character starts the C# parser but HTML & text won’t parse until Razor see a tag wrapped in angle brackets. We are just switching back and forth between to parsing modes. Make sense?

The loop is C# code but the output is HTML.
In ASP Classic and .Net, the Response Object would handle the HTML output to the browser from inside the loop.

Here are two more examples to help us cement the idea in our brains.

1. A simple date:
@{
   int day = 24;
   int month = 08;
   int year = 2020;
}
Date is @day-@month-@year
Output = Date is 24-08-2020


2. Loop thru images in folder
@for (int i = 1; i <= 5; i++)
{
  <img src="~/Images/@(i).png" />
}

Notice how in the sample above, we put the variable inside the parenthesis. Why, when we didn’t do this for the date example above?

Because if we don’t C# will try and read i. and an object with a property so we have to wrap it in parenthesis. This tell the Razor syntax that we are just trying to concatenate the values.

Razor View Code Blocks

In Razor Views we define code blocks using @{}.

@{
 int SumOfEvenNumbers = 0;
 int SumOfOddNumbers = 0;

 for(int i=1; i<=10; i++)
 {
  if(i %2==0)
  {
    SumOfEvenNumbers = SumOfEvenNumber + 1;
  }
  else
  {
    SumOfOddNumbers = SumOfOddNumbers + 1;
  }
 }
}

<h3>Sum of Even Numbers = @SumOfEvenNumbers</h3>
<h3>Sum of Odd Numbers = @SumOfOddNumbers</h3>

Razor View Comments

Razor View multi-line code comments are very similar to JavaScript and CSS that use the asterisk and forward slash, /*  */, to wrap comments.

Razor View multi-line comments use ampersand and asterisk in same way. @* to start a multi-line comment and *@ to end it. (See code example below)

What’s in the Razor View Example Below?

H2 tag class name is dynamically selected based on  logic, if Model.Customers.Count is greater than 5 then change the CSS class of the H2 element to “popular”.

Also, inside the <ul> tags, Razor View code loops through the list of customers and outputs the name.  

Sample C# Razor Code Example:

@model  Vidly.ViewModels.RandomMovieViewModel
@{
    ViewBag.Title = "Random";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
 
@*
    This is a comment
    on multiple lines
*@
 
@{
    var className = Model.Customers.Count > 5? "popular": null;
}
 
<h2 class="@className">@Model.Movie.Name</h2>
 
@if (Model.Customers.Count == 0)
{
    <text>No one has rented the movie before.</text>
}
 
else 
{
    <ul>
        @foreach (var cusomter in Model.Customers)
        {
            <li>@cusomter.Name</li>
        }
     </ul>
}

Using Ghostscript to Extract Text from a PDF file (Command Line)

This is a re-post from one of my favorite articles that I originally posted on 7/23/2018 on my old Blogger blog.

I think I would really like to revisit automating the extraction of text from PDF files. There is a lot of untapped value many companies could be leveraging but aren’t.

Recently, I received a request from a team member to find a way to:

  1. Extract a large amount of text from a large PDF file. 
  2. Once I get the text out I’ll need to parse and get specific elements in to an excel file.
  3. Format the Excel file in to specific tabs for each type of report I extract and add column headers
  4. Create validation code where I connect to a data warehouse using an Ajax web service and Ajax call in the Excel macro to validate the data based on an ID in one of the columns 

Pretty cool right? Just finished the prototype today! 7/31/2018.

In this article I’ll be covering the first step of this task where I use a free tool called Ghostscript to extract text from a PDF file. 

What is Ghostscript?

Ghostscript is a high-performance Postscript and PDF interpreter and rendering engine with the most comprehensive set of page description languages (PDL’s) on the market today and technology conversion capabilities covering PDF, PostScript, PCL and XPS languages.
Ghostscript has been under active development for over 20 years, and offers an extremely versatile feature set and can be deployed across a wide range of platforms, modules, end uses (embedding in hardware, as an engine in document management systems, providing cloud solution integration and as an engine in leading PDF generators and tools).

How to extract text from a PDF using GhostScript

Please note that the PDF file must be formatted correctly (text not image only).

Steps:
– Download Ghostscript
– Install Ghostscript
– Copy your pdf file to the bin directory where you installed Ghostscript
– Open a command line window at the bin directory (as Administrator if you get access error when running).
 – Sample Command: gswin64 -sDEVICE=txtwrite -o[Output File Name] [Input File Name]
– Sample ghostscript command: gswin64 -sDEVICE=txtwrite -ooutput2.txt test.pdf


Please leave a comment if this article helped you.

How to Use Windows Snipping Tool to Capture Right Click Context Menus

I’m working on a huge documentation project where I’m documenting operational support for a suite of C# MVC portal sites with a lot of back end SQL administrative functions.

I used to have SnagIt but my company has been cutting back on licenses.

I’m forced to rely on the Windows native screenshot tool, the Windows Snipping Tool.

One of my first big struggles was how do I capture right click context menus with the Windows Snipping Tool.

In my case, I’m documenting a folder structure and how to commit code to a SVN repository.

  1. Open Snipping Tool, cancel current snippet and leave in standby mode. 
  2. Get focus on your window / folder.
  3. Keys: Shift + F10
  4. Keys: Ctrl + fn + Print Screen (prtsc)
  5. You should have right click menu open and Snipping Tool should prompt you to select an area to capture. Select your menu area.

Copying Files to Windows from Linux Using Putty’s PSCP Command Line Tool

I’m wanting to look at my cloud hosted Linux Apache web server log files on my local Windows PC and I’m using PuTTY SSH as my Windows SSH telnet client.

How do we transfer the Apache web server log files over to my local Windows PC so I can work with it?

Instead of trying to copy the file from inside the PuTTY SSH session on the remote server, we can use a built-In Windows command line tool (pscp.exe) included with PuTTY called “PSCP” which mimic much of what you get with Linux’s built-in scp file transfer utility.

Putty’s Command Line File Transfer Tool (PSCP)

Below is a screenshot of me figuring out how to properly transfer an Apache web server log file over to my Windows PC temp folder on the C drive.

Sample Command Line Code

Run from the Windows Command Line from your Putty Install folder.

I used // to escape the forward slashes on the Linux server and \\ to escape backslahes and it worked.

Below should be entered all on one line.
c:\Program Files\PuTTY>pscp user@remoteserver://var//log//apache2//access.log c:\\temp\\access.log

Re-post: C# Programming – ASCII XMas Tree w/ Code Example

This is re-post from an article I originally posted 10 years ago on 11/18/2009. I still see a bit of traffic from it so it stays on the blog.

I’m guessing the web traffic for this article is coming from programming students who have received ASCII XMas Tree as a coding project.

The original title was: Introduction to C# Programming: ASCII XMas Tree – C# and .Net Sample Project.

ORIGINAL ARTICLE

C#, pronounced C Sharp, is a relatively new programming language from Microsoft that was designed to take full advantage of the new .Net Framework.

Below is a sample project I did for a programming class I’m attending at MJC in Modesto. To test and run the code listed on this site you should download a free copy of Microsoft Visual C# Express 2005. There are four Visual Studio Express versions available for free from Microsoft, Visual Basic, C#, J# and C++.

The xmas tree project was a good idea. It really makes you think. I think this project could have been a little more fun for me if I had a little more time but my schedule just won’t allow it. I don’t see myself coming back to often to update this code but if others want to sent me some other samples I may post them later. So here it goes….

Screenshot of command line window.

C# ASCII XMas Tree Code Sample

using System;
using System.Collections.Generic;
using System.Text;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //create the main array
            int[] myArray = new int[] { 1, 3, 5, 7, 9 };

            //The outside foreach loop to loop throught the array
            foreach (int intLoop in myArray)
            {
                //creates the spaces, takes the array number minus 1 then divide by 2
                //this gives you the amount of spaces needed for each level of the tree
                 for (int iSpace = 0; iSpace < ((myArray[4]-intLoop)/2); iSpace++)
                {
                    System.Console.Write(" ");
                }               
                //middle loop writes the asterisks "*" the full amount of current array[]

                for (int i = 0;i < intLoop; i++)
                {               
                System.Console.Write("*");
             }
                //creates the spaces, takes the array number minus 1 then divide by 2
                //this gives you the amount of spaces needed for each level of the tree
              for (int iSpace = 0; iSpace < ((myArray[4] - intLoop) / 2); iSpace++)
             {
                 System.Console.Write(" ");
             }

            //creates new lines after all 3 loops run

             System.Console.WriteLine("");
            }
            //nest this loop and do it 3 times
            for (int iBase = 0; iBase < myArray[1]; iBase++)
            {
                // now make the base of the tree
                for (int iSpaces = 0; iSpaces < myArray[1]; iSpaces++)
                {
                    System.Console.Write(" ");
                }
                for (int iPipes = 0; iPipes < myArray[1]; iPipes++)
                {
                    System.Console.Write("|");
                }
                // now make the base of the tree
                for (int iSpaces = 0; iSpaces < myArray[1]; iSpaces++)
                {
                    System.Console.Write(" ");
                }
                  //creates new lines after all 3 loops run
                    System.Console.WriteLine("");
            }
        }
    }
}