Tag Archives: windows

Powershell Mysql helper functions

Some Powershell functions for Preparing Mysql Statements and executing them.

Requires the MySQL .NET Connector

$Dbserv = "server"
$Dbname = "db"
$Dbuser = "user"
$Dbpass = "pass"

#MySQL Database connection
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") | Out-Null
$SQLConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$SQLConnection.ConnectionString = "server=$Dbserv;port=3306;uid=$Dbuser;pwd=$Dbpass;database=$Dbname;Integrated Security=False"

function MySQLprepare {
	[OutputType([MySql.Data.MySqlClient.MySqlCommand])]
	param (
		# Query
		[Parameter(
			Mandatory = $true
		)]
		[String]
		$query,

		# Connection
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlConnection]
		$connection
	)

	$command = New-Object MySql.Data.MySqlClient.MySqlCommand
	$command.Connection = $connection
	$command.CommandText = $query

	(Select-String "@\w+" -input $query -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters.AddWithValue($_, "") }
	} | Out-Null

	$command.Prepare()

	return , $command
}

function MySQLexec {
	[OutputType([Int])]
	param (
		# QueryParams
		[Parameter(
			Mandatory = $false
		)]
		[hashtable]
		$queryParams = @{},

		# Command
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlCommand]
		$command
	)

	(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
	} | Out-Null

	$affectedRows = $command.ExecuteNonQuery()

	return $affectedRows
}

function MySQLquery {
	[OutputType([System.Data.DataTable])]
	param (
		# QueryParams
		[Parameter(
			Mandatory = $false
		)]
		[hashtable]
		$queryParams = @{},

		# Command
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlCommand]
		$command
	)

	(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
	} | Out-Null

	$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
	$dataSet = New-Object System.Data.DataSet
	$dataAdapter.Fill($dataSet, "data") | Out-Null
	return , $dataSet.Tables["data"]
}

Examples

Preparing some statements

$insertVM = $SQLConnection | MySQLprepare -query "INSERT INTO virtual_machines ( id, name, description, status ) VALUES
( @id, @name, @description, 'new' );"
$selectVMs = $SQLConnection | MySQLprepare -query "SELECT * FROM virtual_machines WHERE status LIKE @status;"
$updateVM = $SQLConnection | MySQLprepare -query "UPDATE virtual_machines SET status = @status WHERE id = @id;"

Executing them

$insertVM | MySQLexec -queryParams @{"@id" = 1; "@name" = 'test'; "@description" = 'example vm'} | Out-Null

$dtVMs = $selectVMs | MySQLquery -queryParams @{"@status" = 'new'}
foreach ($vm in $dtVMs.Rows) {
	Write-Host "Working on VM" $vm["id"] -ForegroundColor Cyan

	$updateVM | MySQLexec -queryParams @{"@id" = $vm["id"]; "@status" = 'ready'} | Out-Null
}

UTF encoding

Code point range UTF-8 UTF-16
hexadecimal binary Bytes
(code bits)
binary hexadecimal Bytes
(code bits)
binary hexadecimal
00 00 00
00 00 7F
0000 0000 0000 0000 0000 0000
0000 0000 0000 0000 0111 1111
1
(7)
0xxx xxxx 00
7F
2
(16)
xxxx xxxx xxxx xxxx 00 00
FF FF
00 00 80
00 07 FF
0000 0000 0000 0000 1000 0000
0000 0000 0000 0111 1111 1111
2
(11)
110x xxxx
10xx xxxx
C2 80
DF BF
00 08 00
00 FF FF
0000 0000 0000 1000 0000 0000
0000 0000 1111 1111 1111 1111
3
(16)
1110 xxxx
10xx xxxx
10xx xxxx
E0 A0 80
EF BF BF
01 00 00
10 FF FF
0000 0001 0000 0000 0000 0000
0001 0000 1111 1111 1111 1111
4
(21)
1111 0xxx
10xx xxxx
10xx xxxx
10xx xxxx
F0 90 80 80
F4 8F BF BF
4
(20)
1101 10xx xxxx xxxx
1101 11xx xxxx xxxx
D8 00 DC 00
DB FF DF FF

Test Jumbo Frames / MTU 9000

Linux:

ping -M do -s 8972 xxx.xxx.xxx.xxx

OSX/BSD:

ping -D -s 8184 xxx.xxx.xxx.xxx

Windows:

ping -f -l 9000 xxx.xxx.xxx.xxx

Example results on Linux

If you’ve forgotten to enable jumbo frames/9k MTU on your client device you’re sending the ping from:

PING xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx): 8184 data bytes
ping: sendto: Message too long

If you have enabled jumbo frames on your client but not the destination (or a switch in between):

PING xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx): 8184 data bytes
Request timeout for icmp_seq 0

If you’ve done everything righ:

