Tuesday, November 18, 2014

The datepart second is not supported by date function dateadd for data type date.

In Epicor 10, I was trying to create some SQL to combine the values from the SysDate and SysTime fields of table 'erp.PartTran'. Field 'Sysdate' is of type 'date'. Field 'SysTime' is of type 'int' and is the number of Seconds in to the day, and so will have a maximum values of 86400. So I tried the function:
DATEADD(SECOND,SysTime,SysDate)

But I got the error:
Msg 9810, Level 16, State 1, Line 4
The datepart second is not supported by date function dateadd for data type date.

The fix is to cast the field 'SysDate' to type 'datetime', like this:
DATEADD(SECOND,SysTime,Convert(DateTime,SysDate))

Here is my complete SQL:
SELECT DATEADD(SECOND,SysTime,Convert(DateTime,SysDate)) SystemDate,
TranNum, PartNum, WareHouseCode, BinNum, TranClass, TranType, InventoryTrans, TranQty, UM,MtlUnitCost, ExtCost, CostMethod,
EntryPerson, TranReference, InvAdjSrc, InvAdjReason, LotNum, GLTrans, PostedToGL, MtlMtlUnitCost, EmpID, CostID, ActTranQty, ActTransUOM, BaseCostMethod
FROM erp.PartTran
WHERE Company='ABC' AND PartNum='XYZ'
ORDER BY TranNum;

Reference: What about a new DATEADD function?

Wednesday, November 27, 2013

Microsoft Access - Error 3088 "Too many expressions in ORDER BY clause"

In Access 2010, the maximum number (limit) of fields specified in the 'Order By' clause, seems to be 128. Should be enough for most cases!

Wednesday, October 24, 2012

Using tshark to troubleshoot http

Here I give an example of how to use tshark, which is a command line tool and a part of the wireshark package, to monitor http requests and responses.

On Windows, first identify the numbering of the NIC adapters, using the '-D' option:

C:\Program Files\Wireshark>tshark.exe -D
1. \Device\NPF_GenericDialupAdapter (Adapter for generic dialup and VPN capture)
2. \Device\NPF_{2F15B435-C783-4863-90AC-C9DF8B64FBE1} (VMware Accelerated AMD PCNet Adapter)

Ok, lets now look at http traffic when loading the twitter home page:
C:\Program Files\Wireshark>tshark -i 2 "tcp port 80" -R "http.request or http.response"
Capturing on VMware Accelerated AMD PCNet Adapter
0.005726 192.168.2.14 -> 199.59.150.39 HTTP 867 GET / HTTP/1.1
3.067846 192.168.2.14 -> 199.59.150.39 HTTP 867 [TCP Retransmission] GET / HTTP/1.1
4.577303 192.168.2.14 -> 184.169.78.33 HTTP 425 GET /a/1350941601/t1/css/t1_core_logged_out.bundle.css HTTP/1.1
4.578931 192.168.2.14 -> 184.169.78.33 HTTP 414 GET /a/1350941601/t1/css/t1_more.bundle.css HTTP/1.1
4.605371 199.59.150.39 -> 192.168.2.14 HTTP 279 HTTP/1.0 200 OK (text/html)
4.612847 184.169.78.33 -> 192.168.2.14 HTTP 615 HTTP/1.0 200 OK (text/html)
4.620900 184.169.78.33 -> 192.168.2.14 HTTP 593 HTTP/1.0 200 OK (text/html)
4.631572 192.168.2.14 -> 184.169.78.33 HTTP 414 GET /a/1350941601/images/bigger_spinner.gif HTTP/1.1
4.631683 192.168.2.14 -> 184.169.78.33 HTTP 426 GET /a/1350941601/t1/img/front_page/city-balcony@2x.jpg HTTP/1.1
4.634542 192.168.2.14 -> 184.169.78.33 HTTP 432 GET /c/swift/init.ef644480fe2a53578f7ce4eda13396e86b6fa74a.js HTTP/1.1
4.679344 184.169.78.33 -> 192.168.2.14 HTTP 60 HTTP/1.0 200 OK (GIF89a)
4.679459 184.169.78.33 -> 192.168.2.14 HTTP 60 HTTP/1.0 200 OK (GIF89a)
4.683034 184.169.78.33 -> 192.168.2.14 HTTP 629 HTTP/1.0 200 OK (text/html)
4.702673 192.168.2.14 -> 199.59.150.39 HTTP 529 GET /opensearch.xml HTTP/1.1
4.705784 199.59.150.39 -> 192.168.2.14 HTTP 417 HTTP/1.0 304 Not Modified
15 packets captured

