Total Pageviews

2018/11/12

[Java] javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed

Scenario

 發生 PKIX path building failed 錯誤的程式碼 (Line 82)
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
package test.service;

import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;
import java.util.ArrayList;
import java.util.List;

import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.io.IOUtils;
import org.apache.http.HttpHost;
import org.apache.http.HttpResponse;
import org.apache.http.client.config.RequestConfig;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.conn.socket.LayeredConnectionSocketFactory;
import org.apache.http.conn.ssl.SSLConnectionSocketFactory;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;

import test.vo.Bank;
import test.vo.PostOffice;

public class OpenDataService {

    private static final String BANK_OPEN_DATA_URL = "https://quality.data.gov.tw/dq_download_csv.php?nid=6041&md5_url=4cbb7958872cb677421021ef63b2e2b9";

    public List<Bank> parseBankData() throws IOException {

        String csvData = "";
        try {
            csvData = getBankData();
        } catch (Exception e1) {
            throw new RuntimeException("金融機構基本資料 取得失敗, 錯誤原因: " + e1.getMessage(), e1);
        }

        List<Bank> bankData = new ArrayList<>();

        String[] FILE_HEADER = { "類別", "總機構代號", "分支機構代號", "機構名稱", "地址", "緯度", "經度", "電話", "負責人", "異動日期", "金融機構網址" };
        CSVFormat csvFileFormat = CSVFormat.DEFAULT.withHeader(FILE_HEADER);

        try (CSVParser csvFileParser = new CSVParser(new StringReader(csvData), csvFileFormat);) {
            List<CSVRecord> csvRecords = csvFileParser.getRecords();
            for (int i = 1; i < csvRecords.size(); i++) {
                CSVRecord record = csvRecords.get(i);
                String type = record.get("類別");
                String headquarterCode = record.get("總機構代號");
                String branchCode = record.get("分支機構代號");
                String branchName = record.get("機構名稱");
                String address = record.get("地址");
                String latitude = record.get("緯度");
                String longitude = record.get("經度");
                String phone = record.get("電話");
                String representative = record.get("負責人");
                String updateDate = record.get("異動日期");
                String url = record.get("金融機構網址");

                Bank bank = Bank.builder().type(type).headquarterCode(headquarterCode).branchCode(branchCode)
                        .branchName(branchName).address(address).latitude(latitude).longitude(longitude).phone(phone)
                        .representative(representative).updateDate(updateDate).url(url).build();
                bankData.add(bank);
            }
        } catch (IOException e) {
            throw new RuntimeException("金融機構基本資料 解析失敗, 錯誤原因: " + e.getMessage(), e);
        }
        return bankData;
    }

    public String getBankData() throws Exception {
        String bankDataStr = "";
        InputStream inputStream = null;
        try (CloseableHttpClient httpClient = HttpClients.createDefault();) {
      
            HttpGet httpGet = new HttpGet(BANK_OPEN_DATA_URL);

            HttpResponse httpResponse = httpClient.execute(httpGet);

            inputStream = httpResponse.getEntity().getContent();
            bankDataStr = IOUtils.toString(inputStream, "UTF-8");
        } catch (IOException e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(inputStream);
        }
        return bankDataStr;
    }

}


錯誤訊息如下： 
Exception in thread "main" java.lang.RuntimeException: 金融機構基本資料 取得失敗, 錯誤原因: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at test.service.OpenDataService.parseBankData(OpenDataService.java:41)
    at test.OpenDataClient.main(OpenDataClient.java:12)
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
    at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1959)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:302)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:296)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1514)
    at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:216)
    at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1026)
    at sun.security.ssl.Handshaker.process_record(Handshaker.java:961)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1072)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1385)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1413)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1397)


How-To
依據參考文獻，透過暴力法，overwrite TrustManager，使其忽略檢查、校驗。程式碼改寫如下： 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
package test.service;

import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;
import java.util.ArrayList;
import java.util.List;

import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.io.IOUtils;
import org.apache.http.HttpHost;
import org.apache.http.HttpResponse;
import org.apache.http.client.config.RequestConfig;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.conn.socket.LayeredConnectionSocketFactory;
import org.apache.http.conn.ssl.SSLConnectionSocketFactory;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;