PING xxx.xxx.xxx.xxx (xxx.xxx.xxx.xxx): 8184 data bytes
8192 bytes from xxx.xxx.xxx.xxx: icmp_seq=0 ttl=128 time=0.714 ms

How to insert hotfixes and drivers and then create a bootable Windows Setup DVD

  • Create local folders c:\temp\src, c:\temp\mount, c:\temp\winremount, c:\temp\hotfix, and c:\temp\drivers.
  • Copy the setup sources from the DVD or a mounted ISO to C:\temp\src.
  • Copy the hotfix (.msu or .cab files) to C:\temp\hotfix.
  • Copy the driver files to c:\temp\drivers.
  • Run Command Prompt as an administrative.
dism /Mount-Wim /WimFile:c:\temp\src\sources\boot.wim /Index:1 /MountDir:c:\temp\mount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
dism /Unmount-Wim /MountDir:C:\temp\mount /Commit
dism /Mount-Wim /WimFile:c:\temp\src\sources\boot.wim /Index:2 /MountDir:c:\temp\mount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
  • Manually sort the folder C:\temp\mount\sources by date, and then copy the updated files to c:\temp\src\sources.
dism /Unmount-Wim /MountDir:C:\temp\mount /commit
  • Obtain the index from the Install.wim information by running the following command, and then check every index to see how many indexes have to be updated.
dism /Get-WimInfo /WimFile:c:\temp\src\sources\install.wim
  • Insert the hotfixes and drivers to install.wim and winre.wim by running the following commands:
dism /Mount-Wim /WimFile:c:\temp\src\sources\install.wim /Index:1 /MountDir:c:\temp\mount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
dism /Mount-Wim /WimFile:c:\temp\mount\windows\system32\recovery\winre.wim /Index:1 /MountDir:c:\temp\winremount
dism /Image:C:\temp\mount /Add-Package /PackagePath:c:\temp\hotfix
dism /Image:C:\temp\mount /Add-Driver /Driver:c:\temp\drivers /Recurse
dism /Unmount-Wim /MountDir:C:\temp\winremount /Commit
dism /Unmount-Wim /MountDir:C:\temp\mount /Commit

Note If there are multiple indexes in step 8, update them one by one.

Create an ISO file by running the following oscdimg commands.

Install the latest Windows ADK for Windows 8.1 update.

  • For Legacy BIOS Boot mode:
oscdimg -LTEST -m -u2 -bC:\temp\src\boot\etfsboot.com C:\temp\src C:\temp\Win7.ISO
  • For Legacy and UEFI BIOS multiple Boot mode:
oscdimg -LTEST -m -u2 -bootdata:2#p0,e,bC:\temp\src\boot\etfsboot.com#pEF,e,bC:\temp\src\efi\microsoft\boot\efisys.bin C:\temp\src C:\temp\Win7.ISO

https://support.microsoft.com/en-au/help/2990941/update-to-add-native-driver-support-in-nvm-express-in-windows-7-and-windows-server-2008-r2

DNS Fun

calc

A calculator supporting addition, substraction, multiplication and division (respectively : ADD, SUB ,MUL, and DIV operations). More information here.

dig 2.8.add.calc.postel.org @dns.postel.org +short
0.10.0.0
dig 8.2.sub.calc.postel.org @dns.postel.org +short
0.6.0.0
dig 2.8.mul.calc.postel.org @dns.postel.org +short
0.16.0.0
dig 8.2.div.calc.postel.org @dns.postel.org +short
0.4.0.0

IP to ASN Mapping

A service providing a way to map IPv4 and IPv6 addresses or prefixs to ASNs, get information about ASNs, and even find possible peers. More details here.

Mapping an IP address or prefix to a corresponding BGP Origin ASN:

dig 4.4.8.8.origin.asn.cymru.com TXT +short
"15169 | 8.8.4.0/24 | US | arin |"
dig 4.8.8.origin.asn.cymru.com TXT +short
"15169 | 8.8.4.0/24 | US | arin |"
dig 4.4.8.8.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.6.8.4.0.6.8.4.1.0.0.2.origin6.asn.cymru.com TXT +short
"15169 | 2001:4860::/32 | US | arin | 2005-03-14"

Getting information about a given ASN:

dig AS15169.asn.cymru.com TXT +short
"15169 | US | arin | 2000-03-30 | GOOGLE - Google Inc.,US"

fun

dig any google-public-dns-a.google.com +noall +answer
; <<>> DiG 9.8.3-P1 <<>> any google-public-dns-a.google.com +noall +answer
;; global options: +cmd
google-public-dns-a.google.com.	38271 IN TXT	"http://xkcd.com/1361/"
google-public-dns-a.google.com.	38271 IN AAAA	2001:4860:4860::8888
google-public-dns-a.google.com.	38271 IN A	8.8.8.8

more stuff at http://www.cambus.net/interesting-dns-hacks/