You can see above, that this clearly shows the URLs to the html, image and css files that are downloaded from the server, and the associated http response codes.

Note the use of the tshark '-R' option to specify a "display" filter, to filter out everything, but the http requests and responses, so that we can concentrate on whats happening at the http level, without things like the tcp acks causing distraction. And you may want to expand the capture filter to just specify a particular host.

Customizing grub2 boot options in Fedora 18

In days of old, with RedHat Linux, and Fedora and Centos, on servers I used to disable the default behaviour of console display blanking screen-saver by putting the 'setterm -blank 0' command into file '/etc/rc.local'. On recent versions of Fedora, you do not get a '/etc/rc.local' file, however you can create one like this:
# vi /etc/rc.d/rc.local
# chmod a+x /etc/rc.d/rc.local
# ln -s /etc/rc.d/rc.local /etc
# ll /etc/rc.local
lrwxrwxrwx. 1 root root 18 Sep 24 15:44 /etc/rc.local -> /etc/rc.d/rc.local
#

But this no longer seem to be an appropriate way to run  'setterm -blank 0', as the command just seems to be ignored. I tried setting my '/etc/rc.local' file to this:
# cat /etc/rc.local
#!/bin/sh
/bin/date > /root/rc.local.start
/bin/sleep 5
/bin/setterm -blank 0
/bin/date > /root/rc.local.done

..just to prove the command was executing, and it was, but still the screen blanked after the timeout.

After much Googling, I found the answer was to use a kernel command line option of 'consoleblank=0'.

Now again in days of old, I would just edit '/etc/grub.conf' to set these kernel parameters, But now recent versions of Fedora are using Grub2, things have changed. I found that you can specify the kernel parameters, in file '/etc/default/grub', using the 'GRUB_CMDLINE_LINUX_DEFAULT' option.

And while I was editing the grub file, I decided to change some other settings. I wanted to disable the graphical boot splash screen, and just have a basic 'text' screen to select which kernel to boot, so I added 'GRUB_TERMINAL=console'. I also removed the 'rhgb quiet' options, as I like to see all those boot messages scrolling up the screen.

# cd /etc/default/
# cp grub grub.orig
# vi grub
# diff -u grub.orig grub
--- grub.orig   2012-10-24 10:28:28.120869824 +0100
+++ grub        2012-10-24 10:52:40.940184853 +0100
@@ -1,6 +1,8 @@
-GRUB_TIMEOUT=5
+GRUB_TIMEOUT=10
 GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
 GRUB_DEFAULT=saved
-GRUB_CMDLINE_LINUX="rd.md=0 rd.lvm=0 rd.dm=0 rd.luks=0 $([ -x /usr/sbin/rhcrashkernel-param ] && /usr/sbin/rhcrashkernel-param || :) rhgb quiet"
+GRUB_CMDLINE_LINUX="rd.md=0 rd.lvm=0 rd.dm=0 rd.luks=0 $([ -x /usr/sbin/rhcrashkernel-param ] && /usr/sbin/rhcrashkernel-param || :)"
+GRUB_CMDLINE_LINUX_DEFAULT="consoleblank=0"
 GRUB_DISABLE_RECOVERY="true"
 GRUB_THEME="/boot/grub2/themes/system/theme.txt"
+GRUB_TERMINAL=console
#
# grub2-mkconfig -o /boot/grub2/grub.cfg
Generating grub.cfg ...
Found linux image: /boot/vmlinuz-3.6.2-2.fc18.x86_64
Found initrd image: /boot/initramfs-3.6.2-2.fc18.x86_64.img
Found linux image: /boot/vmlinuz-3.6.0-1.fc18.x86_64
Found initrd image: /boot/initramfs-3.6.0-1.fc18.x86_64.img
#

Ok, so lets check that worked....
# grep $'\tlinux' /boot/grub2/grub.cfg
linux   /vmlinuz-3.6.2-2.fc18.x86_64 root=UUID=9d81334c-ed70-4cc8-9279-e82eb8cdef1e ro rd.md=0 rd.lvm=0 rd.dm=0 rd.luks=0  consoleblank=0
linux   /vmlinuz-3.6.0-1.fc18.x86_64 root=UUID=9d81334c-ed70-4cc8-9279-e82eb8cdef1e ro rd.md=0 rd.lvm=0 rd.dm=0 rd.luks=0  consoleblank=0

In the above, note how I get grep to match for the tab character.

