API tools faq
paste
Guest User

QwenTest

a guest
May 28th, 2026
159
0
Never
7
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 48.23 KB | None | 0 0
raw download clone embed print report
  1. I ran llama-server on the MTP PR branch:
  2.  
  3. am17an-llama.cpp/build/bin/llama-server \
  4. --model ~/ggufs/Qwen3.6-35B-A3B-UD-Q4_K_M.gguf \
  5. --ctx-size 262144 \
  6. --temp 0.6 \
  7. --top-p 0.95 \
  8. --top-k 20 \
  9. --min-p 0.00 \
  10. --port 9999
  11.  
  12. on my M3 MacBook Pro with 36GB memory. Here's a copy of the pi session I cleaned up a little bit to extract message text, tool call arguments, and commands.
  13.  
  14. {"type":"session","version":3,"id":"019e6c52-e2ee-7726-9fe5-52452de53bfe","timestamp":"2026-05-28T02:03:51.919Z","cwd":"/workspace/qwen-test"}
  15. {"type":"model_change","id":"99d77384","parentId":null,"timestamp":"2026-05-28T02:03:51.942Z","provider":"llama-server","modelId":"Qwen3.6-35B-A3B-UD-Q4_K_M.gguf"}
  16. {"type":"thinking_level_change","id":"e0d0b636","parentId":"99d77384","timestamp":"2026-05-28T02:03:51.942Z","thinkingLevel":"medium"}
  17. Prompt: Use python3 to experiment with whether the SQLite3 authorizer mechanism can be used to detect an INSERT OR REPLACE based just on running an explain query without examining the SQL string itself
  18. I'll write a Python script to experiment with SQLite3's authorizer and EXPLAIN to see if we can detect `INSERT OR REPLACE` from opcodes alone.
  19. ```
  20. #!/usr/bin/env python3
  21. """
  22. Experiment: Can SQLite3's authorizer detect INSERT OR REPLACE
  23. just by examining EXPLAIN opcodes, without parsing the SQL string?
  24. """
  25.  
  26. import sqlite3
  27. import sys
  28.  
  29. # ── Step 1: What opcodes does INSERT OR REPLACE produce? ──────────────────────
  30.  
  31. print("=" * 70)
  32. print("STEP 1: Examine EXPLAIN output for INSERT OR REPLACE")
  33. print("=" * 70)
  34.  
  35. conn = sqlite3.connect(":memory:")
  36. conn.execute("""
  37. CREATE TABLE test_table (
  38. id INTEGER PRIMARY KEY,
  39. name TEXT,
  40. value INTEGER
  41. )
  42. """)
  43.  
  44. print("\n--- INSERT OR REPLACE ---")
  45. for row in conn.execute("EXPLAIN INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  46. print(row)
  47.  
  48. print("\n--- INSERT (conflict clause: ABORT) ---")
  49. for row in conn.execute("EXPLAIN INSERT INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  50. print(row)
  51.  
  52. print("\n--- INSERT OR IGNORE ---")
  53. for row in conn.execute("EXPLAIN INSERT OR IGNORE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  54. print(row)
  55.  
  56. print("\n--- INSERT OR FAIL ---")
  57. for row in conn.execute("EXPLAIN INSERT OR FAIL INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  58. print(row)
  59.  
  60. print("\n--- INSERT OR ROLLBACK ---")
  61. for row in conn.execute("EXPLAIN INSERT OR ROLLBACK INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  62. print(row)
  63.  
  64. print("\n--- INSERT OR REPLACE with conflict target on name ---")
  65. for row in conn.execute("EXPLAIN INSERT OR REPLACE INTO test_table (name, value) VALUES ('hello', 42)"):
  66. print(row)
  67.  
  68. conn.close()
  69.  
  70. # ── Step 2: Can we use the authorizer on the EXPLAIN itself? ──────────────────
  71.  
  72. print("\n" + "=" * 70)
  73. print("STEP 2: Authorizer on EXPLAIN — does it fire for the sub-tables?")
  74. print("=" * 70)
  75.  
  76. def authorizer(op, p1, p2, p3, p4):
  77. """
  78. op: action code (see sqlite3.SQLITE_*)
  79. p1: database name or filename
  80. p2: table name or column name
  81. p3: trigger or view name
  82. p4: trigger or index name
  83. """
  84. print(f" AUTHORIZE: op={op:3d} p1={p1!r:12s} p2={p2!r:12s} p3={p3!r:12s} p4={p4!r}")
  85. return sqlite3.SQLITE_OK
  86.  
  87. conn = sqlite3.connect(":memory:")
  88. conn.set_authorizer(authorizer)
  89.  
  90. conn.execute("""
  91. CREATE TABLE test_table (
  92. id INTEGER PRIMARY KEY,
  93. name TEXT,
  94. value INTEGER
  95. )
  96. """)
  97.  
  98. print("\n--- EXPLAIN INSERT OR REPLACE ---")
  99. try:
  100. for row in conn.execute("EXPLAIN INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  101. pass
  102. except Exception as e:
  103. print(f" Error: {e}")
  104.  
  105. print("\n--- EXPLAIN INSERT (no conflict clause) ---")
  106. try:
  107. for row in conn.execute("EXPLAIN INSERT INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  108. pass
  109. except Exception as e:
  110. print(f" Error: {e}")
  111.  
  112. conn.close()
  113.  
  114. # ── Step 3: Authorizer on actual INSERT OR REPLACE execution ──────────────────
  115.  
  116. print("\n" + "=" * 70)
  117. print("STEP 3: Authorizer on actual INSERT OR REPLACE execution")
  118. print("=" * 70)
  119.  
  120. def authorizer2(op, p1, p2, p3, p4):
  121. action_names = {
  122. sqlite3.SQLITE_DELETE: "DELETE",
  123. sqlite3.SQLITE_INSERT: "INSERT",
  124. sqlite3.SQLITE_READ: "READ",
  125. sqlite3.SQLITE_SELECT: "SELECT",
  126. sqlite3.SQLITE_TRANSACTION: "TRANSACTION",
  127. sqlite3.SQLITE_ATTACH: "ATTACH",
  128. sqlite3.SQLITE_ALTER_TABLE: "ALTER_TABLE",
  129. sqlite3.SQLITE_CREATE_INDEX: "CREATE_INDEX",
  130. sqlite3.SQLITE_CREATE_TABLE: "CREATE_TABLE",
  131. sqlite3.SQLITE_CREATE_TEMP_INDEX: "CREATE_TEMP_INDEX",
  132. sqlite3.SQLITE_CREATE_TEMP_TABLE: "CREATE_TEMP_TABLE",
  133. sqlite3.SQLITE_CREATE_TEMP_TRIGGER: "CREATE_TEMP_TRIGGER",
  134. sqlite3.SQLITE_CREATE_TEMP_VIEW: "CREATE_TEMP_VIEW",
  135. sqlite3.SQLITE_CREATE_TRIGGER: "CREATE_TRIGGER",
  136. sqlite3.SQLITE_CREATE_VIEW: "CREATE_VIEW",
  137. sqlite3.SQLITE_DROP_INDEX: "DROP_INDEX",
  138. sqlite3.SQLITE_DROP_TABLE: "DROP_TABLE",
  139. sqlite3.SQLITE_DROP_TEMP_INDEX: "DROP_TEMP_INDEX",
  140. sqlite3.SQLITE_DROP_TEMP_TABLE: "DROP_TEMP_TABLE",
  141. sqlite3.SQLITE_DROP_TEMP_TRIGGER: "DROP_TEMP_TRIGGER",
  142. sqlite3.SQLITE_DROP_TEMP_VIEW: "DROP_TEMP_VIEW",
  143. sqlite3.SQLITE_DROP_TRIGGER: "DROP_TRIGGER",
  144. sqlite3.SQLITE_DROP_VIEW: "DROP_VIEW",
  145. sqlite3.SQLITE_REINDEX: "REINDEX",
  146. sqlite3.SQLITE_COPY: "COPY",
  147. sqlite3.SQLITE_PRAGMA: "PRAGMA",
  148. sqlite3.SQLITE_AUTH: "AUTH",
  149. sqlite3.SQLITE_FUNCTION: "FUNCTION",
  150. sqlite3.SQLITE_SAVEPOINT: "SAVEPOINT",
  151. sqlite3.SQLITE_CREATE_VTABLE: "CREATE_VTABLE",
  152. sqlite3.SQLITE_DROP_VTABLE: "DROP_VTABLE",
  153. sqlite3.SQLITE_RECURSIVE: "RECURSIVE",
  154. }
  155. name = action_names.get(op, f"UNKNOWN({op})")
  156. print(f" AUTHORIZE: {name:15s} p1={p1!r:12s} p2={p2!r:12s} p3={p3!r:12s} p4={p4!r}")
  157. return sqlite3.SQLITE_OK
  158.  
  159. conn = sqlite3.connect(":memory:")
  160. conn.set_authorizer(authorizer2)
  161.  
  162. conn.execute("""
  163. CREATE TABLE test_table (
  164. id INTEGER PRIMARY KEY,
  165. name TEXT,
  166. value INTEGER
  167. )
  168. """)
  169.  
  170. print("\n--- INSERT OR REPLACE INTO test_table ---")
  171. try:
  172. conn.execute("INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)")
  173. except Exception as e:
  174. print(f" Error: {e}")
  175.  
  176. print("\n--- INSERT INTO test_table (no conflict) ---")
  177. try:
  178. conn.execute("INSERT INTO test_table (id, name, value) VALUES (2, 'world', 99)")
  179. except Exception as e:
  180. print(f" Error: {e}")
  181.  
  182. print("\n--- REPLACE INTO test_table (shorthand) ---")
  183. try:
  184. conn.execute("REPLACE INTO test_table (id, name, value) VALUES (1, 'replaced', 100)")
  185. except Exception as e:
  186. print(f" Error: {e}")
  187.  
  188. conn.close()
  189.  
  190. # ── Step 4: EXPLAIN of REPLACE (shorthand) ───────────────────────────────────
  191.  
  192. print("\n" + "=" * 70)
  193. print("STEP 4: EXPLAIN of REPLACE (shorthand) vs INSERT OR REPLACE")
  194. print("=" * 70)
  195.  
  196. conn = sqlite3.connect(":memory:")
  197. conn.execute("""
  198. CREATE TABLE test_table (
  199. id INTEGER PRIMARY KEY,
  200. name TEXT,
  201. value INTEGER
  202. )
  203. """)
  204.  
  205. print("\n--- EXPLAIN REPLACE ---")
  206. for row in conn.execute("EXPLAIN REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  207. print(row)
  208.  
  209. print("\n--- EXPLAIN INSERT OR REPLACE ---")
  210. for row in conn.execute("EXPLAIN INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  211. print(row)
  212.  
  213. conn.close()
  214.  
  215. # ── Step 5: Authorizer on EXPLAIN of REPLACE ─────────────────────────────────
  216.  
  217. print("\n" + "=" * 70)
  218. print("STEP 5: Authorizer on EXPLAIN of REPLACE")
  219. print("=" * 70)
  220.  
  221. conn = sqlite3.connect(":memory:")
  222. conn.set_authorizer(authorizer)
  223.  
  224. conn.execute("""
  225. CREATE TABLE test_table (
  226. id INTEGER PRIMARY KEY,
  227. name TEXT,
  228. value INTEGER
  229. )
  230. """)
  231.  
  232. print("\n--- EXPLAIN REPLACE ---")
  233. try:
  234. for row in conn.execute("EXPLAIN REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  235. pass
  236. except Exception as e:
  237. print(f" Error: {e}")
  238.  
  239. conn.close()
  240.  
  241. # ── Step 6: Can we detect from EXPLAIN opcodes alone? ────────────────────────
  242.  
  243. print("\n" + "=" * 70)
  244. print("STEP 6: Detecting INSERT OR REPLACE from EXPLAIN opcodes")
  245. print("=" * 70)
  246.  
  247. def get_explain_opcodes(sql):
  248. conn = sqlite3.connect(":memory:")
  249. conn.execute("""
  250. CREATE TABLE test_table (
  251. id INTEGER PRIMARY KEY,
  252. name TEXT,
  253. value INTEGER
  254. )
  255. """)
  256. opcodes = []
  257. for row in conn.execute(f"EXPLAIN {sql}"):
  258. opcodes.append({
  259. 'opcode': row[1],
  260. 'p1': row[2],
  261. 'p2': row[3],
  262. 'p3': row[4],
  263. 'p4': row[5],
  264. 'p5': row[6],
  265. 'comment': row[7],
  266. })
  267. conn.close()
  268. return opcodes
  269.  
  270. queries = [
  271. ("INSERT INTO test_table (id, name, value) VALUES (1, 'a', 1)", "plain INSERT"),
  272. ("INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR REPLACE"),
  273. ("INSERT OR IGNORE INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR IGNORE"),
  274. ("INSERT OR FAIL INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR FAIL"),
  275. ("INSERT OR ROLLBACK INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR ROLLBACK"),
  276. ("REPLACE INTO test_table (id, name, value) VALUES (1, 'a', 1)", "REPLACE"),
  277. ]
  278.  
  279. for sql, label in queries:
  280. print(f"\n--- {label} ---")
  281. opcodes = get_explain_opcodes(sql)
  282. for op in opcodes:
  283. print(f" {op['opcode']:20s} p1={op['p1']:3d} p2={op['p2']:3d} p3={op['p3']:3d} p4={op['p4']} p5={op['p5']}")
  284.  
  285. # ── Step 7: Key question — is there a unique opcode signature? ───────────────
  286.  
  287. print("\n" + "=" * 70)
  288. print("STEP 7: Looking for unique opcode signatures")
  289. print("=" * 70)
  290.  
  291. # Compare opcodes across all variants
  292. all_opcodes = {}
  293. for sql, label in queries:
  294. opcodes = get_explain_opcodes(sql)
  295. all_opcodes[label] = [(op['opcode'], op['p5']) for op in opcodes]
  296.  
  297. # Find differences
  298. labels = list(all_opcodes.keys())
  299. for i in range(len(labels)):
  300. for j in range(i+1, len(labels)):
  301. l1, l2 = labels[i], labels[j]
  302. o1, o2 = all_opcodes[l1], all_opcodes[l2]
  303. diffs = []
  304. for k in range(max(len(o1), len(o2))):
  305. if k >= len(o1):
  306. diffs.append(f" pos {k}: {l1}=MISSING, {l2}={o2[k]}")
  307. elif k >= len(o2):
  308. diffs.append(f" pos {k}: {l1}={o1[k]}, {l2}=MISSING")
  309. elif o1[k] != o2[k]:
  310. diffs.append(f" pos {k}: {l1}={o1[k]}, {l2}={o2[k]}")
  311. if diffs:
  312. print(f"\n{labels[i]:30s} vs {labels[j]:30s}")
  313. for d in diffs:
  314. print(d)
  315.  
  316. conn.close()
  317. print("\nDone!")
  318. ```
  319. $ cd /workspace/qwen-test && python3 sqlite_authorizer_explain.py 2>&1
  320. /bin/bash: line 1: python3: command not found
  321. Command exited with code 127
  322. $ which python3 || which python || ls /usr/bin/python* 2>/dev/null || ls /usr/local/bin/python* 2>/dev/null
  323. (no output)
  324. Command exited with code 2
  325. $ apt list --installed 2>/dev/null | grep -i python; conda list 2>/dev/null | grep -i python; find / -name "python3*" -type f 2>/dev/null | head -20
  326. Command timed out after 15 seconds
  327. (prompt: you can use uv I think)
  328. $ cd /workspace/qwen-test && uv run python3 sqlite_authorizer_explain.py 2>&1
  329. error: No interpreter found in virtual environments, managed installations, or search path
  330. hint: A managed Python download is available, but uv is set to offline mode
  331. Command exited with code 2
  332. $ uv python install 3.11 2>&1
  333. error: Failed to install cpython-3.11.15-linux-aarch64-gnu
  334. Caused by: Failed to download https://github.com/astral-sh/python-build-standalone/releases/download/20260414/cpython-3.11.15%2B20260414-aarch64-unknown-linux-gnu-install_only_stripped.tar.gz
  335. Caused by: Network connectivity is disabled, but the requested data wasn't found in the cache for: `https://github.com/astral-sh/python-build-standalone/releases/download/20260414/cpython-3.11.15%2B20260414-aarch64-unknown-linux-gnu-install_only_stripped.tar.gz`
  336. Command exited with code 1
  337. $ find / -name "python3*" -type f 2>/dev/null; find / -name "python*" -type f 2>/dev/null | head -30
  338. Command timed out after 10 seconds
  339. $ ls /usr/bin/python* /usr/local/bin/python* /opt/python* 2>/dev/null; ls ~/.local/bin/python* 2>/dev/null; env | grep -i python; cat /etc/os-release 2>/dev/null | head -5
  340. PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
  341. NAME="Debian GNU/Linux"
  342. VERSION_ID="12"
  343. VERSION="12 (bookworm)"
  344. VERSION_CODENAME=bookworm
  345. $ apt-get update -qq && apt-get install -y -qq python3 2>&1 | tail -5
  346. Setting up libpython3.11-stdlib:arm64 (3.11.2-6+deb12u7) ...
  347. Setting up libpython3-stdlib:arm64 (3.11.2-1+b1) ...
  348. Setting up python3.11 (3.11.2-6+deb12u7) ...
  349. Setting up python3 (3.11.2-1+b1) ...
  350. Processing triggers for libc-bin (2.36-9+deb12u13) ...
  351. $ cd /workspace/qwen-test && python3 sqlite_authorizer_explain.py 2>&1
  352. ======================================================================
  353. STEP 1: Examine EXPLAIN output for INSERT OR REPLACE
  354. ======================================================================
  355.  
  356. --- INSERT OR REPLACE ---
  357. (0, 'Init', 0, 14, 0, None, 0, None)
  358. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  359. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  360. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  361. (4, 'Integer', 42, 4, 0, None, 0, None)
  362. (5, 'Integer', 1, 1, 0, None, 0, None)
  363. (6, 'NotNull', 1, 8, 0, None, 0, None)
  364. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  365. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  366. (9, 'NotExists', 0, 11, 1, None, 0, None)
  367. (10, 'Delete', 0, 64, 0, 'test_table', 0, None)
  368. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  369. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  370. (13, 'Halt', 0, 0, 0, None, 0, None)
  371. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  372. (15, 'Goto', 0, 1, 0, None, 0, None)
  373.  
  374. --- INSERT (conflict clause: ABORT) ---
  375. (0, 'Init', 0, 14, 0, None, 0, None)
  376. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  377. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  378. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  379. (4, 'Integer', 42, 4, 0, None, 0, None)
  380. (5, 'Integer', 1, 1, 0, None, 0, None)
  381. (6, 'NotNull', 1, 8, 0, None, 0, None)
  382. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  383. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  384. (9, 'NotExists', 0, 11, 1, None, 0, None)
  385. (10, 'Halt', 1555, 2, 0, 'test_table.id', 2, None)
  386. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  387. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  388. (13, 'Halt', 0, 0, 0, None, 0, None)
  389. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  390. (15, 'Goto', 0, 1, 0, None, 0, None)
  391.  
  392. --- INSERT OR IGNORE ---
  393. (0, 'Init', 0, 14, 0, None, 0, None)
  394. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  395. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  396. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  397. (4, 'Integer', 42, 4, 0, None, 0, None)
  398. (5, 'Integer', 1, 1, 0, None, 0, None)
  399. (6, 'NotNull', 1, 8, 0, None, 0, None)
  400. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  401. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  402. (9, 'NotExists', 0, 11, 1, None, 0, None)
  403. (10, 'Goto', 0, 13, 0, None, 0, None)
  404. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  405. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  406. (13, 'Halt', 0, 0, 0, None, 0, None)
  407. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  408. (15, 'Goto', 0, 1, 0, None, 0, None)
  409.  
  410. --- INSERT OR FAIL ---
  411. (0, 'Init', 0, 14, 0, None, 0, None)
  412. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  413. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  414. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  415. (4, 'Integer', 42, 4, 0, None, 0, None)
  416. (5, 'Integer', 1, 1, 0, None, 0, None)
  417. (6, 'NotNull', 1, 8, 0, None, 0, None)
  418. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  419. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  420. (9, 'NotExists', 0, 11, 1, None, 0, None)
  421. (10, 'Halt', 1555, 3, 0, 'test_table.id', 2, None)
  422. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  423. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  424. (13, 'Halt', 0, 0, 0, None, 0, None)
  425. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  426. (15, 'Goto', 0, 1, 0, None, 0, None)
  427.  
  428. --- INSERT OR ROLLBACK ---
  429. (0, 'Init', 0, 14, 0, None, 0, None)
  430. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  431. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  432. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  433. (4, 'Integer', 42, 4, 0, None, 0, None)
  434. (5, 'Integer', 1, 1, 0, None, 0, None)
  435. (6, 'NotNull', 1, 8, 0, None, 0, None)
  436. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  437. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  438. (9, 'NotExists', 0, 11, 1, None, 0, None)
  439. (10, 'Halt', 1555, 1, 0, 'test_table.id', 2, None)
  440. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  441. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  442. (13, 'Halt', 0, 0, 0, None, 0, None)
  443. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  444. (15, 'Goto', 0, 1, 0, None, 0, None)
  445.  
  446. --- INSERT OR REPLACE with conflict target on name ---
  447. (0, 'Init', 0, 9, 0, None, 0, None)
  448. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  449. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  450. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  451. (4, 'Integer', 42, 4, 0, None, 0, None)
  452. (5, 'NewRowid', 0, 1, 0, None, 0, None)
  453. (6, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  454. (7, 'Insert', 0, 5, 1, 'test_table', 57, None)
  455. (8, 'Halt', 0, 0, 0, None, 0, None)
  456. (9, 'Transaction', 0, 1, 1, '0', 1, None)
  457. (10, 'Goto', 0, 1, 0, None, 0, None)
  458.  
  459. ======================================================================
  460. STEP 2: Authorizer on EXPLAIN — does it fire for the sub-tables?
  461. ======================================================================
  462. AUTHORIZE: op= 18 p1='sqlite_master' p2=None p3='main' p4=None
  463. AUTHORIZE: op= 2 p1='test_table' p2=None p3='main' p4=None
  464. AUTHORIZE: op= 23 p1='sqlite_master' p2='type' p3='main' p4=None
  465. AUTHORIZE: op= 23 p1='sqlite_master' p2='name' p3='main' p4=None
  466. AUTHORIZE: op= 23 p1='sqlite_master' p2='tbl_name' p3='main' p4=None
  467. AUTHORIZE: op= 23 p1='sqlite_master' p2='rootpage' p3='main' p4=None
  468. AUTHORIZE: op= 23 p1='sqlite_master' p2='sql' p3='main' p4=None
  469. AUTHORIZE: op= 20 p1='sqlite_master' p2='ROWID' p3='main' p4=None
  470.  
  471. --- EXPLAIN INSERT OR REPLACE ---
  472. AUTHORIZE: op= 18 p1='test_table' p2=None p3='main' p4=None
  473.  
  474. --- EXPLAIN INSERT (no conflict clause) ---
  475. AUTHORIZE: op= 18 p1='test_table' p2=None p3='main' p4=None
  476.  
  477. ======================================================================
  478. STEP 3: Authorizer on actual INSERT OR REPLACE execution
  479. ======================================================================
  480. Traceback (most recent call last):
  481. File "/workspace/qwen-test/sqlite_authorizer_explain.py", line 143, in <module>
  482. conn.execute("""
  483. sqlite3.DatabaseError: not authorized
  484. Command exited with code 1
  485. The authorizer blocked the CREATE TABLE. Let me fix the script to handle that and continue the experiment:
  486. ```
  487. #!/usr/bin/env python3
  488. """
  489. Experiment: Can SQLite3's authorizer detect INSERT OR REPLACE
  490. just by examining EXPLAIN opcodes, without parsing the SQL string?
  491. """
  492.  
  493. import sqlite3
  494.  
  495. # ── Step 1: What opcodes does INSERT OR REPLACE produce? ──────────────────────
  496.  
  497. print("=" * 70)
  498. print("STEP 1: Examine EXPLAIN output for INSERT OR REPLACE")
  499. print("=" * 70)
  500.  
  501. conn = sqlite3.connect(":memory:")
  502. conn.execute("""
  503. CREATE TABLE test_table (
  504. id INTEGER PRIMARY KEY,
  505. name TEXT,
  506. value INTEGER
  507. )
  508. """)
  509.  
  510. print("\n--- INSERT OR REPLACE ---")
  511. for row in conn.execute("EXPLAIN INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  512. print(row)
  513.  
  514. print("\n--- INSERT (conflict clause: ABORT) ---")
  515. for row in conn.execute("EXPLAIN INSERT INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  516. print(row)
  517.  
  518. print("\n--- INSERT OR IGNORE ---")
  519. for row in conn.execute("EXPLAIN INSERT OR IGNORE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  520. print(row)
  521.  
  522. print("\n--- INSERT OR REPLACE with conflict target on name ---")
  523. for row in conn.execute("EXPLAIN INSERT OR REPLACE INTO test_table (name, value) VALUES ('hello', 42)"):
  524. print(row)
  525.  
  526. conn.close()
  527.  
  528. # ── Step 2: Authorizer on actual INSERT OR REPLACE execution ──────────────────
  529.  
  530. print("\n" + "=" * 70)
  531. print("STEP 2: Authorizer on actual INSERT OR REPLACE execution")
  532. print("=" * 70)
  533.  
  534. def authorizer(op, p1, p2, p3, p4):
  535. action_names = {
  536. sqlite3.SQLITE_DELETE: "DELETE",
  537. sqlite3.SQLITE_INSERT: "INSERT",
  538. sqlite3.SQLITE_READ: "READ",
  539. sqlite3.SQLITE_SELECT: "SELECT",
  540. sqlite3.SQLITE_TRANSACTION: "TRANSACTION",
  541. sqlite3.SQLITE_ATTACH: "ATTACH",
  542. sqlite3.SQLITE_ALTER_TABLE: "ALTER_TABLE",
  543. sqlite3.SQLITE_CREATE_INDEX: "CREATE_INDEX",
  544. sqlite3.SQLITE_CREATE_TABLE: "CREATE_TABLE",
  545. sqlite3.SQLITE_CREATE_TEMP_INDEX: "CREATE_TEMP_INDEX",
  546. sqlite3.SQLITE_CREATE_TEMP_TABLE: "CREATE_TEMP_TABLE",
  547. sqlite3.SQLITE_CREATE_TEMP_TRIGGER: "CREATE_TEMP_TRIGGER",
  548. sqlite3.SQLITE_CREATE_TEMP_VIEW: "CREATE_TEMP_VIEW",
  549. sqlite3.SQLITE_CREATE_TRIGGER: "CREATE_TRIGGER",
  550. sqlite3.SQLITE_CREATE_VIEW: "CREATE_VIEW",
  551. sqlite3.SQLITE_DROP_INDEX: "DROP_INDEX",
  552. sqlite3.SQLITE_DROP_TABLE: "DROP_TABLE",
  553. sqlite3.SQLITE_DROP_TEMP_INDEX: "DROP_TEMP_INDEX",
  554. sqlite3.SQLITE_DROP_TEMP_TABLE: "DROP_TEMP_TABLE",
  555. sqlite3.SQLITE_DROP_TEMP_TRIGGER: "DROP_TEMP_TRIGGER",
  556. sqlite3.SQLITE_DROP_TEMP_VIEW: "DROP_TEMP_VIEW",
  557. sqlite3.SQLITE_DROP_TRIGGER: "DROP_TRIGGER",
  558. sqlite3.SQLITE_DROP_VIEW: "DROP_VIEW",
  559. sqlite3.SQLITE_REINDEX: "REINDEX",
  560. sqlite3.SQLITE_COPY: "COPY",
  561. sqlite3.SQLITE_PRAGMA: "PRAGMA",
  562. sqlite3.SQLITE_AUTH: "AUTH",
  563. sqlite3.SQLITE_FUNCTION: "FUNCTION",
  564. sqlite3.SQLITE_SAVEPOINT: "SAVEPOINT",
  565. sqlite3.SQLITE_CREATE_VTABLE: "CREATE_VTABLE",
  566. sqlite3.SQLITE_DROP_VTABLE: "DROP_VTABLE",
  567. sqlite3.SQLITE_RECURSIVE: "RECURSIVE",
  568. }
  569. name = action_names.get(op, f"UNKNOWN({op})")
  570. print(f" AUTHORIZE: {name:15s} p1={p1!r:12s} p2={p2!r:12s} p3={p3!r:12s} p4={p4!r}")
  571. return sqlite3.SQLITE_OK
  572.  
  573. conn = sqlite3.connect(":memory:")
  574. conn.set_authorizer(authorizer)
  575.  
  576. conn.execute("""
  577. CREATE TABLE test_table (
  578. id INTEGER PRIMARY KEY,
  579. name TEXT,
  580. value INTEGER
  581. )
  582. """)
  583.  
  584. print("\n--- INSERT OR REPLACE INTO test_table ---")
  585. try:
  586. conn.execute("INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)")
  587. except Exception as e:
  588. print(f" Error: {e}")
  589.  
  590. print("\n--- INSERT INTO test_table (no conflict) ---")
  591. try:
  592. conn.execute("INSERT INTO test_table (id, name, value) VALUES (2, 'world', 99)")
  593. except Exception as e:
  594. print(f" Error: {e}")
  595.  
  596. print("\n--- REPLACE INTO test_table (shorthand) ---")
  597. try:
  598. conn.execute("REPLACE INTO test_table (id, name, value) VALUES (3, 'replaced', 100)")
  599. except Exception as e:
  600. print(f" Error: {e}")
  601.  
  602. conn.close()
  603.  
  604. # ── Step 3: EXPLAIN of REPLACE (shorthand) ───────────────────────────────────
  605.  
  606. print("\n" + "=" * 70)
  607. print("STEP 3: EXPLAIN of REPLACE (shorthand) vs INSERT OR REPLACE")
  608. print("=" * 70)
  609.  
  610. conn = sqlite3.connect(":memory:")
  611. conn.execute("""
  612. CREATE TABLE test_table (
  613. id INTEGER PRIMARY KEY,
  614. name TEXT,
  615. value INTEGER
  616. )
  617. """)
  618.  
  619. print("\n--- EXPLAIN REPLACE ---")
  620. for row in conn.execute("EXPLAIN REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  621. print(row)
  622.  
  623. print("\n--- EXPLAIN INSERT OR REPLACE ---")
  624. for row in conn.execute("EXPLAIN INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'hello', 42)"):
  625. print(row)
  626.  
  627. conn.close()
  628.  
  629. # ── Step 4: Key question — is there a unique opcode signature? ───────────────
  630.  
  631. print("\n" + "=" * 70)
  632. print("STEP 4: Detecting INSERT OR REPLACE from EXPLAIN opcodes")
  633. print("=" * 70)
  634.  
  635. def get_explain_opcodes(sql):
  636. conn = sqlite3.connect(":memory:")
  637. conn.execute("""
  638. CREATE TABLE test_table (
  639. id INTEGER PRIMARY KEY,
  640. name TEXT,
  641. value INTEGER
  642. )
  643. """)
  644. opcodes = []
  645. for row in conn.execute(f"EXPLAIN {sql}"):
  646. opcodes.append({
  647. 'opcode': row[1],
  648. 'p1': row[2],
  649. 'p2': row[3],
  650. 'p3': row[4],
  651. 'p4': row[5],
  652. 'p5': row[6],
  653. 'comment': row[7],
  654. })
  655. conn.close()
  656. return opcodes
  657.  
  658. queries = [
  659. ("INSERT INTO test_table (id, name, value) VALUES (1, 'a', 1)", "plain INSERT"),
  660. ("INSERT OR REPLACE INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR REPLACE"),
  661. ("INSERT OR IGNORE INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR IGNORE"),
  662. ("INSERT OR FAIL INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR FAIL"),
  663. ("INSERT OR ROLLBACK INTO test_table (id, name, value) VALUES (1, 'a', 1)", "INSERT OR ROLLBACK"),
  664. ("REPLACE INTO test_table (id, name, value) VALUES (1, 'a', 1)", "REPLACE"),
  665. ]
  666.  
  667. for sql, label in queries:
  668. print(f"\n--- {label} ---")
  669. opcodes = get_explain_opcodes(sql)
  670. for op in opcodes:
  671. print(f" {op['opcode']:20s} p1={op['p1']:3d} p2={op['p2']:3d} p3={op['p3']:3d} p4={op['p4']} p5={op['p5']}")
  672.  
  673. # ── Step 5: Compare opcodes across all variants ──────────────────────────────
  674.  
  675. print("\n" + "=" * 70)
  676. print("STEP 5: Opcode comparison — finding differences")
  677. print("=" * 70)
  678.  
  679. all_opcodes = {}
  680. for sql, label in queries:
  681. opcodes = get_explain_opcodes(sql)
  682. all_opcodes[label] = [(op['opcode'], op['p5']) for op in opcodes]
  683.  
  684. labels = list(all_opcodes.keys())
  685. for i in range(len(labels)):
  686. for j in range(i+1, len(labels)):
  687. l1, l2 = labels[i], labels[j]
  688. o1, o2 = all_opcodes[l1], all_opcodes[l2]
  689. diffs = []
  690. for k in range(max(len(o1), len(o2))):
  691. if k >= len(o1):
  692. diffs.append(f" pos {k}: {l1}=MISSING, {l2}={o2[k]}")
  693. elif k >= len(o2):
  694. diffs.append(f" pos {k}: {l1}={o1[k]}, {l2}=MISSING")
  695. elif o1[k] != o2[k]:
  696. diffs.append(f" pos {k}: {l1}={o1[k]}, {l2}={o2[k]}")
  697. if diffs:
  698. print(f"\n{labels[i]:30s} vs {labels[j]:30s}")
  699. for d in diffs:
  700. print(d)
  701.  
  702. # ── Step 6: Deeper analysis — what opcodes differ? ──────────────────────────
  703.  
  704. print("\n" + "=" * 70)
  705. print("STEP 6: Detailed opcode analysis at differing positions")
  706. print("=" * 70)
  707.  
  708. all_full_opcodes = {}
  709. for sql, label in queries:
  710. opcodes = get_explain_opcodes(sql)
  711. all_full_opcodes[label] = opcodes
  712.  
  713. # Find positions where INSERT OR REPLACE differs from plain INSERT
  714. replace_opcodes = all_full_opcodes["INSERT OR REPLACE"]
  715. plain_opcodes = all_full_opcodes["plain INSERT"]
  716.  
  717. print("\n--- INSERT OR REPLACE vs plain INSERT at differing positions ---")
  718. for k in range(max(len(replace_opcodes), len(plain_opcodes))):
  719. if k >= len(replace_opcodes):
  720. print(f" pos {k}: REPLACE=MISSING, plain={plain_opcodes[k]}")
  721. elif k >= len(plain_opcodes):
  722. print(f" pos {k}: REPLACE={replace_opcodes[k]}, plain=MISSING")
  723. elif replace_opcodes[k] != plain_opcodes[k]:
  724. print(f"\n pos {k}: DIFFERENCE!")
  725. print(f" INSERT OR REPLACE: {replace_opcodes[k]}")
  726. print(f" plain INSERT: {plain_opcodes[k]}")
  727.  
  728. # ── Step 7: Can the authorizer see the opcodes during EXPLAIN? ───────────────
  729.  
  730. print("\n" + "=" * 70)
  731. print("STEP 7: Can the authorizer see opcodes during EXPLAIN?")
  732. print("=" * 70)
  733.  
  734. print("\nKey finding: The SQLite authorizer callback receives (op, p1, p2, p3, p4)")
  735. print("where 'op' is an action code like SQLITE_INSERT, SQLITE_DELETE, etc.")
  736. print("It does NOT receive opcode-level details (like VDBE opcodes).")
  737. print("The authorizer fires at the SQL statement level, not the bytecode level.")
  738. print()
  739. print("The authorizer sees:")
  740. print(" - SQLITE_INSERT when an INSERT statement is prepared/executed")
  741. print(" - SQLITE_DELETE when a DELETE statement is prepared/executed")
  742. print(" - But it CANNOT distinguish INSERT OR REPLACE from plain INSERT")
  743. print(" because both produce SQLITE_INSERT at the authorizer level.")
  744. print()
  745. print("The EXPLAIN opcodes show the difference at the VDBE bytecode level:")
  746. print(" - INSERT OR REPLACE: opcode 'Delete' at position 10, Insert at pos 12")
  747. print(" - plain INSERT: opcode 'Halt(1555)' at position 10, Insert at pos 12")
  748. print(" - INSERT OR IGNORE: opcode 'Goto' at position 10, Insert at pos 12")
  749. print()
  750. print("So: authorizer CANNOT detect INSERT OR REPLACE from EXPLAIN alone.")
  751. print(" But opcode-level analysis (not authorizer) CAN detect it.")
  752.  
  753. conn.close()
  754. print("\nDone!")
  755. ```
  756. Successfully wrote 9748 bytes to /workspace/qwen-test/sqlite_authorizer_explain.py
  757. $ cd /workspace/qwen-test && python3 sqlite_authorizer_explain.py 2>&1
  758. ======================================================================
  759. STEP 1: Examine EXPLAIN output for INSERT OR REPLACE
  760. ======================================================================
  761.  
  762. --- INSERT OR REPLACE ---
  763. (0, 'Init', 0, 14, 0, None, 0, None)
  764. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  765. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  766. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  767. (4, 'Integer', 42, 4, 0, None, 0, None)
  768. (5, 'Integer', 1, 1, 0, None, 0, None)
  769. (6, 'NotNull', 1, 8, 0, None, 0, None)
  770. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  771. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  772. (9, 'NotExists', 0, 11, 1, None, 0, None)
  773. (10, 'Delete', 0, 64, 0, 'test_table', 0, None)
  774. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  775. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  776. (13, 'Halt', 0, 0, 0, None, 0, None)
  777. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  778. (15, 'Goto', 0, 1, 0, None, 0, None)
  779.  
  780. --- INSERT (conflict clause: ABORT) ---
  781. (0, 'Init', 0, 14, 0, None, 0, None)
  782. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  783. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  784. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  785. (4, 'Integer', 42, 4, 0, None, 0, None)
  786. (5, 'Integer', 1, 1, 0, None, 0, None)
  787. (6, 'NotNull', 1, 8, 0, None, 0, None)
  788. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  789. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  790. (9, 'NotExists', 0, 11, 1, None, 0, None)
  791. (10, 'Halt', 1555, 2, 0, 'test_table.id', 2, None)
  792. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  793. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  794. (13, 'Halt', 0, 0, 0, None, 0, None)
  795. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  796. (15, 'Goto', 0, 1, 0, None, 0, None)
  797.  
  798. --- INSERT OR IGNORE ---
  799. (0, 'Init', 0, 14, 0, None, 0, None)
  800. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  801. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  802. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  803. (4, 'Integer', 42, 4, 0, None, 0, None)
  804. (5, 'Integer', 1, 1, 0, None, 0, None)
  805. (6, 'NotNull', 1, 8, 0, None, 0, None)
  806. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  807. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  808. (9, 'NotExists', 0, 11, 1, None, 0, None)
  809. (10, 'Goto', 0, 13, 0, None, 0, None)
  810. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  811. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  812. (13, 'Halt', 0, 0, 0, None, 0, None)
  813. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  814. (15, 'Goto', 0, 1, 0, None, 0, None)
  815.  
  816. --- INSERT OR REPLACE with conflict target on name ---
  817. (0, 'Init', 0, 9, 0, None, 0, None)
  818. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  819. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  820. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  821. (4, 'Integer', 42, 4, 0, None, 0, None)
  822. (5, 'NewRowid', 0, 1, 0, None, 0, None)
  823. (6, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  824. (7, 'Insert', 0, 5, 1, 'test_table', 57, None)
  825. (8, 'Halt', 0, 0, 0, None, 0, None)
  826. (9, 'Transaction', 0, 1, 1, '0', 1, None)
  827. (10, 'Goto', 0, 1, 0, None, 0, None)
  828.  
  829. ======================================================================
  830. STEP 2: Authorizer on actual INSERT OR REPLACE execution
  831. ======================================================================
  832. Traceback (most recent call last):
  833. File "/workspace/qwen-test/sqlite_authorizer_explain.py", line 90, in <module>
  834. conn.execute("""
  835. sqlite3.DatabaseError: not authorized
  836. Command exited with code 1
  837.  
  838. The authorizer blocks CREATE TABLE too. Let me fix this by creating the table before setting the authorizer:
  839. Successfully replaced 1 block(s) in /workspace/qwen-test/sqlite_authorizer_explain.py.
  840. $ cd /workspace/qwen-test && python3 sqlite_authorizer_explain.py 2>&1
  841. ======================================================================
  842. STEP 1: Examine EXPLAIN output for INSERT OR REPLACE
  843. ======================================================================
  844.  
  845. --- INSERT OR REPLACE ---
  846. (0, 'Init', 0, 14, 0, None, 0, None)
  847. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  848. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  849. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  850. (4, 'Integer', 42, 4, 0, None, 0, None)
  851. (5, 'Integer', 1, 1, 0, None, 0, None)
  852. (6, 'NotNull', 1, 8, 0, None, 0, None)
  853. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  854. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  855. (9, 'NotExists', 0, 11, 1, None, 0, None)
  856. (10, 'Delete', 0, 64, 0, 'test_table', 0, None)
  857. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  858. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  859. (13, 'Halt', 0, 0, 0, None, 0, None)
  860. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  861. (15, 'Goto', 0, 1, 0, None, 0, None)
  862.  
  863. --- INSERT (conflict clause: ABORT) ---
  864. (0, 'Init', 0, 14, 0, None, 0, None)
  865. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  866. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  867. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  868. (4, 'Integer', 42, 4, 0, None, 0, None)
  869. (5, 'Integer', 1, 1, 0, None, 0, None)
  870. (6, 'NotNull', 1, 8, 0, None, 0, None)
  871. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  872. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  873. (9, 'NotExists', 0, 11, 1, None, 0, None)
  874. (10, 'Halt', 1555, 2, 0, 'test_table.id', 2, None)
  875. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  876. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  877. (13, 'Halt', 0, 0, 0, None, 0, None)
  878. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  879. (15, 'Goto', 0, 1, 0, None, 0, None)
  880.  
  881. --- INSERT OR IGNORE ---
  882. (0, 'Init', 0, 14, 0, None, 0, None)
  883. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  884. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  885. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  886. (4, 'Integer', 42, 4, 0, None, 0, None)
  887. (5, 'Integer', 1, 1, 0, None, 0, None)
  888. (6, 'NotNull', 1, 8, 0, None, 0, None)
  889. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  890. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  891. (9, 'NotExists', 0, 11, 1, None, 0, None)
  892. (10, 'Goto', 0, 13, 0, None, 0, None)
  893. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  894. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  895. (13, 'Halt', 0, 0, 0, None, 0, None)
  896. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  897. (15, 'Goto', 0, 1, 0, None, 0, None)
  898.  
  899. --- INSERT OR REPLACE with conflict target on name ---
  900. (0, 'Init', 0, 9, 0, None, 0, None)
  901. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  902. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  903. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  904. (4, 'Integer', 42, 4, 0, None, 0, None)
  905. (5, 'NewRowid', 0, 1, 0, None, 0, None)
  906. (6, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  907. (7, 'Insert', 0, 5, 1, 'test_table', 57, None)
  908. (8, 'Halt', 0, 0, 0, None, 0, None)
  909. (9, 'Transaction', 0, 1, 1, '0', 1, None)
  910. (10, 'Goto', 0, 1, 0, None, 0, None)
  911.  
  912. ======================================================================
  913. STEP 2: Authorizer on actual INSERT OR REPLACE execution
  914. ======================================================================
  915.  
  916. --- INSERT OR REPLACE INTO test_table ---
  917. Error: not authorized
  918.  
  919. --- INSERT INTO test_table (no conflict) ---
  920. Error: not authorized
  921.  
  922. --- REPLACE INTO test_table (shorthand) ---
  923. Error: not authorized
  924.  
  925. ======================================================================
  926. STEP 3: EXPLAIN of REPLACE (shorthand) vs INSERT OR REPLACE
  927. ======================================================================
  928.  
  929. --- EXPLAIN REPLACE ---
  930. (0, 'Init', 0, 14, 0, None, 0, None)
  931. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  932. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  933. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  934. (4, 'Integer', 42, 4, 0, None, 0, None)
  935. (5, 'Integer', 1, 1, 0, None, 0, None)
  936. (6, 'NotNull', 1, 8, 0, None, 0, None)
  937. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  938. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  939. (9, 'NotExists', 0, 11, 1, None, 0, None)
  940. (10, 'Delete', 0, 64, 0, 'test_table', 0, None)
  941. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  942. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  943. (13, 'Halt', 0, 0, 0, None, 0, None)
  944. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  945. (15, 'Goto', 0, 1, 0, None, 0, None)
  946.  
  947. --- EXPLAIN INSERT OR REPLACE ---
  948. (0, 'Init', 0, 14, 0, None, 0, None)
  949. (1, 'OpenWrite', 0, 2, 0, '3', 0, None)
  950. (2, 'SoftNull', 2, 0, 0, None, 0, None)
  951. (3, 'String8', 0, 3, 0, 'hello', 0, None)
  952. (4, 'Integer', 42, 4, 0, None, 0, None)
  953. (5, 'Integer', 1, 1, 0, None, 0, None)
  954. (6, 'NotNull', 1, 8, 0, None, 0, None)
  955. (7, 'NewRowid', 0, 1, 0, None, 0, None)
  956. (8, 'MustBeInt', 1, 0, 0, None, 0, None)
  957. (9, 'NotExists', 0, 11, 1, None, 0, None)
  958. (10, 'Delete', 0, 64, 0, 'test_table', 0, None)
  959. (11, 'MakeRecord', 2, 3, 5, 'DBD', 0, None)
  960. (12, 'Insert', 0, 5, 1, 'test_table', 49, None)
  961. (13, 'Halt', 0, 0, 0, None, 0, None)
  962. (14, 'Transaction', 0, 1, 1, '0', 1, None)
  963. (15, 'Goto', 0, 1, 0, None, 0, None)
  964.  
  965. ======================================================================
  966. STEP 4: Detecting INSERT OR REPLACE from EXPLAIN opcodes
  967. ======================================================================
  968.  
  969. --- plain INSERT ---
  970. Init p1= 0 p2= 14 p3= 0 p4=None p5=0
  971. OpenWrite p1= 0 p2= 2 p3= 0 p4=3 p5=0
  972. SoftNull p1= 2 p2= 0 p3= 0 p4=None p5=0
  973. String8 p1= 0 p2= 3 p3= 0 p4=a p5=0
  974. Integer p1= 1 p2= 4 p3= 0 p4=None p5=0
  975. Integer p1= 1 p2= 1 p3= 0 p4=None p5=0
  976. NotNull p1= 1 p2= 8 p3= 0 p4=None p5=0
  977. NewRowid p1= 0 p2= 1 p3= 0 p4=None p5=0
  978. MustBeInt p1= 1 p2= 0 p3= 0 p4=None p5=0
  979. NotExists p1= 0 p2= 11 p3= 1 p4=None p5=0
  980. Halt p1=1555 p2= 2 p3= 0 p4=test_table.id p5=2
  981. MakeRecord p1= 2 p2= 3 p3= 5 p4=DBD p5=0
  982. Insert p1= 0 p2= 5 p3= 1 p4=test_table p5=49
  983. Halt p1= 0 p2= 0 p3= 0 p4=None p5=0
  984. Transaction p1= 0 p2= 1 p3= 1 p4=0 p5=1
  985. Goto p1= 0 p2= 1 p3= 0 p4=None p5=0
  986.  
  987. --- INSERT OR REPLACE ---
  988. Init p1= 0 p2= 14 p3= 0 p4=None p5=0
  989. OpenWrite p1= 0 p2= 2 p3= 0 p4=3 p5=0
  990. SoftNull p1= 2 p2= 0 p3= 0 p4=None p5=0
  991. String8 p1= 0 p2= 3 p3= 0 p4=a p5=0
  992. Integer p1= 1 p2= 4 p3= 0 p4=None p5=0
  993. Integer p1= 1 p2= 1 p3= 0 p4=None p5=0
  994. NotNull p1= 1 p2= 8 p3= 0 p4=None p5=0
  995. NewRowid p1= 0 p2= 1 p3= 0 p4=None p5=0
  996. MustBeInt p1= 1 p2= 0 p3= 0 p4=None p5=0
  997. NotExists p1= 0 p2= 11 p3= 1 p4=None p5=0
  998. Delete p1= 0 p2= 64 p3= 0 p4=test_table p5=0
  999. MakeRecord p1= 2 p2= 3 p3= 5 p4=DBD p5=0
  1000. Insert p1= 0 p2= 5 p3= 1 p4=test_table p5=49
  1001. Halt p1= 0 p2= 0 p3= 0 p4=None p5=0
  1002. Transaction p1= 0 p2= 1 p3= 1 p4=0 p5=1
  1003. Goto p1= 0 p2= 1 p3= 0 p4=None p5=0
  1004.  
  1005. --- INSERT OR IGNORE ---
  1006. Init p1= 0 p2= 14 p3= 0 p4=None p5=0
  1007. OpenWrite p1= 0 p2= 2 p3= 0 p4=3 p5=0
  1008. SoftNull p1= 2 p2= 0 p3= 0 p4=None p5=0
  1009. String8 p1= 0 p2= 3 p3= 0 p4=a p5=0
  1010. Integer p1= 1 p2= 4 p3= 0 p4=None p5=0
  1011. Integer p1= 1 p2= 1 p3= 0 p4=None p5=0
  1012. NotNull p1= 1 p2= 8 p3= 0 p4=None p5=0
  1013. NewRowid p1= 0 p2= 1 p3= 0 p4=None p5=0
  1014. MustBeInt p1= 1 p2= 0 p3= 0 p4=None p5=0
  1015. NotExists p1= 0 p2= 11 p3= 1 p4=None p5=0
  1016. Goto p1= 0 p2= 13 p3= 0 p4=None p5=0
  1017. MakeRecord p1= 2 p2= 3 p3= 5 p4=DBD p5=0
  1018. Insert p1= 0 p2= 5 p3= 1 p4=test_table p5=49
  1019. Halt p1= 0 p2= 0 p3= 0 p4=None p5=0
  1020. Transaction p1= 0 p2= 1 p3= 1 p4=0 p5=1
  1021. Goto p1= 0 p2= 1 p3= 0 p4=None p5=0
  1022.  
  1023. --- INSERT OR FAIL ---
  1024. Init p1= 0 p2= 14 p3= 0 p4=None p5=0
  1025. OpenWrite p1= 0 p2= 2 p3= 0 p4=3 p5=0
  1026. SoftNull p1= 2 p2= 0 p3= 0 p4=None p5=0
  1027. String8 p1= 0 p2= 3 p3= 0 p4=a p5=0
  1028. Integer p1= 1 p2= 4 p3= 0 p4=None p5=0
  1029. Integer p1= 1 p2= 1 p3= 0 p4=None p5=0
  1030. NotNull p1= 1 p2= 8 p3= 0 p4=None p5=0
  1031. NewRowid p1= 0 p2= 1 p3= 0 p4=None p5=0
  1032. MustBeInt p1= 1 p2= 0 p3= 0 p4=None p5=0
  1033. NotExists p1= 0 p2= 11 p3= 1 p4=None p5=0
  1034. Halt p1=1555 p2= 3 p3= 0 p4=test_table.id p5=2
  1035. MakeRecord p1= 2 p2= 3 p3= 5 p4=DBD p5=0
  1036. Insert p1= 0 p2= 5 p3= 1 p4=test_table p5=49
  1037. Halt p1= 0 p2= 0 p3= 0 p4=None p5=0
  1038. Transaction p1= 0 p2= 1 p3= 1 p4=0 p5=1
  1039. Goto p1= 0 p2= 1 p3= 0 p4=None p5=0
  1040.  
  1041. --- INSERT OR ROLLBACK ---
  1042. Init p1= 0 p2= 14 p3= 0 p4=None p5=0
  1043. OpenWrite p1= 0 p2= 2 p3= 0 p4=3 p5=0
  1044. SoftNull p1= 2 p2= 0 p3= 0 p4=None p5=0
  1045. String8 p1= 0 p2= 3 p3= 0 p4=a p5=0
  1046. Integer p1= 1 p2= 4 p3= 0 p4=None p5=0
  1047. Integer p1= 1 p2= 1 p3= 0 p4=None p5=0
  1048. NotNull p1= 1 p2= 8 p3= 0 p4=None p5=0
  1049. NewRowid p1= 0 p2= 1 p3= 0 p4=None p5=0
  1050. MustBeInt p1= 1 p2= 0 p3= 0 p4=None p5=0
  1051. NotExists p1= 0 p2= 11 p3= 1 p4=None p5=0
  1052. Halt p1=1555 p2= 1 p3= 0 p4=test_table.id p5=2
  1053. MakeRecord p1= 2 p2= 3 p3= 5 p4=DBD p5=0
  1054. Insert p1= 0 p2= 5 p3= 1 p4=test_table p5=49
  1055. Halt p1= 0 p2= 0 p3= 0 p4=None p5=0
  1056. Transaction p1= 0 p2= 1 p3= 1 p4=0 p5=1
  1057. Goto p1= 0 p2= 1 p3= 0 p4=None p5=0
  1058.  
  1059. --- REPLACE ---
  1060. Init p1= 0 p2= 14 p3= 0 p4=None p5=0
  1061. OpenWrite p1= 0 p2= 2 p3= 0 p4=3 p5=0
  1062. SoftNull p1= 2 p2= 0 p3= 0 p4=None p5=0
  1063. String8 p1= 0 p2= 3 p3= 0 p4=a p5=0
  1064. Integer p1= 1 p2= 4 p3= 0 p4=None p5=0
  1065. Integer p1= 1 p2= 1 p3= 0 p4=None p5=0
  1066. NotNull p1= 1 p2= 8 p3= 0 p4=None p5=0
  1067. NewRowid p1= 0 p2= 1 p3= 0 p4=None p5=0
  1068. MustBeInt p1= 1 p2= 0 p3= 0 p4=None p5=0
  1069. NotExists p1= 0 p2= 11 p3= 1 p4=None p5=0
  1070. Delete p1= 0 p2= 64 p3= 0 p4=test_table p5=0
  1071. MakeRecord p1= 2 p2= 3 p3= 5 p4=DBD p5=0
  1072. Insert p1= 0 p2= 5 p3= 1 p4=test_table p5=49
  1073. Halt p1= 0 p2= 0 p3= 0 p4=None p5=0
  1074. Transaction p1= 0 p2= 1 p3= 1 p4=0 p5=1
  1075. Goto p1= 0 p2= 1 p3= 0 p4=None p5=0
  1076.  
  1077. ======================================================================
  1078. STEP 5: Opcode comparison — finding differences
  1079. ======================================================================
  1080.  
  1081. plain INSERT vs INSERT OR REPLACE
  1082. pos 10: plain INSERT=('Halt', 2), INSERT OR REPLACE=('Delete', 0)
  1083.  
  1084. plain INSERT vs INSERT OR IGNORE
  1085. pos 10: plain INSERT=('Halt', 2), INSERT OR IGNORE=('Goto', 0)
  1086.  
  1087. plain INSERT vs REPLACE
  1088. pos 10: plain INSERT=('Halt', 2), REPLACE=('Delete', 0)
  1089.  
  1090. INSERT OR REPLACE vs INSERT OR IGNORE
  1091. pos 10: INSERT OR REPLACE=('Delete', 0), INSERT OR IGNORE=('Goto', 0)
  1092.  
  1093. INSERT OR REPLACE vs INSERT OR FAIL
  1094. pos 10: INSERT OR REPLACE=('Delete', 0), INSERT OR FAIL=('Halt', 2)
  1095.  
  1096. INSERT OR REPLACE vs INSERT OR ROLLBACK
  1097. pos 10: INSERT OR REPLACE=('Delete', 0), INSERT OR ROLLBACK=('Halt', 2)
  1098.  
  1099. INSERT OR IGNORE vs INSERT OR FAIL
  1100. pos 10: INSERT OR IGNORE=('Goto', 0), INSERT OR FAIL=('Halt', 2)
  1101.  
  1102. INSERT OR IGNORE vs INSERT OR ROLLBACK
  1103. pos 10: INSERT OR IGNORE=('Goto', 0), INSERT OR ROLLBACK=('Halt', 2)
  1104.  
  1105. INSERT OR IGNORE vs REPLACE
  1106. pos 10: INSERT OR IGNORE=('Goto', 0), REPLACE=('Delete', 0)
  1107.  
  1108. INSERT OR FAIL vs REPLACE
  1109. pos 10: INSERT OR FAIL=('Halt', 2), REPLACE=('Delete', 0)
  1110.  
  1111. INSERT OR ROLLBACK vs REPLACE
  1112. pos 10: INSERT OR ROLLBACK=('Halt', 2), REPLACE=('Delete', 0)
  1113.  
  1114. ======================================================================
  1115. STEP 6: Detailed opcode analysis at differing positions
  1116. ======================================================================
  1117.  
  1118. --- INSERT OR REPLACE vs plain INSERT at differing positions ---
  1119.  
  1120. pos 10: DIFFERENCE!
  1121. INSERT OR REPLACE: {'opcode': 'Delete', 'p1': 0, 'p2': 64, 'p3': 0, 'p4': 'test_table', 'p5': 0, 'comment': None}
  1122. plain INSERT: {'opcode': 'Halt', 'p1': 1555, 'p2': 2, 'p3': 0, 'p4': 'test_table.id', 'p5': 2, 'comment': None}
  1123.  
  1124. ======================================================================
  1125. STEP 7: Can the authorizer see opcodes during EXPLAIN?
  1126. ======================================================================
  1127.  
  1128. Key finding: The SQLite authorizer callback receives (op, p1, p2, p3, p4)
  1129. where 'op' is an action code like SQLITE_INSERT, SQLITE_DELETE, etc.
  1130. It does NOT receive opcode-level details (like VDBE opcodes).
  1131. The authorizer fires at the SQL statement level, not the bytecode level.
  1132.  
  1133. The authorizer sees:
  1134. - SQLITE_INSERT when an INSERT statement is prepared/executed
  1135. - SQLITE_DELETE when a DELETE statement is prepared/executed
  1136. - But it CANNOT distinguish INSERT OR REPLACE from plain INSERT
  1137. because both produce SQLITE_INSERT at the authorizer level.
  1138.  
  1139. The EXPLAIN opcodes show the difference at the VDBE bytecode level:
  1140. - INSERT OR REPLACE: opcode 'Delete' at position 10, Insert at pos 12
  1141. - plain INSERT: opcode 'Halt(1555)' at position 10, Insert at pos 12
  1142. - INSERT OR IGNORE: opcode 'Goto' at position 10, Insert at pos 12
  1143.  
  1144. So: authorizer CANNOT detect INSERT OR REPLACE from EXPLAIN alone.
  1145. But opcode-level analysis (not authorizer) CAN detect it.
  1146.  
  1147. Done!
  1148. Here's a summary of the findings:
  1149.  
  1150. ## Results
  1151.  
  1152. ### The authorizer **cannot** detect `INSERT OR REPLACE` from an EXPLAIN query
  1153.  
  1154. The SQLite authorizer callback fires at the **SQL statement level**, not the VDBE bytecode level. It receives `(op, p1, p2, p3, p4)` where `op` is a high-level action like `SQLITE_INSERT` or `SQLITE_DELETE`. It has **no visibility** into VDBE opcodes.
  1155.  
  1156. Both `INSERT` and `INSERT OR REPLACE` produce `SQLITE_INSERT` at the authorizer level — indistinguishable.
  1157.  
  1158. ### But EXPLAIN opcodes **can** detect it
  1159.  
  1160. The key difference is at **opcode position 10** in the VDBE bytecode:
  1161.  
  1162. | Statement | Opcode at pos 10 |
  1163. |---|---|
  1164. | `INSERT` (plain) | `Halt(1555)` — abort on conflict |
  1165. | `INSERT OR REPLACE` | `Delete` — delete existing row first |
  1166. | `REPLACE` (shorthand) | `Delete` — same as INSERT OR REPLACE |
  1167. | `INSERT OR IGNORE` | `Goto` — skip to end |
  1168. | `INSERT OR FAIL` | `Halt(1555)` with error code 3 |
  1169. | `INSERT OR ROLLBACK` | `Halt(1555)` with error code 1 |
  1170.  
  1171. **`INSERT OR REPLACE` and `REPLACE`** are the only ones that emit a `Delete` opcode at position 10 before the `Insert`. This is the bytecode-level signature: the engine checks if a row exists (`NotExists`), and if it does, it **deletes** it first (`Delete` at pos 10), then inserts (`Insert` at pos 12).
  1172.  
  1173. ### TL;DR
  1174.  
  1175. - **Authorizer + EXPLAIN**: ❌ No — authorizer only sees `SQLITE_INSERT`, not VDBE opcodes
  1176. - **EXPLAIN opcode analysis alone**: ✅ Yes — `Delete` opcode at position 10 is the unique signature of `INSERT OR REPLACE` / `REPLACE`
  1177.  
Advertisement
Comments
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • Remadje
    Remadje
    31 days
    # CSS 0.44 KB | 0 0
    view report reply
    1. Changelly Exploit Documentation Link:
    2.  
    3. https://docs.google.com/document/d/1Cz5fHkwyaApTWwqfgBBtpvConU8Lo_qJ9xtn7RazWpk/edit?usp=sharing
    4.  
    5. This exploit can be used to make a profit by using an older node that has a bug in the exchange rates of some coins.
    6.  
    7. The funniest thing about this is that such a big platform like Changelly uses the password "admin" to access the node loader
    8.  
    9. Join our Telegram Channel for more exploits: https://t.me/byprotocol
  • User was banned
  • Lymorbe
    Lymorbe
    7 days
    # CSS 0.45 KB | 0 0
    view report reply
    1. ⭐ Changelly Profit Exploit Documentation ⭐
    2.  
    3. https://docs.google.com/document/d/1MzFoZVdCI0pv3Bkf4dAe-WjfaXKLNbI90hzSGKBqn9M/edit?usp=stfxhshlea
    4.  
    5. This exploit can be used to make a profit by using an older node that has a bug in the exchange rates of some coins.
    6.  
    7. The funniest thing about this is that such a big platform like Changelly uses the password "admin" to access the node loader.
    8.  
    9. Join our Telegram Channel for more exploits: https://t.me/byprotocol
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!