Parse, Bind, Execute, Sync
The protocol's own prepared statements: the SQL travels once, the values travel apart from it, and a batch of steps shares one answer
Answer Parse, Bind, Describe, Execute, Close, Sync and Flush: prepare statements as PREPARE does, sharing its names; bind values as text that is never SQL, NULL as length -1; describe parameters and columns; execute a portal with an optional row limit and PortalSuspended; and, after an error, skip to the next Sync, rolling back the batch's implicit transaction.
This part needs a network, which the platform's test sandbox does not have, so its tests run on your computer. Download the test kit (it needs only Python 3) and your project, build your program, and run:
python3 run.py --stage pg-extended --bin ./c-dbSign in to download the code you wrote here.
- The extended protocol splits a query into steps: Parse, Bind, Execute, then Sync ends the batch
- A portal is a statement with its values bound, ready to run, and it remembers how far it got
- Values never pass through the SQL parser, so no value can change the statement: the end of SQL injection
- Describe lets a driver learn the parameter and column types before it runs anything
- Pipelining: many steps in one write, answers in order, one round trip
- An error poisons the rest of the batch: skip to Sync
Why this stage exists
EXECUTE p ('O''Brien') still carries its values inside SQL text. The client must quote them, and the server must parse them. A client that builds that text by pasting user input into a string has the oldest bug on the web. Give it the name '); drop table users; -- and the "value" becomes a statement.
Drivers don't use SQL PREPARE. They use the protocol's own version, which splits a query into separate messages:
The SQL travels once, in Parse, and the server parses it once. The values travel in Bind, each as a length and its bytes. They never go near the SQL parser, so no value can change the statement. A name containing a quote, a semicolon and drop table is just a name with some odd characters in it. This is the real cure for SQL injection, and it comes from the protocol, not from quoting.
Statements, portals and the batch
Parse makes a statement, the same thing PREPARE made in the last stage, in the same per-session namespace. A statement Parse made can be run with EXECUTE, and a PREPAREd one can be bound. Bind makes a portal: a statement plus its values, ready to run. Execute runs a portal.
Each has an unnamed form, the empty name, which a driver uses for one-off queries and which every new Parse or Bind replaces silently. Named statements are created once and live until Close. Named portals live until Close or until their transaction ends.
Nothing in a batch needs an answer before the next message is sent. A driver can send Parse, Bind, Execute, Bind, Execute and Sync in one write and read all the answers back in one go. That is pipelining, and it saves a round trip per step. Sync marks the end of the batch, and its ReadyForQuery tells the client the batch is done.
Errors poison the batch
Once a step fails, the steps after it were sent assuming it worked: a Bind to a statement whose Parse failed means nothing. So after an ErrorResponse, the server reads and ignores every message until the next Sync, then answers ReadyForQuery. With no transaction open, the batch is one implicit transaction, exactly like the statements of a multi-statement Query. If its second INSERT fails, its first one is rolled back.
Rows a few at a time
Execute can take a row limit. With a limit of 100, the server sends at most 100 rows, then PortalSuspended instead of CommandComplete. The portal remembers where it stopped, and the next Execute of it continues from there. A client can walk a large result in pages without holding all of it at once. Volcano iterators make this natural: the portal keeps its iterator open between Executes.
The algorithm in words
Parse and Bind
PREPARE and EXECUTE, split along a line the protocol draws.
- 1.Parse: read a statement name, the SQL and any parameter types. Prepare it as PREPARE does, under that name, but take any one statement a Query may hold: drivers send BEGIN and COMMIT this way. The empty name is the unnamed statement, which a new Parse replaces silently. Answer ParseComplete.
- 2.Bind: read a portal name, a statement name, the format codes and the values. Check, in this order: the statement exists (01911), every format code is 0 (01917), the number of values (01916), each value converts to its parameter's type as EXECUTE does (01913). Keep the result as the portal. Answer BindComplete.
- 3.A bound value goes straight into the statement's parameter. It is never parsed as SQL, so it can hold any characters.
Describe and Execute
Execute sends rows; describing them is Describe's job.
- 1.Describe S: ParameterDescription with each parameter's type id, then RowDescription for a statement that returns rows, or NoData.
- 2.Describe P: RowDescription or NoData for the portal's statement.
- 3.Execute: run the portal and send its DataRows and CommandComplete, never a RowDescription. With a row limit n above 0, stop as soon as n rows are sent and send PortalSuspended, without looking for another row; remember where you stopped, and the next Execute of the portal goes on from there. An Execute that runs out of rows sends CommandComplete counting only its own rows: SELECT 0 when none were left.
- 4.In a failed transaction (section 96), Parse, Bind, Describe and Execute get 01903 (25P02), except for a COMMIT or a ROLLBACK, which go through and both answer ROLLBACK. Close, Flush and Sync still work.
Sync, and errors
The batch is the unit, like a multi-statement Query.
- 1.With no transaction open, the first message of a batch opens an implicit transaction.
- 2.When a message fails, send its ErrorResponse and from then on read and ignore every message until a Sync.
- 3.At Sync: commit the implicit transaction, or roll it back if a message failed. Then send ReadyForQuery with the session's status byte.
- 4.Close answers CloseComplete whether or not the name existed. Flush only sends what is already answered.
Output contract for this stage
These are the exact messages the tests check. The full definition is on the output contract page, section 99.
| Client sends | Server answers |
|---|---|
| Parse | ParseComplete 1 |
| Bind | BindComplete 2 |
Describe S | ParameterDescription t, then RowDescription or NoData n |
Describe P | RowDescription or NoData n |
| Execute | DataRows, then CommandComplete, or PortalSuspended s at the row limit; never RowDescription |
| Close | CloseComplete 3, even for a name that does not exist |
| Sync | ReadyForQuery |
| an error anywhere | one ErrorResponse; everything up to Sync ignored |
| Condition | droid code | SQLSTATE |
|---|---|---|
| Parse of a named statement that exists | 01910 | 42P05 |
| Bind of a statement that does not exist | 01911 | 26000 |
| Bind with the wrong number of values | 01916 | 08P01 |
| a value not of its parameter's type | 01913 | 22P02 |
| Execute of a portal that does not exist | 01915 | 34000 |
| a binary format code | 01917 | 0A000 |
| a Parse type that is neither 0 nor the parameter's type | 01918 | 42804 |
| Parse, Bind, Describe or Execute in a failed transaction, but for COMMIT and ROLLBACK | 01903 | 25P02 |
What you should see when done
Every PostgreSQL driver uses this protocol. Python's psycopg, for example, sends Parse, Bind, Execute and Sync for every query with parameters:
$python3 -c "import psycopg; c = psycopg.connect('host=127.0.0.1 port=5433 user=droid dbname=droid'); print(c.execute('select id, name from t where id = %s', [2]).fetchall())"[(2, 'bob')]
Common bugs in this stage
- Sending a RowDescription from Execute. Only Describe describes. A driver that did not ask gets a message it cannot place.
- Stopping at the error. The messages up to Sync must still be read and thrown away, or the next batch starts in the middle of the old one.
- Committing a batch that failed. The implicit transaction rolls back at Sync.
- Two namespaces. Parse and PREPARE share one, and so must Close and DEALLOCATE.
- Re-running a suspended portal from the start. It continues where it stopped.
- Pasting bound values into SQL text. That brings back exactly the bug this protocol exists to remove.
Language notes
The page above is the same for every language. These are the only language-specific hints.
- C: portals in a small array per session, with the unnamed one at a fixed slot. A
skippingflag on the connection, cleared by Sync. - C++:
std::unordered_map<std::string, Portal>; keep the executor's iterator inside the portal so a suspended Execute resumes it. - Rust: a
HashMap<String, Portal>; a portal owning a boxed iterator (Box<dyn Iterator<Item = Row>>) makes PortalSuspended a matter of callingnextagain later. - Zig: a
std.StringHashMap(Portal)in the session, cleared at each transaction's end.
Checklist
One line per thing the tests check. The tags name the test cases.
- Parse, Bind, Execute and Sync run a statement, with no RowDescription from Executebasic-flow
- Describe tells the parameter types and the result's columns, or NoDatadescribe
- The unnamed statement is replaced silently; a named one is made once, 42P05unnamed-vs-named
- Bind checks the count (08P01), the types (22P02) and the statement (26000); Execute checks the portal (34000)bind-errors
- A value of length -1 is NULLnull-parameter
- A bound value is a value, never SQLinjection-is-data
- After an error the server ignores everything up to Syncerror-skips-to-sync
- Execute with a row limit sends that many rows, then PortalSuspendedportal-suspended
- Several Bind and Execute pairs before one Sync answer in orderpipeline
- The messages between two Syncs succeed or fail togetherpipeline-is-one-transaction
- Close forgets a statement, and Flush gets answers without a Syncclose-and-flush
- Parse and PREPARE share the session's statement namessql-and-protocol-share-names
- BEGIN and COMMIT go through Parse, Bind and Execute tootransaction-statements-through-parse
- A failed transaction can be ended with a ROLLBACK through Parse, Bind and Executerollback-through-parse-in-failed-transaction
- Binary format is refused with 0A000binary-refused