MySQL Query

Select

두 개의 테이블(또는 Self Table)에서 다른 값 찾기

TABLEA 테이블에서 키가 COMPARE_ID이고, F98 필드에 대해서 같은 테이블의 같은 키, 같은 필드의 필드값이 다른 것을 찾는 쿼리
(두 개의 테이블로 할 경우는 테이블 명과 where 조건절만 수정하면 된다.)
비교할 대상의 테이블 두 개를 UNION해서 같은 키 에 대해 정렬하고 그룹별 카운팅을 하면 2개씩 나와야 정상인데, 1개인 것이 바로 다른 부분이다.

SELECT COMPARE_ID, F98
FROM (
SELECT T1.COMPARE_ID, T1.F98 FROM TABLEA T1 WHERE T1.AP_ID='A08v25'
 UNION ALL SELECT T2.COMPARE_ID, T2.F98  FROM TABLEA T2 WHERE T2.AP_ID='A10v25'
 ) t
 GROUP BY COMPARE_ID, F98
 HAVING COUNT(*) = 1
 ORDER BY COMPARE_ID
 

Written with StackEdit.

JAVA RSA Test

RSA 알고리즘으로 JAVA에서 키쌍(공개키, 개인키)를 생성해 보고,
암복호화 및 서명 검증 테스트를 해보자.

package test_tryexcept;

import java.io.*;
import java.security.*;
import java.security.spec.*;
import javax.crypto.*;
import org.apache.commons.codec.binary.Base64;

public class RSATest {

