【MySQL】Prepared Statements

Prepared Statements

MySQL 8.0 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol. Using prepared statements with placeholders for parameter values has the following benefits:

Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.
Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

In order to use MySQL prepared statement, you use three following statements:

PREPARE – prepare a statement for execution.
EXECUTE – execute a prepared statement prepared by the PREPARE statement.
DEALLOCATE PREPARE – release a prepared statement.

The following diagram illustrates how to use a prepared statement:

...

Posted by Wei on 2023-01-15, Last Modified on 2025-08-11

【Security】SQL Injection

SQL 注入（SQL Injection）

Overview

A SQL injection attack consists of insertion or “injection” of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to affect the execution of predefined SQL commands.

SQL 注入漏洞（SQL Injection）是 Web 开发中最常见的一种安全漏洞。可以用它来从数据库获取敏感信息，或者利用数据库的特性执行添加用户，导出文件等一系列恶意操作，甚至有可能获取数据库乃至系统用户最高权限。

而造成 SQL 注入的原因是因为程序没有有效的转义过滤用户的输入，使攻击者成功的向服务器提交恶意的 SQL 查询代码，程序在接收后错误的将攻击者的输入作为查询语句的一部分执行，导致原始的查询逻辑被改变，额外的执行了攻击者精心构造的恶意代码。

很多 Web 开发者没有意识到 SQL 查询是可以被篡改的，从而把 SQL 查询当作可信任的命令。殊不知，SQL 查询是可以绕开访问控制，从而绕过身份验证和权限检查的。更有甚者，有可能通过 SQL 查询去运行主机系统级的命令。

...

Posted by Wei on 2023-01-11, Last Modified on 2025-08-11

【Design Pattern】Structural - Facade

...

Posted by Wei on 2023-01-10, Last Modified on 2025-08-11

【Design Pattern】Transformer

Transformers

Transformers contain the business logic for changing a model’s format to whatever you need for the output, whether that’s HTML, Datatable, or JSON API. Decoupling this logic from the model allows for unobtrusive schema changes, a reliable place for housing formatting logic, and data formatting operations outside of views (for example, converting a date, or performing translations).

...

Posted by Wei on 2023-01-10, Last Modified on 2025-08-11

【Engineering】Chaos-Engineering

...

Posted by Wei on 2023-01-05, Last Modified on 2025-08-11

【Engineering】SLO and SLA

SLO（Service Level Objective，服务水平目标）和SLA（Service Level Agreement，服务水平协议）是服务管理中的两个重要概念，它们之间有着明显的区别：

定义:
- SLO：这是具体的性能目标，如系统的可用性、响应时间或处理能力等。它们是量化的指标，用于衡量服务的性能和可靠性。
- SLA：这是服务提供商和客户之间的正式协议。它包含多个SLO，还可能包括对于服务质量的承诺、违约责任、服务时间等。
功能和目的:
- SLO：是服务提供者内部用来确保其服务达到预期标准的具体目标。SLO帮助团队专注于重要的性能指标。
- SLA：是一种法律文件，规定了服务提供者必须达到的最低服务标准以及未能达到这些标准时的后果。它为客户和服务提供商之间的关系提供了法律框架。
范围和细节:
- SLO：更为具体和量化，专注于单个性能指标。
- SLA：范围更广，不仅包括性能指标，还包括服务的所有其他方面，如客户支持、报告要求等。

简而言之，SLO是SLA的一部分，它们共同帮助确保服务提供者能夠满足客户的期望和需求。SLA提供了一个全面的服务框架，而SLO则是在这个框架内，具体衡量服务性能的标准。

...

Posted by Wei on 2023-01-05, Last Modified on 2025-08-11

【Kafka】通过 Prometheus 监控 Kafka

Via anielqsj/kafka-exporter

Run Docker Image

docker run -tid --restart unless-stopped -p 9308:9308 danielqsj/kafka-exporter --kafka.server=192.168.18.129:9092

Grafana Dashboard

Grafana Dashboard ID: 7589, name: Kafka Exporter Overview.