import test.vo.Bank;
import test.vo.PostOffice;

public class OpenDataService {

    private static final String BANK_OPEN_DATA_URL = "https://quality.data.gov.tw/dq_download_csv.php?nid=6041&md5_url=4cbb7958872cb677421021ef63b2e2b9";

    public List<Bank> parseBankData() throws IOException {

        String csvData = "";
        try {
            csvData = getBankData();
        } catch (Exception e1) {
            throw new RuntimeException("金融機構基本資料 取得失敗, 錯誤原因: " + e1.getMessage(), e1);
        }

        List<Bank> bankData = new ArrayList<>();

        String[] FILE_HEADER = { "類別", "總機構代號", "分支機構代號", "機構名稱", "地址", "緯度", "經度", "電話", "負責人", "異動日期", "金融機構網址" };
        CSVFormat csvFileFormat = CSVFormat.DEFAULT.withHeader(FILE_HEADER);

        try (CSVParser csvFileParser = new CSVParser(new StringReader(csvData), csvFileFormat);) {
            List<CSVRecord> csvRecords = csvFileParser.getRecords();
            for (int i = 1; i < csvRecords.size(); i++) {
                CSVRecord record = csvRecords.get(i);
                String type = record.get("類別");
                String headquarterCode = record.get("總機構代號");
                String branchCode = record.get("分支機構代號");
                String branchName = record.get("機構名稱");
                String address = record.get("地址");
                String latitude = record.get("緯度");
                String longitude = record.get("經度");
                String phone = record.get("電話");
                String representative = record.get("負責人");
                String updateDate = record.get("異動日期");
                String url = record.get("金融機構網址");

                Bank bank = Bank.builder().type(type).headquarterCode(headquarterCode).branchCode(branchCode)
                        .branchName(branchName).address(address).latitude(latitude).longitude(longitude).phone(phone)
                        .representative(representative).updateDate(updateDate).url(url).build();
                bankData.add(bank);
            }
        } catch (IOException e) {
            throw new RuntimeException("金融機構基本資料 解析失敗, 錯誤原因: " + e.getMessage(), e);
        }
        return bankData;
    }

    public String getBankData() throws Exception {
        String bankDataStr = "";
        InputStream inputStream = null;
        try (CloseableHttpClient httpClient = createHttpsClient();) {

            HttpGet httpGet = new HttpGet(BANK_OPEN_DATA_URL);

            HttpResponse httpResponse = httpClient.execute(httpGet);

            inputStream = httpResponse.getEntity().getContent();
            bankDataStr = IOUtils.toString(inputStream, "UTF-8");
        } catch (NoSuchAlgorithmException | KeyManagementException | IOException e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(inputStream);
        }
        return bankDataStr;
    }

    /**
     * 解決 PKIX path building failed 問題
     * 
     * @return HttpClient
     * @throws NoSuchAlgorithmException
     * @throws KeyManagementException
     */
    private CloseableHttpClient createHttpsClient() throws NoSuchAlgorithmException, KeyManagementException {
        // Create a trust manager that does not validate certificate chains
        TrustManager[] trustAllCerts = new TrustManager[] { new X509TrustManager() {
            @Override
            public void checkClientTrusted(X509Certificate[] arg0, String arg1) throws CertificateException {
            }

            @Override
            public void checkServerTrusted(X509Certificate[] arg0, String arg1) throws CertificateException {
            }

            @Override
            public X509Certificate[] getAcceptedIssuers() {
                return null;
            }
        } };

        SSLContext ctx = SSLContext.getInstance("TLS");
        ctx.init(null, trustAllCerts, null);

        LayeredConnectionSocketFactory sslSocketFactory = new SSLConnectionSocketFactory(ctx);

        CloseableHttpClient httpclient = HttpClients.custom().setSSLSocketFactory(sslSocketFactory).build();
        return httpclient;
    }

}



Reference
[1] http://www.aneasystone.com/archives/2016/04/java-and-https.html

Posted by Albert Kuo at 8:00 AM 0 comments
Labels:

2018/11/11

[Java] 如何連上政府資料開放平台，取得金融機構基本資料

