# MySQL-Server Connection
## Step1: Docker-Hub Pull mysql-server
https://hub.docker.com/r/mysql/mysql-server/tags?name=8.0.3
```
docker pull mysql/mysql-server:8.0.32-1.2.11-server
```
## Step2: Run Docker:MySQL
* Open Rancher Desktop
* Run Docker
```
docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=p@ssw0rd -v D:\temp\mysql8:/var/lib/mysql -d -p 3306:3306 mysql:8.0
```
`-v` Container path: `D:\temp\mysql8`
`-e` MYSQL_ROOT_PASSWORD: Custom password
## Step3: Test for Connection
* Download DBeaver
https://dbeaver.io/
* Connect to MySQL
## Step4: Add Data base
* MySQL Shell for VS Code
https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code
* Connect to mySQL
* Create Data base
```
CREATE DATABASE mysecurity
```
* Create Table
Sample 1
```
USE mysecurity;
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30)
)
```
Sample 2
```
CREATE DATABASE mysecurity;
USE mysecurity;
CREATE TABLE member
(
member_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(256) NOT NULL UNIQUE KEY,
password VARCHAR(256) NOT NULL,
name VARCHAR(256) NOT NULL,
age INT NOT NULL
);
INSERT INTO member(email, password, name, age) VALUES ('test3@gmail.com', 333, 'Test 3', '20');
INSERT INTO member(email, password, name, age) VALUES ('test4@gmail.com', 444, 'Test 4', '30');
INSERT INTO member(email, password, name, age) VALUES ('test5@gmail.com', 555, 'Test 5', '22');
```
* Edit Table
Use DBeaver to edit
* List Table
```
USE mysecurity;
SELECT * FROM mysecurity.student;
```
* Remove Database
```
DROP DATABASE mysecurity
```
# Others
## 實作 CORS 設定
### 方法1: Spring Security
設定 CorsConfig 於 securityFilterChain
```
@Bean
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
return http
.csrf(csrf -> csrf.disable())
.httpBasic(Customizer.withDefaults())
.formLogin(Customizer.withDefaults())
.authorizeHttpRequests(request -> request
.anyRequest().authenticated())
.cors(cors -> cors
.configurationSource(createCorsConfig()))
.build();
}
```
```
private CorsConfigurationSource createCorsConfig() {
CorsConfiguration config = new CorsConfiguration();
config.setAllowedOrigins(List.of("*"));
config.setAllowedHeaders(List.of("*"));
config.setAllowedMethods(List.of("*"));
// config.setAllowCredentials(true);
config.setMaxAge(3600L);
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
source.registerCorsConfiguration("/**", config);
return source;
}
```
### 方法2: @CrossOrigin
直接在 controller 加上 @CrossOrigin
```
@CrossOrigin(origins = "*")
@RestController
public class HelloController {
@RequestMapping("/hello")
public String hello() {
System.out.println("執行 /hello");
return "Hello!";
}
}
```
### 方法3: Nginx
```
<!DOCTYPE HTML>
<html>
<head></head>
<body>
<h3>這是前端開發的 demo 頁面</h3>
<p>API 返回結果為：</p>
<p id="output"></p>
</body>
<script>
// 指定 http method 和 url
const method = "GET";
const url = "http://localhost:222/hello";
// 前端發出 http request 請求
const http = new XMLHttpRequest();
http.open(method, url);
// http.setRequestHeader("Authorization", "Basic dGVzdDE6MTEx")
// http.withCredentials = true;
http.send();
http.onreadystatechange = (e) => {
if (http.status === 200) {
document.getElementById("output").innerHTML = http.responseText;
document.getElementById("output").style.backgroundColor = "#18C5A6";
} else {
document.getElementById("output").innerHTML = "請求失敗";
document.getElementById("output").style.backgroundColor = "#FD3C3C";
}
}
</script>
</html>
```
```
docker run --name my-nginx -v {path}\my_nginx.conf:/etc/nginx/nginx.conf:ro -d -p 222:222 nginx
```
## 防禦 CSRF 攻擊 (加入 X-XSRF-TOKEN 認證)
```
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
return http
.sessionManagement(session -> session
.sessionCreationPolicy((SessionCreationPolicy.ALWAYS)))
.csrf(csrf -> csrf.csrfTokenRepository(CookieCsrfTokenRepository.withHttpOnlyFalse())
.csrfTokenRequestHandler(createCsrHandler()))
...
.build();
}
private CsrfTokenRequestAttributeHandler createCsrHandler() {
CsrfTokenRequestAttributeHandler csrHandler = new CsrfTokenRequestAttributeHandler();
csrHandler.setCsrfRequestAttributeName(null);
return csrHandler;
}
```
# QA
## MySQL : Public Key Retrieval is not allowed
Ref.
1. https://stackoverflow.com/questions/50379839/connection-java-mysql-public-key-retrieval-is-not-allowed
2. https://sinyilin.github.io/SQL/20230425/4002286829/
Sign in with Wallet
Connect another wallet