References:
http://lists.fedoraproject.org/pipermail/users/2012-March/415317.html
http://superuser.com/questions/152347/change-linux-console-screen-blanking-behavior
http://thangnguyennang.wordpress.com/2012/06/17/change-the-grub-menu-timeout-on-fedora-17-20-2/
http://ubuntuforums.org/showthread.php?t=1456104
http://www.dedoimedo.com/computers/grub-2.htm
http://fedoraproject.org/wiki/GRUB_2
http://www.redhat.com/archives/rhl-list/2004-May/msg07775.html
http://savannah.gnu.org/bugs/?23535
http://www.howtoarena.com/how-to-disable-linux-console-screen-blank-out-feature/

Thursday, July 12, 2012

Centos vlan configuration

On our Centos boxes, we start with a minimal installation, and then add additional rpm packages as required. Today, we wanted to add a vlan trunk interface on an existing box. We created the config file as follows:
# cat /etc/sysconfig/network-scripts/ifcfg-eth5.800
# Intel Corporation 82546GB Gigabit Ethernet Controller (Copper)
DEVICE=eth5.800
BOOTPROTO=static
HWADDR=00:1B:21:52:09:0F
ONBOOT=yes
IPADDR=192.168.51.193
NETMASK=255.255.255.192
VLAN=yes
#

But when we tried to bring up the vlan interface, we got this error:
# ifup eth5.800
Device eth5.800 does not seem to be present, delaying initialization.
#

This was caused because the 'vconfig' program was missing, and so we had to install that package:
# which vconfig
/usr/bin/which: no vconfig in (/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)
# yum install vconfig
# which vconfig
/sbin/vconfig
#

Then everything worked as expected:
# ifup eth5.800
Added VLAN with VID == 800 to IF -:eth5:-
# ls -l /proc/net/vlan
-rw------- 1 root root 0 Jul 12 10:35 config
-rw------- 1 root root 0 Jul 12 10:35 eth5.800
# lsmod | grep 8021q
8021q 57425 0
#

BTW, the above commands were run on the following version of Centos:
# cat /etc/redhat-release
CentOS release 5.8 (Final)
#

Thursday, July 05, 2012

AES Encryption & Decryption from the command line with OpenSSL

Today, I wanted to gain a deeper understanding of AES encryption. I have succesfully used AES encryption with ipsec vpn's. But here I want to examine what are the inputs and outputs from the AES algorithm, and do some sanity checks with test data using openssl from the command-line.

On the wikipedia page for AES, they mention, "..Test vectors are a set of known ciphers for a given input and key. NIST distributes the reference of AES test vectors as AES Known Answer Test (KAT) Vectors (in ZIP format)."

That sounds like what I need as a reference, so I downloaded:

http://csrc.nist.gov/groups/STM/cavp/documents/aes/KAT_AES.zip

...and selected (guessed?) file "CBCVarKey128.rsp" as suitable, because I wanted to use a 128 bit key length,

Here is one example (from 128 possible examples) from that file:
KEY = 80000000000000000000000000000000
IV = 00000000000000000000000000000000
PLAINTEXT = 00000000000000000000000000000000
CIPHERTEXT = 0edd33d3c621e546455bd8ba1418bec8

Ok, first let's sanity check we can generate the required PLAINTEXT. I'm using xxd to do this, as I explained in an earlier blog post.

# echo -n '00000000000000000000000000000000' | xxd -p -r | hexdump -C
00000000 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00000010

So the above looks good. We are generating the 16 bytes of test data, from a 32 character hexadecimal string.

Openssl allow you to specify the Key and the 'Initialization Vector' on the command like, as hexadecimal strings, using the '-K' and '-iv' parameters. Be careful, thats an uppercase K for the key. As another sanity check, it is useful to use the '-P' option, again uppercase, to get openssl to report back what it thinks are the values of the Key and IV. So lets try this:

# echo -n '00000000000000000000000000000000' | xxd -p -r | openssl enc -aes-128-cbc -P -nosalt -K '80000000000000000000000000000000' -iv '00000000000000000000000000000000'
key=80000000000000000000000000000000
iv =00000000000000000000000000000000