Scenario

 金融機構基本資料: https://data.gov.tw/dataset/6041


 How-To
1. Add Maven dependency: 
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>165test</groupId>
    <artifactId>test165</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>test165</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.5.5</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-io</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-csv</artifactId>
            <version>1.5</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.22</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
</project>

2. Bank Value Object: 用來儲存 CSV 解析後的結果 
package test.vo;

import lombok.Builder;
import lombok.Data;
import lombok.ToString;

@Data
@Builder
@ToString
public class Bank {

    /*
     * 類別
     */
    private String type;

    /*
     * 總機構代號
     */
    private String headquarterCode;

    /*
     * 分支機構代號
     */
    private String branchCode;

    /*
     * 機構名稱
     */
    private String branchName;

    /*
     * 地址
     */
    private String address;

    /*
     * 緯度
     */
    private String latitude;

    /*
     * 經度
     */
    private String longitude;
    
    /*
     * 電話
     */
    private String phone;
    
    /*
     * 負責人
     */
    private String representative;
    
    /*
     * 異動日期
     */
    private String updateDate;
    
    /*
     * 金融機構網址
     */
    private String url;

}


3. Service class: 用來連上 open data URL，並解析獲得的結果，轉換成 List of Bank 
package test.service;

import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;
import java.util.ArrayList;
import java.util.List;

import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.commons.io.IOUtils;
import org.apache.http.HttpHost;
import org.apache.http.HttpResponse;
import org.apache.http.client.config.RequestConfig;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.conn.socket.LayeredConnectionSocketFactory;
import org.apache.http.conn.ssl.SSLConnectionSocketFactory;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;

import test.vo.Bank;
import test.vo.PostOffice;

public class OpenDataService {

    private static final String BANK_OPEN_DATA_URL = "https://quality.data.gov.tw/dq_download_csv.php?nid=6041&md5_url=4cbb7958872cb677421021ef63b2e2b9";

    public List<Bank> parseBankData() throws IOException {

        String csvData = "";
        try {
            csvData = getBankData();
        } catch (Exception e1) {
            throw new RuntimeException("金融機構基本資料 取得失敗, 錯誤原因: " + e1.getMessage(), e1);
        }

        List<Bank> bankData = new ArrayList<>();

        String[] FILE_HEADER = { "類別", "總機構代號", "分支機構代號", "機構名稱", "地址", "緯度", "經度", "電話", "負責人", "異動日期", "金融機構網址" };
        CSVFormat csvFileFormat = CSVFormat.DEFAULT.withHeader(FILE_HEADER);

        try (CSVParser csvFileParser = new CSVParser(new StringReader(csvData), csvFileFormat);) {
            List<CSVRecord> csvRecords = csvFileParser.getRecords();
            for (int i = 1; i < csvRecords.size(); i++) {
                CSVRecord record = csvRecords.get(i);
                String type = record.get("類別");
                String headquarterCode = record.get("總機構代號");
                String branchCode = record.get("分支機構代號");
                String branchName = record.get("機構名稱");
                String address = record.get("地址");
                String latitude = record.get("緯度");
                String longitude = record.get("經度");
                String phone = record.get("電話");
                String representative = record.get("負責人");
                String updateDate = record.get("異動日期");
                String url = record.get("金融機構網址");

                Bank bank = Bank.builder().type(type).headquarterCode(headquarterCode).branchCode(branchCode)
                        .branchName(branchName).address(address).latitude(latitude).longitude(longitude).phone(phone)
                        .representative(representative).updateDate(updateDate).url(url).build();
                bankData.add(bank);
            }
        } catch (IOException e) {
            throw new RuntimeException("金融機構基本資料 解析失敗, 錯誤原因: " + e.getMessage(), e);
        }
        return bankData;
    }

    public String getBankData() throws Exception {
        String bankDataStr = "";
        InputStream inputStream = null;
        try (CloseableHttpClient httpClient = createHttpsClient();) {
          
            HttpGet httpGet = new HttpGet(BANK_OPEN_DATA_URL);
            HttpResponse httpResponse = httpClient.execute(httpGet);

            inputStream = httpResponse.getEntity().getContent();
            bankDataStr = IOUtils.toString(inputStream, "UTF-8");
        } catch (NoSuchAlgorithmException | KeyManagementException | IOException e) {
            throw e;
        } finally {
            IOUtils.closeQuietly(inputStream);
        }
        return bankDataStr;
    }