	public static void main(String[] args) throws IllegalBlockSizeException, BadPaddingException {

		// Create Key Pair . (public key, private key)
		System.out.println("1. create key pair -----------------------------");
		PublicKey publicKey1 = null;
		PrivateKey privateKey1 = null;

		SecureRandom secureRandom = new SecureRandom();
		KeyPairGenerator keyPairGenerator;
		try {
			keyPairGenerator = KeyPairGenerator.getInstance("RSA");
			keyPairGenerator.initialize(512, secureRandom);

			KeyPair keyPair = keyPairGenerator.genKeyPair();
			publicKey1 = keyPair.getPublic();
			privateKey1 = keyPair.getPrivate();

			KeyFactory keyFactory1 = KeyFactory.getInstance("RSA");
			RSAPublicKeySpec rsaPublicKeySpec = keyFactory1.getKeySpec(publicKey1, RSAPublicKeySpec.class);
			RSAPrivateKeySpec rsaPrivateKeySpec = keyFactory1.getKeySpec(privateKey1, RSAPrivateKeySpec.class);
			System.out.println("Public  key modulus : " + rsaPublicKeySpec.getModulus());
			System.out.println("Public  key exponent: " + rsaPublicKeySpec.getPublicExponent());
			System.out.println("Private key modulus : " + rsaPrivateKeySpec.getModulus());
			System.out.println("Private key exponent: " + rsaPrivateKeySpec.getPrivateExponent());
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (InvalidKeySpecException e) {
			e.printStackTrace();
		}

		// 2. key save to file. (base64 encoding)
		System.out.println("\n2. save key file -----------------------------");
		byte[] bPublicKey1 = publicKey1.getEncoded();
		String sPublicKey1 = Base64.encodeBase64String(bPublicKey1);
		byte[] bPrivateKey1 = privateKey1.getEncoded();
		String sPrivateKey1 = Base64.encodeBase64String(bPrivateKey1);

		try {
			BufferedWriter bw1 = new BufferedWriter(new FileWriter("PublicKey.txt"));
			bw1.write(sPublicKey1);
			bw1.newLine();
			bw1.close();
			BufferedWriter bw2 = new BufferedWriter(new FileWriter("PrivateKey.txt"));
			bw2.write(sPrivateKey1);
			bw2.newLine();
			bw2.close();
			System.out.println("PublicKey.txt, PrivateKey.txt file saved.") ;
		} catch (IOException e) {
			e.printStackTrace();
		}

		// 3. load key file
		System.out.println("\n3. load key file -----------------------------");
		String sPublicKey2 = null;
		String sPrivateKey2 = null;

		BufferedReader brPublicKey = null;
		BufferedReader brPrivateKey = null;
		try {
			brPublicKey = new BufferedReader(new FileReader("PublicKey.txt"));
			sPublicKey2 = brPublicKey.readLine();
			brPrivateKey = new BufferedReader(new FileReader("PrivateKey.txt"));
			sPrivateKey2 = brPrivateKey.readLine();
			System.out.println("load PubilcKey.txt, PrivateKey.txt");
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (brPublicKey != null)
					brPublicKey.close();
				if (brPrivateKey != null)
					brPrivateKey.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		// key string to key data. 
		byte[] bPublicKey2 = Base64.decodeBase64(sPublicKey2.getBytes());
		PublicKey publicKey2 = null;
		byte[] bPrivateKey2 = Base64.decodeBase64(sPrivateKey2.getBytes());
		PrivateKey privateKey2 = null;
		try {
			KeyFactory keyFactory2 = KeyFactory.getInstance("RSA");
			X509EncodedKeySpec publicKeySpec = new X509EncodedKeySpec(bPublicKey2);
			publicKey2 = keyFactory2.generatePublic(publicKeySpec);
			PKCS8EncodedKeySpec privateKeySpec = new PKCS8EncodedKeySpec(bPrivateKey2);
			privateKey2 = keyFactory2.generatePrivate(privateKeySpec);
		} catch (NoSuchAlgorithmException | InvalidKeySpecException e) {
			e.printStackTrace();
		}

		// 4. encrypt test
		System.out.println("\n4. encrypt test -----------------------------");

		String sPlain1 = "This is an example.";
		String sPlain2 = null;
		try {
			Cipher cipher = Cipher.getInstance("RSA");
			System.out.println("input:"+ sPlain1);
			// 공개키 이용 암호화
			cipher.init(Cipher.ENCRYPT_MODE, publicKey2);
			byte[] bCipher1 = cipher.doFinal(sPlain1.getBytes());
			String sCipherBase64 = Base64.encodeBase64String(bCipher1);
			System.out.println("encrypt(pubkey):"+sCipherBase64);

			// 개인키 이용 복호화
			byte[] bCipher2 = Base64.decodeBase64(sCipherBase64.getBytes());
			cipher.init(Cipher.DECRYPT_MODE, privateKey2);
			byte[] bPlain2 = cipher.doFinal(bCipher2);
			sPlain2 = new String(bPlain2);
			System.out.println("decrypt(prikey):"+sPlain2);
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (IllegalBlockSizeException e) {
			e.printStackTrace();
		} catch (BadPaddingException e) {
			e.printStackTrace();
		}

		///////////////////////////////////////////////////////////////////////
		// 5. digital sign test
		System.out.println("\n5. digital sign test -----------------------------");
		try {
			Cipher cipher = Cipher.getInstance("RSA");
			
			// sign
			Signature rsa = Signature.getInstance("SHA1withRSA"); 
			rsa.initSign(privateKey2);
			rsa.update(sPlain1.getBytes());
			byte[] ds = rsa.sign();
			
			String dsBase64 = Base64.encodeBase64String(ds) ;
			System.out.println("signature:"+dsBase64);
			
			// verify by pubkey
			rsa.initVerify(publicKey2);
			rsa.update(sPlain1.getBytes());
			boolean bret = rsa.verify(ds);
			System.out.println("verify:"+bret);
			
		} catch (NoSuchAlgorithmException e) {
			e.printStackTrace();
		} catch (NoSuchPaddingException e) {
			e.printStackTrace();
		} catch (InvalidKeyException e) {
			e.printStackTrace();
		} catch (SignatureException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

결과

1. create key pair -----------------------------
Public  key modulus : 8248696551746632304090375750090525095455249159692340933778807179048823058050266268679997440009797868855529133651252439721233111988778274617606294142148157
Public  key exponent: 65537
Private key modulus : 8248696551746632304090375750090525095455249159692340933778807179048823058050266268679997440009797868855529133651252439721233111988778274617606294142148157
Private key exponent: 7921703957617548721899753716754620427893524678607141706686365641426276369846256193464100508658766225776306979668612467878281049443630080785215985430577793

2. save key file -----------------------------
PublicKey.txt, PrivateKey.txt file saved.

3. load key file -----------------------------
load PubilcKey.txt, PrivateKey.txt

4. encrypt test -----------------------------
input:This is an example.
encrypt(pubkey):NHeP0KvZpgqd/yE5HEbh5v/kuOP6jvKE8+y9wJb1p7Nt0SOlJrFrtkn+i+75h2d2EiSBZBxBAMErsG7wp6BN3A==
decrypt(prikey):This is an example.

5. digital sign test -----------------------------
signature:KSsmktqxCDjmy4mHMxT+aUb/y2m7b4B9zq1jxMixGs7J8dPTqyKD5LJkXgNwIm4pY6JIn9YotxwyWUP3hjzaXA==
verify:true

Written with StackEdit.

ASN.1 c/cpp code

What is ASN.1

Abstract Syntax Notation One 이란 뜻.
ITU에서 네트웍 데이터 교환을 정의한 프로토콜로 사용하는 표준화 포맷임. (X.680, X.681, X.682, X.683, X.690, X.691, X.692, X.693 등에 설명)
이 기종 장치들 간의 데이터 교환시 표준화가 필요하다. (byte ordering 등 여러가지 문제)
여러가지 ISO 표준 문서에서 사용하는 자료 구조 형태를 보면 ASN.1 문법으로 사용한다.
ASN.1 에서는 자료 구조들을 정의하는데, 길이라던지 범위라던지 등의 제약 조건 등도 포함된다. 자료 구조를 정의하는 문법의 문서 형태라서 소프트웨어 구현시에는 자체로 사용할 수 없고, ASN.1을 인코딩하여 사용한다. 이런 인코딩 들을 하기 위한 작업을 개발 언어별로 소스를 자동으로 만들어 주는 역할을 하는 것이 ASN.1 컴파일러 이다.

BER는 ASN.1의 기본 인코딩 규칙을 나타낸다. ASN.1 데이터를 인코딩하는 다른 방법으로는 DER, CER, PER 등이 있다.

ASN.1 compiler는 ASN.1 문법으로 작성된 문서를 읽어 원하는 언어의 소스 파일로 만들어 주는 역할을 한다. (asn1c는 c 소스 코드를 생성한다.)

compiler download

Download asn1c(Compiler) source code and build.
버전에 따라 생성되는 코드가 다를 수 있다. 가장 최신 버전을 사용하는 것이 좋다. 상용으로 컴파일러를 파는 곳도 많다.

# wget https://lionet.info/soft/asn1c-0.9.28.tar.gz
# tar xvfz asn1c-0.9.28.tar.gz
# cd asn1c-0.9.28
./configure
./make
./make install
# asn1c -h
ASN.1 Compiler, v0.9.28

# Installation path may be /usr/local/bin.

Syntax

문법은 ASN.1 Syntax 검색하여 자세한 것은 찾아보고, 여기서는 간단한 예제 맛 보기.

Order ::= SEQUENCE {
  header Order-header,
  items SEQUENCE OF Order-line
  }
Order-header ::= SEQUENCE {
  number Order-number,
  date	Date,
  client Client,
  payment Payment-method
}

Certificate ::= SEQUENCE {
  tbsCertificate TBSCertificate,
  signatureAlgorithm AlgorithmIdentifier,
  signatureValue BIT STRING
}

TBSCertificate ::= SEQUENCE {
  version 	[0] EXPLICIT Version DEFAULT v1,
  serialNumber CertificateSerialNumber,
  signature	AlgorithmIdentifier,
  issuer	Name,
  validity	Validity,
  ...
}

Test ASN1c

asn 파일을 만들었으면 asn1c로 c코드를 생성한다.
Makefile을 만들어 사용하기.

--build.sh
#!/bin/sh
make -f Makefile.asn

--Makefile.asn
CC=cc
CPP=g++

.SUFFIXES=.c .o
.SUFFIXES=.cpp .o

all:
	asn1c format_iso.asn
	-rm converter-sample.c converter-sample.o
	$(CC) -c -fPIC -g *.c -I.
	$(CPP) -c -fPIC -g *.cpp -I.
#	$(CPP) -o a.out *.o

--Makefile.api
...
all:
	$(CC) -c -fPIC *.c -I.
	$(CPP) -c -fPIC asnapi.cpp -I . -D_LITTLE_ENDIAN_

test:
	$(CPP) -c -fPIC asnapitest.cpp -I.
	$(CPP) -o a.out *.o 
		

위에서 converter-sample.c를 참고하여 인코딩을 하는 것을 쉽게 따라해 볼 수 있다.
실제 사용하지는 않을 테니까 rm으로 빌드시 삭제하였다.

ASN api wrapper

ASN1 문서가 asn1c에 의해 컴파일되면 소스 코드들이 잔뜩 생긴다. 정의한 자료구조마다 헤더파일과 소스 파일이 각각 생성되어 생성된 소스 파일들이 너무 많다.
자료구조들을 사용하기 위해서는 wrapper하여 변환하는 함수를 만드는 것이 좋다.
ASN1 문서에 자료 구조 정의한 이름으로 소스 파일들이 생성되니, 최상위에 정의한 자료구조 명으로된 헤더 파일을 include하여 작성한다. 타입은 자료구조명에 _t가 붙어서 자동으로 생성된다.

wrapper example)

-- asnapi.h
#include "myData.h"
...
myData_t* mynotation2iso(string mynote) ;
myData_t* xer2iso(string mynote) ;
string iso2xer(myData_t *mydata) ;
myData_t* bin2iso(char *bin) ;
char* iso2bin(myData_t *mydata, OUT int *retbufsize) ;
...

--asnapi.cpp
위 wrapper 함수들을 구현

ASN 자료구조 생성

mynotation2iso():
mydata_t *mydata ;
// 메모리 초기화
mydata = (mydata_t*) calloc(1, sizeof *mydata) ;
// 메모리 해제 함수 연결. 메모리 해제시 내부의 리스트 엘리먼트들에 할당한 
// 메모리들이 있을 때는 수동으로 메모리 해제 함수를 만든다.
mydata->bodies.list.free = free_body ;

// 스트링 할당. 4바이트. 마지막 null 포함하여 octet string자료 구조내에 저장. 
// 내부에서 contents 메모리 할당이 발생함. free 필요.  
OCTET_STRING_fromBuf(&mydata->header.formatId, "abc", 4) ;
mydata->header.number = 1 ;

Body_t* body=NULL;
body = (Body_t*) calloc(1, sizeof *body) ;
body->reprenentation.qualityRecord.qualityBlock.list.free=free_qualityBlock ;

// 버퍼에서 스트링을 읽어 자료구조 octet 스트링에 복사. 일반 바이너리값 
// 바이트 어레이도 이렇게 저장 가능.  
OCTET_STRING_fromBuf( &body->representation.captureDateTime, 
	(const char*)&utc, 9) ;
QualityBlock_t *qb=NULL ;
qb = (QualityBlock_t*) calloc(1, sizeof *qb) ;
..
// 리스트 구조(sequence of)는 이렇게 추가한다.
asn_sequence_add(&body->representation.qualityRecord.qualityBlocks, qb) ;
...
int zero=0 ;

// new_fromBuf로 하면 octet 스트링 자료구조 메모리가 할당되어 반환된다. 
body->representationBody.extendedData = 
	OCTET_STRING_new_fromBuf(&asn_DEF_OCTET_STRING, (const char*)&zero, 2) ;
asn_sequence_add(&mydata->bodies, body) ;

// XER 포맷으로 변환하여 스트링 출력
xer_fprintf(stdout, &asn_DEF_mydata, mydata) ;
return mydata ;

free_body(Body_t *b):
free_scd(b->representation.channelDescription.descriptions.x) ;

// 리스트 해제. 내부에 free 지정 콜백이 돌아가면서 내부의 메모리 해제.
asn_sequence_empty(&b->representation.qualityRecord.qualityBlocks)

// 내부 버퍼 메모리 해제 및 OCTET_STRING 구조체 메모리도 해제.
// extendedData 자체가 OCTET_STRING* 타입. 별도 할당(new_fromBuf)한 
// 메모리 해제가 필요하여 마지막 플래그값으로 0이로 설정. 
OCTET_STRING_free(&asn_DEF_OCTET_STRINTG, b->representationBody.extendedData,0) ;

// 마지막플래그는 contents_only로 1이면 contents만 해제한다. 
// octet_string 구조체(컨테이너) 메모리는 해제하지 않음.
// captureDateTime 자체가 OCTET_STRING 타입으로 별도 할당한 메모리가 아니라
// 상위 구조체 할당시 잡힌 영역임. 여기서 해제 불필요.
OCTET_STRING_free(&asn_DEF_OCTET_STRINTG, &b->representation.captureDateTime,1) ;
free(b);

set_nodefree(mydata_t *mydata):
mydata->bodies.list.free=free_body ;
asn_anonymous_sequence_ *ss = _A_SEQUENCE_FROM_VOID( &mydata->bodies) ;
for (int j=0; j<ss->count; j++) {
	Body_t *body = (Body_t*)ss->array[j] ;
	body->representation.qualityRecord.qualityBlocks.list.free = 
		free_qualityBlock ;
}

위의 예제를 보면 복잡해 보인다. 실제 생성된 소스코드들을 분석해야지만 사용할 수 가 있다. 특히 리스트 사용이나 스트링 처리, 메모리 관리가 처음에 어려울 수 있다. 위의 예제를 참고하면 스트링이나 리스트 사용시 도움이 될 것이다. (주의: asn1c는 버전에 따라 Integer_t에 int 값 할당하는 것도 에러가 날 수 있다.)

Example

original page http://lionet.info/asn1c/examples.html

• MyModule.asn1

MyModule DEFINITIONS ::=
BEGIN

MyTypes ::= SEQUENCE {
    myObjectId   OBJECT IDENTIFIER,
    mySeqOf      SEQUENCE OF MyInt,
    myBitString  BIT STRING {
                        muxToken(0), 
                        modemToken(1)
                 }
}

MyInt ::= INTEGER (0..65535)

END

• asn1c MyModule.asn1

• create structure files.

• my-program.c

#include <stdio.h>	/* for stdout */
#include <stdlib.h>	/* for malloc() */
#include <assert.h>	/* for run-time control */
#include "MyTypes.h"	/* Include MyTypes definition */

int main() {
	/* Define an OBJECT IDENTIFIER value */
	int oid[] = { 1, 3, 6, 1, 4, 1, 9363, 1, 5, 0 }; /* or whatever */

	/* Declare a pointer to a new instance of MyTypes type */
	MyTypes_t *myType;
	/* Declare a pointer to a MyInt type */
	MyInt_t *myInt;

	/* Temporary return value */
	int ret;

	/* Allocate an instance of MyTypes */
	myType = calloc(1, sizeof *myType);
	assert(myType);	/* Assume infinite memory */

	/*
	 * Fill in myObjectId
	 */
	ret = OBJECT_IDENTIFIER_set_arcs(&myType->myObjectId,
			oid, sizeof(oid[0]), sizeof(oid) / sizeof(oid[0]));
	assert(ret == 0);

	/*
	 * Fill in mySeqOf with a couple of integers.
	 */

	/* Prepare a certain INTEGER */
	myInt = calloc(1, sizeof *myInt);
	assert(myInt);
	*myInt = 123;	/* Set integer value */

	/* Fill in mySeqOf with the prepared INTEGER */
	ret = ASN_SEQUENCE_ADD(&myType->mySeqOf, myInt);
	assert(ret == 0);

	/* Prepare another integer */
	myInt = calloc(1, sizeof *myInt);
	assert(myInt);
	*myInt = 111222333;	/* Set integer value */

	/* Append another INTEGER into mySeqOf */
	ret = ASN_SEQUENCE_ADD(&myType->mySeqOf, myInt);
	assert(ret == 0);

	/*
	 * Fill in myBitString
	 */

	/* Allocate some space for bitmask */
	myType->myBitString.buf = calloc(1, 1);
	assert(myType->myBitString.buf);
	myType->myBitString.size = 1;	/* 1 byte */

	/* Set the value of muxToken */
	myType->myBitString.buf[0] |= 1 << (7 - myBitString_muxToken);

	/* Also set the value of modemToken */
	myType->myBitString.buf[0] |= 1 << (7 - myBitString_modemToken);

	/* Trim unused bits (optional) */
	myType->myBitString.bits_unused = 6;

	/*
	 * Print the resulting structure as XER (XML)
	 */
	xer_fprint(stdout, &asn_DEF_MyTypes, myType);

	return 0;
}

• $ cc -o a.out -I. *.c
• ./a.out

<MyTypes>
    <myObjectId>1.3.6.1.4.1.9363.1.5.0</myObjectId>
    <mySeqOf>
        <MyInt>123</MyInt>
        <MyInt>111222333</MyInt>
    </mySeqOf>
    <myBitString>11</myBitString>
</MyTypes>

• my-program2.c

#include <stdio.h>	/* for stdout */
#include <stdlib.h>	/* for malloc() */
#include <assert.h>	/* for run-time control */
#include "MyTypes.h"	/* Include MyTypes definition */

int main(int argc, char *argv[]) {
	char buf[1024];	/* Hope, sufficiently large buffer */
	MyTypes_t *myType = 0;
	asn_dec_rval_t rval;
	char *filename;
	size_t size;
	FILE *f;

	/*
	 * Target variables.
	 */
	int *oid_array;	/* holds myObjectId */
	int oid_size;
	int *int_array;	/* holds mySeqOf */
	int int_size;
	int muxToken_set;	/* holds single bit */
	int modemToken_set;	/* holds single bit */

	/*
	 * Read in the input file.
	 */
	assert(argc == 2);
	filename = argv[1];
	f = fopen(filename, "r");
	assert(f);
	size = fread(buf, 1, sizeof buf, f);
	if(size == 0 || size == sizeof buf) {
		fprintf(stderr, "%s: Too large input\n", filename);
		exit(1);
	}

	/*
	 * Decode the XER buffer.
	 */
	rval = xer_decode(0, &asn_DEF_MyTypes, &myType, buf, size);
	assert(rval.code == RC_OK);

	/*
	 * Convert the OBJECT IDENTIFIER into oid_array/oid_size pair.
	 */
	/* Figure out the number of arcs inside OBJECT IDENTIFIER */
	oid_size = OBJECT_IDENTIFIER_get_arcs(&myType->myObjectId,
			0, sizeof(oid_array[0]), 0);
	assert(oid_size >= 0);
	/* Create the array of arcs and fill it in */
	oid_array = malloc(oid_size * sizeof(oid_array[0]));
	assert(oid_array);
	(void)OBJECT_IDENTIFIER_get_arcs(&myType->myObjectId,
			oid_array, sizeof(oid_array[0]), oid_size);

	/*
	 * Convert the sequence of integers into array of integers.
	 */
	int_size = myType->mySeqOf.list.count;
	int_array = malloc(int_size * sizeof(int_array[0]));
	assert(int_array);
	for(int_size = 0; int_size < myType->mySeqOf.list.count; int_size++)
		int_array[int_size] = *myType->mySeqOf.list.array[int_size];

	if(myType->myBitString.buf) {
		muxToken_set   = myType->myBitString.buf[0]
					& (1 << (7 - myBitString_muxToken));
		modemToken_set = myType->myBitString.buf[0]
					& (1 << (7 - myBitString_modemToken));
	} else {
		muxToken_set = modemToken_set = 0;	/* Nothing is set */
	}

	return 0;
}

Authors
crazyj7@gmail.com
Written with StackEdit.