Ok, thats good. They Key and IV values are what we were expecting.
By the way, for this sort of validation check, we don't want to use a salt, hence the use of the '-nosalt' option.
Right, lets remove the '-P' and see what data comes out:
# echo -n '00000000000000000000000000000000' | xxd -p -r | openssl enc -aes-128-cbc -nosalt -K '80000000000000000000000000000000' -iv '00000000000000000000000000000000' | hexdump -C
00000000 0e dd 33 d3 c6 21 e5 46 45 5b d8 ba 14 18 be c8 |.Ý3ÓÆ!åFE[غ..¾È|
00000010 fe 3d e6 e1 86 98 08 4f 63 de e5 04 42 ff 94 d2 |þ=æá...OcÞå.Bÿ.Ò|
00000020

Oh, that strange!
I'm trying to encrypt 16-bytes, but the output is 32 bytes long!
But the first 16 bytes of output looks correct!

The answer to this is padding. If you specify the '-nopad' option, then you get the expected 16 bytes of output:

echo -n '00000000000000000000000000000000' | xxd -p -r | openssl enc -aes-128-cbc -nopad -nosalt -K '80000000000000000000000000000000' -iv '00000000000000000000000000000000' | xxd -p
0edd33d3c621e546455bd8ba1418bec8

Good. Thats the CIPHERTEXT output we were expecting!

If you check 'man enc' you see that the '-nopad' option, disables standard block padding. And the man page also notes "All the block ciphers normally use PKCS#5 padding also known as standard block padding".

By the way, in the above tests, the IV is all-zeroes, so we can abbreviate the command like this:

echo -n '00000000000000000000000000000000' | xxd -p -r | openssl enc -aes-128-cbc -nopad -nosalt -K 80000000000000000000000000000000 -iv 0 | xxd -p
0edd33d3c621e546455bd8ba1418bec8

To finish off, lets try some decryption, reversing what we did above:
# echo -n '0edd33d3c621e546455bd8ba1418bec8' | xxd -p -r | openssl enc -aes-128-cbc -d -nosalt -K 80000000000000000000000000000000 -iv 0 -nopad | xxd -p
00000000000000000000000000000000

Ok, that looks good. I think I am begining to get the hang of this!

Using openssl to generate HMAC using a binary key

If you want to do a quick command-line generation of a HMAC, then the openssl command is useful.
For example:
# echo -n 'value' | openssl dgst -sha1 -hmac 'key'
(stdin)= 57443a4c052350a44638835d64fd66822f813319

# echo -n 'value' | openssl dgst -md5 -hmac 'key'
(stdin)= 01433efd5f16327ea4b31144572c67f6

...or alternatively...
# echo -n 'value' | openssl sha1 -hmac 'key'
(stdin)= 57443a4c052350a44638835d64fd66822f813319

# echo -n 'value' | openssl md5 -hmac 'key'
(stdin)= 01433efd5f16327ea4b31144572c67f6

Reference:
http://stackoverflow.com/questions/7285059/hmac-sha1-in-bash

BUT, note in the above commands, the 'value' and 'key' are ascii strings. The above syntax is problematic if you want to specify a Binary value for the key, which does not correspond to printable characters.

This had been a problem reported in the past:
http://www.mail-archive.com/openssl-users@openssl.org/msg49098.html
http://www.mail-archive.com/openssl-users@openssl.org/msg49100.html

But I can report here, that certainly with openssl v1.0.0, the following method allows you to specify a binary key, by passing it as a string of hex values.

To demonstate the point, let's get the hex string equivalent of the three character acsii string 'key', so that we can use the same hashes as in the examples above. To do this, I use utility 'xxd' which does a hexdump. (For further information on 'xdd' see my previous blog posts.)

# echo -n 'key' | xxd -p
6b6579

Ok, so the hex-string "6b6579" corresponds to ascii string "key".

So after reading up the man page for 'openssl dgst', we try a further alternate form of the command, like this:

# echo -n 'value' | openssl dgst -sha1 -mac HMAC -macopt key:key
(stdin)= 57443a4c052350a44638835d64fd66822f813319

# echo -n 'value' | openssl dgst -sha1 -mac HMAC -macopt hexkey:6b6579
(stdin)= 57443a4c052350a44638835d64fd66822f813319

# echo -n 'value' | openssl dgst -md5 -mac HMAC -macopt key:key
(stdin)= 01433efd5f16327ea4b31144572c67f6

# echo -n 'value' | openssl dgst -md5 -mac HMAC -macopt hexkey:6B6579
(stdin)= 01433efd5f16327ea4b31144572c67f6

Note the use of the '-macopt hexkey:string' option which allows you to specify the key in hexadecimal (two hex digits per byte).

Nice! So now we can do something like this:

# echo -n '55f80d132e8b68eb' | xxd -r -p | openssl dgst -sha1 -mac HMAC -macopt key:645a487340f4c7f0
(stdin)= 2f0928b4bb365b4a590d84960a7cd04fd2d80221

I hope to show a practical use of the above in a future blog post!

Finally, I will just confirm some details of the system that gave the above output:

# rpm -qa | grep openssl
openssl-1.0.0-20.el6_2.5.x86_64

# cat /etc/redhat-release
CentOS release 6.2 (Final)

By the way, if your wondering about the '(stdin)= ' that openssl is outputing, then see my previous blog post on this subject.