    /**
     * 解決 PKIX path building failed 問題
     * 
     * @return HttpClient
     * @throws NoSuchAlgorithmException
     * @throws KeyManagementException
     */
    private CloseableHttpClient createHttpsClient() throws NoSuchAlgorithmException, KeyManagementException {
        // Create a trust manager that does not validate certificate chains
        TrustManager[] trustAllCerts = new TrustManager[] { new X509TrustManager() {
            @Override
            public void checkClientTrusted(X509Certificate[] arg0, String arg1) throws CertificateException {
            }

            @Override
            public void checkServerTrusted(X509Certificate[] arg0, String arg1) throws CertificateException {
            }

            @Override
            public X509Certificate[] getAcceptedIssuers() {
                return null;
            }
        } };

        SSLContext ctx = SSLContext.getInstance("TLS");
        ctx.init(null, trustAllCerts, null);

        LayeredConnectionSocketFactory sslSocketFactory = new SSLConnectionSocketFactory(ctx);

        CloseableHttpClient httpclient = HttpClients.custom().setSSLSocketFactory(sslSocketFactory).build();
        return httpclient;
    }

}


4. Client class: 呼叫 Service class，並取得解析後的結果 (List of Bank)
package test;

import java.util.List;

import test.service.OpenDataService;
import test.vo.Bank;

public class OpenDataClient {

    public static void main(String[] args) throws Exception {
        OpenDataService service = new OpenDataService();
        List<Bank> banks = service.parseBankData();
        banks.forEach(b -> System.out.println(b.toString()));
    }

}

Posted by Albert Kuo at 8:00 AM 0 comments
Labels:

2018/11/10

[SchemaSpy] How to generate SQL Server schema document via SchemaSpy

Steps
1. Download SchemaSpy jar from http://schemaspy.org/
2. Downnload and install Graphviz from https://www.graphviz.org/
3. Download JDBC jar file for your target database
4. Execute command as bellows: 
java -jar "F:\lib\schemaspy-6.0.0-rc2.jar" -dp "F:\sqljdbc_6.0\sqljdbc42.jar" -t mssql05 -db MyDB -host 192.168.0.1 -port 1433 -u user -p secret -charset UTF-8 -o "F:\Schema" -hq



Commonly used parameters

  • -jar: The location of schemaspy jar file
  • -dp: NLooks for drivers here before looking in driverPath in [databaseType].properties. The drivers are usually contained in .jar or .zip files and are typically provided by your database vendor.
  • -t: Type of database (e.g. ora, db2, etc.)
  • -db: Name of database to connect to
  • -host: Databas URL to connect to
  • -port: Database port
  • -u: Valid database user id with read access.
  • -p: Valid password
  • -charset: Assign character set
  • -o: Directory to write the generated HTML/graphs to
  • -hq: Generate either higher-quality diagrams. 

Posted by Albert Kuo at 8:00 AM 0 comments
Labels: , ,

2018/11/09

[SQL Server] Utilize FOR XML PATH to merge multiple rows into one row

Problem
Assume I get two rows from the following SQL statement: 
select t1.CASEID, t1.PID, t1.NAME, t2.QVALUE + CHAR(10) + t2.AVALUE as QA
from TEXT_MAIN t1, TEXT_QA t2
where t1.CASEID = 'W00000003944' and t1.RID = t2.RID



If I would like to merge the two rows into one row:
How-To
You can use FOR XML PATH to fulfill this requirement, here has the example 
select t1.CASEID, t1.PID, t1.NAME,
    (select t2.QVALUE + CHAR(10) + t2.AVALUE + CHAR(10)
     from TEXT_QA t2
     where t1.RID = t2.RID
     FOR XML PATH('')) as QA
from TEXT_MAIN t1
where t1.CASEID = 'W00000003944'


Posted by Albert Kuo at 8:00 AM 0 comments
Labels:
Subscribe to: Posts (Atom)