Email 信息安全
2020-02-10
DomainKeys Identified Mail,域名密钥识别邮件
作用是使用非对称加密(公钥 + 私钥)对邮件内容进行签名,防止伪造和篡改。
历史
- 2004 年,雅虎 DomainKeys 和思科 Identified Internet Mail 合并为 DKIM。
- 2007 年 2 月,DKIM 被列入互联网工程工作小组(IETF)的标准提案(Proposed Standard),并于同年 5 月成为正式标准(Standards Track)。
示例
DKIM-Signature: v=1; a=rsa-sha256; d=example.net; s=brisbane;
c=relaxed/simple; q=dns/txt; i=foo@eng.example.net;
t=1117574938; x=1118006938; l=200;
h=from:to:subject:date:keywords:keywords;
z=From:foo@eng.example.net|To:joe@example.com|
Subject:demo=20run|Date:July=205,=202005=203:44:08=20PM=20-0700;
bh=MTIzNDU2Nzg5MDEyMzQ1Njc4OTAxMjM0NTY3ODkwMTI=;
b=dzdVyOfAKCdLXdJOc9G2q8LoXSlEniSbav+yuU4zGeeruD00lszZ
VoG4ZHRNiYzR
Tag |
Required |
Meanning |
v |
✓ |
version |
a |
✓ |
signing algorithm |
d |
✓ |
Signing Domain Identifier (SDID) |
s |
✓ |
selector |
c |
- |
canonicalization algorithm(s) for header and body |
q |
- |
default query method |
i |
- |
Agent or User Identifier (AUID) |
t |
recommended |
signature timestamp |
x |
recommended |
expire time |
l |
- |
body length |
h |
✓ |
header fields - list of those that have been signed |
z |
- |
header fields - copy of selected header fields and values |
bh |
✓ |
body hash |
b |
✓ |
signature of headers and body |
示例 2
邮件中的 DKIM-Signature 头:
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=mail.instagram.com;
s=s1024-2013-q3; t=1674163477;
bh=6RXa/HYJQNKpB5PIGtLn7v1NE/4T5FaqxBLWNHVRZu8=;
h=Date:To:Subject:From:MIME-Version:Content-Type;
b=VAY3x16QtXeH1rQxu6eEbzhfgZl69m1sG9XzN3ym4FbWiMg+K+IfMGF4yszGYk8yO
YXAAJZuQfG45pjthISDDSwhhBK0WGgufQ8ofnzhNUN9WT/okEATC+JfzksS9w2Ts4V
ALa/4HHXnikQV5AFNJJNJIvWMN/fJ5c49nLkW024=
域名中的 DKIM 公钥:
dig TXT +short s1024-2013-q3._domainkey.mail.instagram.com
"k=rsa; t=s; h=sha256; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQC7twdVo+BW8Pv2poU5129KYmE6npHdxUU8fktUKTE9TNovCvLy5LVjYc3TQcUFjOH" "VaZ89ZCjmpAcrA2QnTEKZ/2QWV56gn6bWdFW4SFxnQdHjguBZQykfKe5KTxy2a/OxuA0x2dHfdnYfw7RVzr4uednpKcWJy4Rl3gM6XB1zDwIDAQAB"
Python 编程中的应用
准备工作
- selector 选择 s2020 (随便)
- 生成密钥对
# 方法一:使用 OpenDKIM
sudo apt install -y opendkim-tools
opendkim-genkey --help
opendkim-genkey -D . -d markjour.com -s s2020
# 方法二:使用 OpenSSL
openssl genrsa -out dkim-markjour-s2020.pem 2048
openssl rsa -in dkim-markjour-s2020.pem -pubout -outform der 2>/dev/null | openssl base64 -A > dkim-markjour-s2020.pub
- 配置 DNS
s2020._domainkey.markjour.com
"v=DKIM1; h=sha256; k=rsa; p=MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAjnfmXLuyBt0Tus/Bdr87GpLqcRCZX7UC61OiPZ1Y3MG42qBcdEAMJfu7qop7KOLL8cywTRxiX39ehmf0ZovAXH1KkijiX/16tkI3cO9T6KS4vyr0Ip3fsGgNgjn5rH3M5AZAmbym6DIzYrtpTiAKgLYmFLALd9SLi/OhFIltWK+QJhaJgcuWUXCzlry01Fdsv1qj28WdZ6PQbQrSffc1qzkvOEOlmZXwWjQfg5X4E3DR4WKenC6f5WdcJeXk4pUeBOdQDoEM+4uCk4S6cN3OuYEQbvVmfQ5RAlCODccx7lJemWZZnlIf+03FppUEEMENZ8tu3iixD24m2q9wDLDL5QIDAQAB
v
版本,只有一种选择:DKIM1,必须放最前面,可以忽略
h
哈希算法,sha1 或者 sha256
k
密钥类型,只有一种选择:rsa,可以忽略
n
注释,可以忽略
p
公钥(ASN.1 DER-encoded + Base64)
s
服务类型,默认 *
,表示所有服务,可以忽略
t
逗号隔开的标记
- y 测试
- s DKIM 签名中的 i= 域名必须是 d= 域名完全相同(子域也不行)。
参考:
签名
headers = {'aaa': '123', 'bbb': '456', 'ccc': '789'}
校验签名
mail = """
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=qq.com; s=s0907;
t=1331524836; bh=Pqr4lbxMcef/3IqsXx/edT0iwPe18N7n8qKmQSnLio8=;
h=X-QQ-SSF:X-QQ-Spam:X-QQ-BUSINESS-ORIGIN:X-Originating-IP:
X-QQ-STYLE:X-QQ-mid:From:To:Sender:Subject:Mime-Version:
Content-Type:Content-Transfer-Encoding:Date:X-Priority:Message-ID:
X-QQ-MIME:X-Mailer:X-QQ-Mailer:X-QQ-ReplyHash;
b=hF3hXt429Mp9WUJx9wQQYYk32EABCQST/OmV+dI+vJ/XIidVkc6fsh8l/vBz/optb
MDp0XIupHHkUozz6jwMryhHd/ZNjLNtBBAIOgl1wH7R016x8uTtDQink5uIPH+5
X-QQ-SSF: 0000000000010060
X-QQ-Spam: true
X-QQ-BUSINESS-ORIGIN: 2
X-Originating-IP: 61.151.148.196
X-QQ-STYLE:
X-QQ-mid: bizmail6t1331524835t2734595
From: "=?gb18030?B?wffE6g==?=" <thinkphp@qq.com>
To: "=?gb18030?B?uvqwug==?=" <ninedoors@126.com>
Sender: liuchen@topthink.net
Subject: =?gb18030?B?UmU60rvOu1RQZXK1xNLJu/M=?=
Mime-Version: 1.0
Content-Type: multipart/alternative;
boundary="----=_NextPart_4F5D74E3_DF6406C0_249C5176"
Content-Transfer-Encoding: 8Bit
Date: Mon, 12 Mar 2012 12:00:35 +0800
X-Priority: 3
Message-ID: <tencent_519100472CAA258E750FA58D@qq.com>
X-QQ-MIME: TCMime 1.0 by Tencent
X-Mailer: QQMail 2.x
X-QQ-Mailer: QQMail 2.x
X-QQ-ReplyHash: 3949397671
""".strip('\n')
软件设计
2020-02-08
:) 本文正在编辑中,暂时不提供浏览...
Python 加密
2020-02-05
和 PyCrypto 的关系
PyCrypto 是 Python 界最知名的加密模块,它提供了一系列的加密算法,包括对称加密、非对称加密、哈希算法、签名算法等。
不过有一个很大的问题:上一个版本 2.6.1 发布于 2013-10-18,已经很多年没有维护了。
PyCryptodome 是 PyCrypto 的分叉,该项目在统一套代码的基础上提供了两种包:pycryptodome
和 pycryptodomex
:
- 前者保持对 PyCrypto 的兼容,所有的代码都在
Crypto
名称下,
-
后者丢掉了历史包袱,放弃对 PyCrypto 的兼容,所有代码都在 Cryptodome
名称下。
-
https://pypi.org/project/pycryptodome/
- https://pypi.org/project/pycryptodomex/
- https://www.pycryptodome.org/en/latest/
- https://github.com/Legrandin/pycryptodome/
其他加密模块
示例
云计算
2020-02-01
公有云 Public Cloud
云计算厂商提供的公共服务,比如腾讯云、阿里云等。
私有云 Private Cloud
个人和组织自己搭建的(可能是厂商帮忙搭建),自用的云计算服务。机器由个人或组织持有(可能在云计算厂商的机房中)。
优点是数据完全掌握在自己手中。如果规模没有达到一定程度的话,这样做的成本会比较高(除了固定资产的投入之外,维护成本不可忽视)。
有一种形式,是一些利益相关的个体,对计算有着相同或相似的需求,然后共同搭建一个云服务平台。
比如 XX 市弄一个政务云,下属政府机构直接使用这个云服务平台。
又比如某地教育单位弄一个云服务平台,下属学校有啥服务的话,就都使用这个云服务平台。
专有云
有些厂商提供 “专有云” 的服务:整套设施就为单个客户专门提供服务。设备属于厂商所有。
感觉这个算公有云,私有云的交叉地带,只好单独算一类。
知乎上看到一个比喻:公有云是小姐,私有云是老婆,专有云是小三 (包养)。比较贴切。
混合云 Hybrid Cloud
一般意义上,混合云是指公有云和私有云的结合。
PS: 多个私有云之间,或多个公有云之间,相互共享资源,也可以叫做混合云。
应该是通过一些对接好了各大厂商(比如腾讯云,阿里云)和常见私有云平台(比如 OpenStack,CloudStack)的管理工具,对所有资源进行整合管理。
也可能部分二次开发过的私有云平台支持对接公有云,可以直接调度和整合公有云资源。
开发者 ASN1 网络编程
2020-01-30
我印象中曾在某个项目中接触到了这种格式,但是一时间竟也想不起来。
PS: 可能是有一次涉及 LDAP 协议的时候。
概念
ASN 全名 Abstract Syntax Notation, 翻译过来就是:抽象语法标记。
ASN.1 可能是第一版的意思(?)。
asn.1 是一套国际标准,用来定义一种通用的、严谨的数据表示(标记)方法,以及对应的数据编码格式。
PS:对数据 Scheme 的定义独立于硬件架构和编程语言。
- ITU-T Rec. X.680 (2015) | ISO/IEC 8824-1:2015
Specification of basic notation
- ITU-T Rec. X.681 (2015) | ISO/IEC 8824-2:2015
Information object specification
- ITU-T Rec. X.682 (2015) | ISO/IEC 8824-3:2015
Constraint specification
- ITU-T Rec. X.683 (2015) | ISO/IEC 8824-4:2015
Parameterization of ASN.1 specifications
- ITU-T Rec. X.690 (2015) | ISO/IEC 8825-1:2015
BER, CER and DER
PS:常见证书格式 der 就是来自这个 DER。
- ITU-T Rec. X.691 (2015) | ISO/IEC 8825-2:2015
PER (Packed Encoding Rules)
- ITU-T Rec. X.692 (2015) | ISO/IEC 8825-3:2015
ECN (Extended Component Notation)
- ITU-T Rec. X.693 (2015) | ISO/IEC 8825-4:2015
XER (XML Encoding Rules)
- ITU-T Rec. X.694 (2015) | ISO/IEC 8825-5:2015
Mapping W3C XML schema definitions into ASN.1
- ITU-T Rec. X.695 (2015) | ISO/IEC 8825-6:2015
Registration and application of PER encoding instructions
- ITU-T Rec. X.696 (2015) | ISO/IEC 8825-7:2015
OER (Octet Encoding Rules)
- ITU-T Rec. X.697 (2017) | ISO/IEC 8825-8:2018
JER (JSON Encoding Rules)
一般又被称之为 X.680 系列,最早是 1995 年出第一版。最新的是 2018 年出的 5.4 版(X.680 (2015) Amd. 1)
PS:2021 年 X.680 出了第六版。
部分应用层的网络协议就使用了 ASN.1 格式,比如 X.500 Directory Services,LDAP,VoIP,PKCS,Kerberos,移动通信(2G/GSM,GRPS,一直到 5G)。
它和 JSON 这种通用数据交换格式完全不同,更加类似与 protobuf,msgpack,thrift 这样,提供一个完备的数据定义语法用来声明 Schema(ASN.1 称之为模块),然后基于二进制紧凑地表示数据。所以非常适合用在 C/S 架构的网络编程上,作为服务通讯协议的一部分,负责内外数据交换,也就是 TCP/UDP 服务的接口部分。
如果要将 ASN.1 归类的话,更贴切的应该是接口定义语言,或者叫协议定义语言。
要是了解到 ASN.1 出现的年份(1984)的话,对照它的竞争者出现的时间,会发现它的设计确实比较超前。不管怎么说,这些晚辈确实更加流行,作为国际标准的 ASN.1 不够卖座,肯定是也有不好的地方。
PS:可能是 ASN.1 历史包袱太重, 不够轻便 (我看到的一些评论和我的猜想比较符合)。
数据定义
先来一个示例(维基上找来的,感觉没啥意义):
FooProtocol DEFINITIONS ::= BEGIN
FooQuestion ::= SEQUENCE {
-- 跟踪编号,后面括号是限制值的范围
trackingNumber INTEGER(0..199),
-- 问题内容,字符串
question IA5String
}
FooAnswer ::= SEQUENCE {
-- 问题编号
questionNumber INTEGER(10..20),
-- 答案内容
answer BOOLEAN
}
FooHistory ::= SEQUENCE {
-- 问题数组
questions SEQUENCE(SIZE(0..10)) OF FooQuestion,
-- 答案数组
answers SEQUENCE(SIZE(1..10)) OF FooAnswer,
-- 一个整型数组
anArray SEQUENCE(SIZE(100)) OF INTEGER(0..1000),
...
}
END
基本语法
- 大小写字母,数字,短横杠,空格
标识符:小写字母开头
类型名称:大写字母开头
- 多个空白符号(空格、换行)会当作一个空格
- 数据类型都有一个 TagNumber
--
注释
数据类型
简单类型
结构化类型
标记类型
其他类型:CHOICE
,ANY
类别:
- 0 Universal 通用类型
- 1 Application 应用协议相关类型
- 2 Context-specific
- 3 Private 自定义
结构化:
原始类型:
Type |
Tag number |
备注 |
INTEGER |
2 |
整型 |
BIT STRING |
3 |
|
OCTET STRING |
4 |
|
NULL |
5 |
NULL |
OBJECT IDENTIFIER |
6 |
对象 |
SEQUENCE and
SEQUENCE OF |
16 |
数组 |
SET and SET OF |
17 |
集合 |
PrintableString |
19 |
字符串 |
T61String |
20 |
|
IA5String |
22 |
|
UTCTime |
23 |
时间 |
示例:
编码规则
- 基本编码规则(BER,Basic Encoding Rules)
- 规范编码规则(CER,Canonical Encoding Rules)
- 唯一编码规则(DER,Distinguished Encoding Rules)
- 压缩编码规则(PER,Packed Encoding Rules)
- XML 编码规则(XER,XML Encoding Rules)
Python
https://www.cnblogs.com/20175211lyz/p/12769883.html
https://github.com/etingof/pyasn1
上面的示例通过 asn1ate /tmp/foo.asn > /tmp/foo.py
生成 Python 代码:
PS:并不是一定需要定义成这样类的结构,只是 pyasn1 库适合这样用而已。
from pyasn1.type import univ, char, namedtype, namedval, tag, constraint, useful
class FooAnswer(univ.Sequence):
pass
FooAnswer.componentType = namedtype.NamedTypes(
namedtype.NamedType('questionNumber', univ.Integer().subtype(subtypeSpec=constraint.ValueRangeConstraint(10, 20))),
namedtype.NamedType('answer', univ.Boolean())
)
class FooQuestion(univ.Sequence):
pass
FooQuestion.componentType = namedtype.NamedTypes(
namedtype.NamedType('trackingNumber', univ.Integer().subtype(subtypeSpec=constraint.ValueRangeConstraint(0, 199))),
namedtype.NamedType('question', char.IA5String())
)
class FooHistory(univ.Sequence):
pass
FooHistory.componentType = namedtype.NamedTypes(
namedtype.NamedType('questions', univ.SequenceOf(componentType=FooQuestion()).subtype(subtypeSpec=constraint.ValueSizeConstraint(0, 10))),
namedtype.NamedType('answers', univ.SequenceOf(componentType=FooAnswer()).subtype(subtypeSpec=constraint.ValueSizeConstraint(1, 10))),
namedtype.NamedType('anArray', univ.SequenceOf(componentType=univ.Integer().subtype(subtypeSpec=constraint.ValueRangeConstraint(0, 1000))).subtype(subtypeSpec=constraint.ValueSizeConstraint(100, 100)))
)
然后就可以使用了:
import foo
from pyasn1.codec.der.encoder import encode
fa = foo.FooAnswer()
fa['questionNumber'] = 10
fa['answer'] = False
fa_encoded = encode(fa)
print(fa_encoded) # b'0\x06\x02\x01\n\x01\x01\x00'
print(binascii.b2a_hex(fa_encoded).decode()) # 300602010a010100
from pyasn1.codec.der.decoder import decode
obj, rest = decode(fa_encoded)
print(obj)
# Sequence:
# field-0=10
# field-1=False
for k, v in obj.items():
print([k, v])
# ['field-0', <Integer value object, tagSet <TagSet object, tags 0:0:2>, payload [10]>]
# ['field-1', <Boolean value object, tagSet <TagSet object, tags 0:0:1>, subtypeSpec <ConstraintsIntersection object, consts <SingleValueConstraint object, consts 0, 1>>, namedValues <NamedValues object, enums False=0, True=1>, payload [False]>]
obj, rest = decode(fa_encoded, asn1Spec=foo.FooAnswer())
print(obj)
# FooAnswer:
# questionNumber=10
# answer=False
# print(dict(obj.items()))
print(dict([(k, str(v)) for k, v in obj.items()]))
# {'questionNumber': '10', 'answer': 'False'}
print(obj['questionNumber'].__dict__)
print(obj['questionNumber']._value) # 10
print(obj['answer'].__dict__)
print(obj['answer']._value) # 0
print([int(obj['questionNumber']), bool(obj['answer'])])
GitHub 找到的几个相关库:
- wbond/asn1crypto
Python ASN.1 library with a focus on performance and a pythonic API
- etingof/pyasn1
Generic ASN.1 library for Python
- eerimoq/asn1tools
ASN.1 parsing, encoding and decoding.
- P1sec/pycrate
A Python library to ease the development of encoders and decoders for various protocols and file formats; contains ASN.1
参考资料与拓展阅读
时事 COVID19
2020-01-29
具体的情况,网上不要太多,我说说我的经历和想法。
MySQL
2020-01-25
输入 help 之后,按照提示就会发现 MySQL 有一个主题树。
从头 help contents 开始:
Account Management
ALTER RESOURCE GROUP
ALTER USER
CREATE RESOURCE GROUP
CREATE ROLE
CREATE USER
DROP RESOURCE GROUP
DROP ROLE
DROP USER
GRANT
RENAME USER
REVOKE
SET DEFAULT ROLE
SET PASSWORD
SET RESOURCE GROUP
SET ROLE
Administration
BINLOG
CACHE INDEX
FLUSH
HELP COMMAND
KILL
LOAD INDEX
RESET
RESET PERSIST
RESTART
SET
SET CHARACTER SET
SET CHARSET
SET NAMES
SHOW
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE SCHEMA
SHOW CREATE TABLE
SHOW CREATE TRIGGER
SHOW CREATE USER
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINE
SHOW ENGINES
SHOW ERRORS
SHOW EVENTS
SHOW FIELDS
SHOW FUNCTION CODE
SHOW FUNCTION STATUS
SHOW GRANTS
SHOW INDEX
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW OPEN TABLES
SHOW PLUGINS
SHOW PRIVILEGES
SHOW PROCEDURE CODE
SHOW PROCEDURE STATUS
SHOW PROCESSLIST
SHOW PROFILE
SHOW PROFILES
SHOW RELAYLOG EVENTS
SHOW REPLICA STATUS
SHOW REPLICAS
SHOW SCHEMAS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES
SHOW WARNINGS
SHUTDOWN
Components
CLONE
INSTALL COMPONENT
UNINSTALL COMPONENT
UNINSTALL PLUGIN
Compound Statements
BEGIN END
CASE STATEMENT
CLOSE
DECLARE CONDITION
DECLARE CURSOR
DECLARE HANDLER
DECLARE VARIABLE
FETCH
GET DIAGNOSTICS
IF STATEMENT
ITERATE
LABELS
LEAVE
LOOP
OPEN
REPEAT LOOP
RESIGNAL
RETURN
SIGNAL
WHILE
Contents
Data Definition
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER INSTANCE
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SCHEMA
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SCHEMA
CREATE SERVER
CREATE SPATIAL REFERENCE SYSTEM
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP SCHEMA
DROP SERVER
DROP SPATIAL REFERENCE SYSTEM
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP VIEW
FOREIGN KEY
RENAME TABLE
TRUNCATE TABLE
Data Manipulation
CALL
DELETE
DO
DUAL
HANDLER
IMPORT TABLE
INSERT
INSERT DELAYED
INSERT SELECT
JOIN
LOAD DATA
LOAD XML
PARENTHESIZED QUERY EXPRESSIONS
REPLACE
SELECT
TABLE
UNION
UPDATE
VALUES STATEMENT
Data Types
AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE
Functions
Aggregate Functions and Modifiers
AVG
BIT_AND
BIT_OR
BIT_XOR
COUNT
COUNT DISTINCT
GROUP_CONCAT
JSON_ARRAYAGG
JSON_OBJECTAGG
MAX
MIN
STD
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VARIANCE
VAR_POP
VAR_SAMP
Bit Functions
Cast Functions and Operators
BINARY OPERATOR
CAST
CONVERT
Comparison Operators
!=
<
<=
<=>
=
>
>=
BETWEEN AND
COALESCE
GREATEST
IN
INTERVAL
IS
IS NOT
IS NOT NULL
IS NULL
ISNULL
LEAST
NOT BETWEEN
NOT IN
Date and Time Functions
ADDDATE
ADDTIME
CONVERT_TZ
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE FUNCTION
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
FROM_UNIXTIME
GET_FORMAT
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME FUNCTION
TIMEDIFF
TIMESTAMP FUNCTION
TIMESTAMPADD
TIMESTAMPDIFF
TIME_FORMAT
TIME_TO_SEC
TO_DAYS
TO_SECONDS
UNIX_TIMESTAMP
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Encryption Functions
AES_DECRYPT
AES_ENCRYPT
COMPRESS
MD5
RANDOM_BYTES
SHA1
SHA2
STATEMENT_DIGEST
STATEMENT_DIGEST_TEXT
UNCOMPRESS
UNCOMPRESSED_LENGTH
VALIDATE_PASSWORD_STRENGTH
Enterprise Encryption Functions
ASYMMETRIC_DECRYPT
ASYMMETRIC_DERIVE
ASYMMETRIC_ENCRYPT
ASYMMETRIC_SIGN
ASYMMETRIC_VERIFY
CREATE_ASYMMETRIC_PRIV_KEY
CREATE_ASYMMETRIC_PUB_KEY
CREATE_DH_PARAMETERS
CREATE_DIGEST
Flow Control Functions
CASE OPERATOR
IF FUNCTION
IFNULL
NULLIF
GROUP BY Functions and Modifiers
GTID
GTID_SUBSET
GTID_SUBTRACT
WAIT_FOR_EXECUTED_GTID_SET
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
Information Functions
BENCHMARK
CHARSET
COERCIBILITY
COLLATION
CONNECTION_ID
CURRENT_ROLE
CURRENT_USER
DATABASE
FOUND_ROWS
ICU_VERSION
LAST_INSERT_ID
ROLES_GRAPHML
ROW_COUNT
SCHEMA
SESSION_USER
SYSTEM_USER
USER
VERSION
Internal Functions
CAN_ACCESS_COLUMN
CAN_ACCESS_DATABASE
CAN_ACCESS_TABLE
CAN_ACCESS_USER
CAN_ACCESS_VIEW
GET_DD_COLUMN_PRIVILEGES
GET_DD_CREATE_OPTIONS
GET_DD_INDEX_SUB_PART_LENGTH
INTERNAL_AUTO_INCREMENT
INTERNAL_AVG_ROW_LENGTH
INTERNAL_CHECKSUM
INTERNAL_CHECK_TIME
INTERNAL_DATA_FREE
INTERNAL_DATA_LENGTH
INTERNAL_DD_CHAR_LENGTH
INTERNAL_GET_COMMENT_OR_ERROR
INTERNAL_GET_ENABLED_ROLE_JSON
INTERNAL_GET_HOSTNAME
INTERNAL_GET_USERNAME
INTERNAL_GET_VIEW_WARNING_OR_ERROR
INTERNAL_INDEX_COLUMN_CARDINALITY
INTERNAL_INDEX_LENGTH
INTERNAL_IS_ENABLED_ROLE
INTERNAL_IS_MANDATORY_ROLE
INTERNAL_KEYS_DISABLED
INTERNAL_MAX_DATA_LENGTH
INTERNAL_TABLE_ROWS
INTERNAL_UPDATE_TIME
IS_VISIBLE_DD_OBJECT
Locking Functions
GET_LOCK
IS_FREE_LOCK
IS_USED_LOCK
RELEASE_ALL_LOCKS
RELEASE_LOCK
Logical Operators
!
AND
ASSIGN-EQUAL
ASSIGN-VALUE
OR
XOR
Miscellaneous Functions
ANY_VALUE
BIN_TO_UUID
DEFAULT
GROUPING
INET6_ATON
INET6_NTOA
INET_ATON
INET_NTOA
IS_IPV4
IS_IPV4_COMPAT
IS_IPV4_MAPPED
IS_IPV6
IS_UUID
MASTER_POS_WAIT
NAME_CONST
SLEEP
SOURCE_POS_WAIT
UUID
UUID_SHORT
UUID_TO_BIN
VALUES
Numeric Functions
%
*
+
- BINARY
- UNARY
/
ABS
ACOS
ASIN
ATAN
ATAN2
CEIL
CEILING
CONV
COS
COT
CRC32
DEGREES
DIV
EXP
FLOOR
LN
LOG
LOG10
LOG2
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
Performance Schema Functions
FORMAT_BYTES
FORMAT_PICO_TIME
PS_CURRENT_THREAD_ID
PS_THREAD_ID
Spatial Functions
Geometry Constructors
GEOMCOLLECTION
GEOMETRYCOLLECTION
LINESTRING
MULTILINESTRING
MULTIPOINT
MULTIPOLYGON
POINT
POLYGON
Geometry Property Functions
ST_DIMENSION
ST_ENVELOPE
ST_GEOMETRYTYPE
ST_ISEMPTY
ST_ISSIMPLE
ST_SRID
Geometry Relation Functions
ST_CONTAINS
ST_CROSSES
ST_DISJOINT
ST_DISTANCE
ST_EQUALS
ST_FRECHETDISTANCE
ST_HAUSDORFFDISTANCE
ST_INTERSECTS
ST_OVERLAPS
ST_TOUCHES
ST_WITHIN
GeometryCollection Property Functions
ST_BUFFER
ST_BUFFER_STRATEGY
ST_CONVEXHULL
ST_DIFFERENCE
ST_GEOMETRYN
ST_INTERSECTION
ST_LINEINTERPOLATEPOINT
ST_LINEINTERPOLATEPOINTS
ST_NUMGEOMETRIES
ST_POINTATDISTANCE
ST_SYMDIFFERENCE
ST_TRANSFORM
ST_UNION
LineString Property Functions
ST_ENDPOINT
ST_ISCLOSED
ST_LENGTH
ST_NUMPOINTS
ST_POINTN
ST_STARTPOINT
MBR Functions
->
->>
JSON_ARRAY
JSON_ARRAY_APPEND
JSON_ARRAY_INSERT
JSON_CONTAINS
JSON_CONTAINS_PATH
JSON_DEPTH
JSON_EXTRACT
JSON_INSERT
JSON_KEYS
JSON_LENGTH
JSON_MERGE
JSON_MERGE_PATCH()
JSON_MERGE_PRESERVE()
JSON_OBJECT
JSON_OVERLAPS
JSON_PRETTY
JSON_QUOTE
JSON_REMOVE
JSON_REPLACE
JSON_SCHEMA_VALID
JSON_SCHEMA_VALIDATION_REPORT
JSON_SEARCH
JSON_SET
JSON_STORAGE_FREE
JSON_STORAGE_SIZE
JSON_TABLE
JSON_TYPE
JSON_UNQUOTE
JSON_VALID
JSON_VALUE
MBRCONTAINS
MBRCOVEREDBY
MBRCOVERS
MBRDISJOINT
MBREQUALS
MBRINTERSECTS
MBROVERLAPS
MBRTOUCHES
MBRWITHIN
MEMBER OF
ST_ASGEOJSON
ST_COLLECT
ST_DISTANCE_SPHERE
ST_GEOHASH
ST_GEOMFROMGEOJSON
ST_ISVALID
ST_LATFROMGEOHASH
ST_LONGFROMGEOHASH
ST_MAKEENVELOPE
ST_POINTFROMGEOHASH
ST_SIMPLIFY
ST_VALIDATE
Point Property Functions
ST_LATITUDE
ST_LONGITUDE
ST_X
ST_Y
Polygon Property Functions
ST_AREA
ST_CENTROID
ST_EXTERIORRING
ST_INTERIORRINGN
ST_NUMINTERIORRINGS
WKB Functions
ST_ASBINARY
ST_ASTEXT
ST_GEOMCOLLFROMWKB
ST_GEOMFROMWKB
ST_LINEFROMWKB
ST_MLINEFROMWKB
ST_MPOINTFROMWKB
ST_MPOLYFROMWKB
ST_POINTFROMWKB
ST_POLYFROMWKB
ST_SWAPXY
WKT Functions
ST_GEOMCOLLFROMTEXT
ST_GEOMFROMTEXT
ST_LINEFROMTEXT
ST_MLINEFROMTEXT
ST_MPOINTFROMTEXT
ST_MPOLYFROMTEXT
ST_POINTFROMTEXT
ST_POLYFROMTEXT
String Functions
ASCII
BIN
BIT_LENGTH
CHAR FUNCTION
CHARACTER_LENGTH
CHAR_LENGTH
CONCAT
CONCAT_WS
ELT
EXPORT_SET
FIELD
FIND_IN_SET
FORMAT
FROM_BASE64
HEX
INSERT FUNCTION
INSTR
LCASE
LEFT
LENGTH
LIKE
LOAD_FILE
LOCATE
LOWER
LPAD
LTRIM
MAKE_SET
MATCH AGAINST
MID
NOT LIKE
NOT REGEXP
OCT
OCTET_LENGTH
ORD
POSITION
QUOTE
REGEXP
REGEXP_INSTR
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_SUBSTR
REPEAT FUNCTION
REPLACE FUNCTION
REVERSE
RIGHT
RPAD
RTRIM
SOUNDEX
SOUNDS LIKE
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TO_BASE64
TRIM
UCASE
UNHEX
UPPER
WEIGHT_STRING
Window Functions
CUME_DIST
DENSE_RANK
FIRST_VALUE
LAG
LAST_VALUE
LEAD
NTH_VALUE
NTILE
PERCENT_RANK
RANK
ROW_NUMBER
XML
Geographic Features
GEOMETRY HIERARCHY
SPATIAL COLUMNS
SPATIAL INDEXES
MBR
WKT
Help Metadata
HELP_DATE
HELP_VERSION
Language Structure
FALSE
TRUE
Loadable Functions
CREATE FUNCTION LOADABLE FUNCTION
DROP FUNCTION LOADABLE FUNCTION
Plugins
Prepared Statements
DEALLOCATE PREPARE
DROP PREPARE
EXECUTE STATEMENT
PREPARE
Replication Statements
CHANGE MASTER TO
CHANGE REPLICATION FILTER
CHANGE REPLICATION SOURCE TO
PURGE BINARY LOGS
PURGE MASTER LOGS
RESET MASTER
RESET REPLICA
RESET SLAVE
SET SQL_LOG_BIN
START REPLICA
START SLAVE
STOP REPLICA
STOP SLAVE
Storage Engines
Table Maintenance
ANALYZE TABLE
CHECK TABLE
CHECKSUM TABLE
OPTIMIZE TABLE
REPAIR TABLE
Transactions
BEGIN
COMMIT
LOCK INSTANCE FOR BACKUP
LOCK TABLES
RELEASE SAVEPOINT
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
SET TRANSACTION
START TRANSACTION
XA
Utility
DESC
DESCRIBE
EXPLAIN
HELP STATEMENT
USE
MySQL
2020-01-18
多线程执行 MySQL 查询的时候会遇到 Packet sequence number wrong 错误。
Traceback (most recent call last):
File "/tmp/db.py", line 167, in _execute
rowcount = cur.execute(sql, args)
File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 163, in execute
result = self._query(query)
File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
conn.query(q)
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 505, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 724, in _read_query_result
result.read()
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1076, in read
self._read_result_packet(first_packet)
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1146, in _read_result_packet
self._read_rowdata_packet()
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1180, in _read_rowdata_packet
packet = self.connection._read_packet()
File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 660, in _read_packet
% (packet_number, self._next_seq_id))
InternalError: Packet sequence number wrong - got 51 expected 178
多个查询共用连接,并发查询的时候,现在的 MySQL 网络传输机制无法判断这个返回属于哪个查询。
所以每个线程应该使用不同的连接,或者在 execute 之前加一个锁。
Python
2020-01-10
有同事排查 Python 项目问题的时候指出一处 open
没有关闭可能会导致句柄泄露 Handle Leak。
PS: 句柄泄漏的危害:大量资源占用可能导致性能下降,甚至由于可打开文件数达到极限,服务无法继续向外提供服务。
我看了之后告诉他,此处函数退出之后句柄会自动关闭,他还不信,下去自己研究了一会儿,可能是百度一下,过一会儿说好像确实是这样,不过他仍然很疑惑,那么 with open
的作用是什么呢?
一般我们常用上下文管理的方式(with open
)来打开文件,这样可以自动关闭句柄,这是一个好的实践。
- 退出函数之后文件描述符的自动关闭是 CPython GC (垃圾回收机制) 的特性,非 Python 语言规范。
- 根据 CPython 的 GC 策略(引用计数),如果有引用,文件描述符不会被关闭,这是一个非常严重的潜在风险。
大部分时候我们可能没有引用文件描述符,但是不能排除可能性。
万一出现句柄泄漏,在代码库中排查可能的未关闭引用会比较麻烦。
- 上下文管理会自动处理异常,相当于我们的
close
方法放在 finally
块中。
无关的事情:进程退出时的句柄
进程退出时如果有没有关闭的句柄,
- 如果程序正常退出,
- 语言可能会处理一道,清理相关数据
- 系统会处理一道
- 如果程序异常退出,则只有靠系统了
至少我看到 POSIX 中有相关规定,无论任何原因或任何方式的退出,都应该:
All of the file descriptors, directory streams, conversion descriptors, and message catalog descriptors open in the calling process shall be closed.
PS: 其中提到的:
- 文件描述符 File Descriptors
- 目录流 Directory Streams
- 转换描述符 Conversion Descriptors
- 消息编码描述符 Message Catalog Descriptors
后面三个是个啥?
Unix Linux Shell
2020-01-07
参考: https://en.wikipedia.org/wiki/Comparison_of_command_shells
Linux Shell
- Thompson shell (sh), 1971, Unix v1 - v6
只存在历史中
在 Bourne shell 之前,贝尔实验室还出了一个 Mashey shell,只出现在 1976 年的 PWB UNIX 中(所以又叫 PWB shell),没有大范围使用
- Bourne shell (sh), 1977, Unix v7
- C shell (csh), 1978