For details of the dashboard please see Kafka Exporter Overview.

...

Posted by Wei on 2022-11-13, Last Modified on 2025-08-11

【Kafka】常用命令

...

Posted by Wei on 2022-11-13, Last Modified on 2025-08-11

【macOS】干掉 Crowdstrike

$ ps aux | grep crowdstrike
root               347   0.0  0.2 34322908  33944   ??  Ss    2:13PM   2:29.46 /Library/SystemExtensions/a-b-c-d/com.crowdstrike.falcon.Agent.systemextension/Contents/MacOS/com.crowdstrike.falcon.Agent

$ sudo chmod -R 777 /Library/SystemExtensions/a-b-c-d/com.crowdstrike.falcon.Agent.systemextension
$ cd /Library/SystemExtensions/a-b-c-d/
$ l
total 0
drwxr-xr-x@ 3 root  wheel    96B Aug 30 07:17 .
drwxr-xr-x  8 root  wheel   256B Oct 10 22:19 ..
drwxrwxrwx@ 3 root  wheel    96B Feb 15  2022 com.crowdstrike.falcon.Agent.systemextension
$ l
total 0
drwxr-xr-x@ 3 root  wheel    96B Oct 11 11:13 .
drwxr-xr-x  8 root  wheel   256B Oct 10 22:19 ..
drwxrwxrwx@ 3 root  wheel    96B Feb 15  2022 com.crowdstrike.falcon123.Agent.systemextension
$ sudo cp -R /Library/SystemExtensions/a-b-c-d/com.crowdstrike.falcon.Agent.systemextension /Library/SystemExtensions/a-b-c-d/com.crowdstrike.falcon123.Agent.systemextension
$ sudo rm /Library/SystemExtensions/a-b-c-d/com.crowdstrike.falcon.Agent.systemextension/Contents/MacOS/com.crowdstrike.falcon.Agent
$ sudo touch /Library/SystemExtensions/a-b-c-d/com.crowdstrike.falcon.Agent.systemextension/Contents/MacOS/com.crowdstrike.falcon.Agent
$ cd /Library/SystemExtensions/a-b-c-d/; tree
.
├── com.crowdstrike.falcon.Agent.systemextension
│   └── Contents
│       ├── Info.plist
│       ├── MacOS
│       │   └── com.crowdstrike.falcon.Agent
│       ├── _CodeSignature
│       │   └── CodeResources
│       └── embedded.provisionprofile
└── com.crowdstrike.falcon123.Agent.systemextension
    └── Contents
        ├── Info.plist
        ├── MacOS
        │   └── com.crowdstrike.falcon123.Agent
        ├── _CodeSignature
        │   └── CodeResources
        └── embedded.provisionprofile
8 directories, 8 files

# 重启
$ ps aux | grep crowdstrike
# 如果没有 crowdstrike，则说明成功disable了

...

Posted by Wei on 2022-10-10, Last Modified on 2025-08-11

【TrueNAS】性能调优

Knowledge

Cache

The technologies work in conjunction with a flash-based separate write log (SLOG), which can be thought of as a write cache that keeps what’s called the ZFS-intent log (ZIL), used to accelerate writes. On the read side, flash can be used as a level two adaptive replacement (read) cache (L2ARC) to keep the hottest data sets on the faster flash media. Workloads with synchronous writes such as NFS and databases consistently benefit from SLOG devices, while workloads with frequently-accessed data may benefit from an L2ARC device. The reason that an L2ARC device is not always the best choice is because the level one ARC in RAM will always provide a faster cache; also, some RAM will be used by the L2ARC table.

About L2CARC and ARC

a level two ARC, or L2ARC, is typically an SSD or NVMe-based read cache, instead of write and read cache, which means adding L2CARC won’t boost the write speed.
An SSD or NVMe card is often used for a ZFS separate log device, or SLOG, to boost the performance of synchronized writes, such as over NFS or with a database.

...

Posted by Wei on 2022-04-11, Last Modified on 2025-08-11