- TENEX C shell (tcsh), 1983

是 TENEX 系统开发的 csh 兼容 Shell
- Korn shell (ksh), 1983

- Bash, 1989

- Almquist shell (ash), 1989

- zsh, 1990

- Debian Almquist shell (dash), an ash fork, 1997
主体随 ash 是 BSD 协议,不过,可能是为了保持对 bash 的兼容,采用了 bash 项目的一个文件
- fish, 2005

分类
- ksh, bash, zsh, ash, dash 都是 Bourne shell 兼容。
- csh, tcsh 就是 csh 兼容。
- fish
说明
- Unix 上的 sh 有版权问题, 所以 BSD 和 Linux 上的
/bin/sh
都指向某一种兼容 Shell (一般是默认 Shell)
- RHEL/CentOS 上,默认 Shell 是 Bash
- Debian/Ubuntu 上,默认 Shell 是 Dash
- FreeBSD 上默认采用 tcsh, 基于 FreeBSD 的 GhostBSD 采用 fish
- OpenBSD 上默认采用 pdksh (pd: Public Domain), 不知道和 tsh 什么关系。可能是 OpenBSD 维护的 tsh 兼容 Shell。
- NetBSD 上默认采用 ash
- 由于 ash 非常轻量级,BusyBox 采用了 dash fork
然后 BusyBox 被 Alpine Linux, Tiny Core Linux 或者其他嵌入式 Linux (比如 OpenWrt) 采用,所以这些系统的默认 Shell 应该就是 ash
所以可能使用最广泛的 Shell 是 ash 和 bash。
其他:MacOS 上曾经默认采用 bash, 后来专向了 zsh
Windows 平台
- command.com, 1980
- cmd.exe, 1993
- PowerShell, 2006