Getting Markdown Into SQL Without Breaking the Query

I was building the backend for a Terms and Conditions feature. The shape of it was simple: a table holding every published version, with the application showing whichever version was current.

CREATE TABLE dbo.TermsAndConditions
(
    Id      int IDENTITY PRIMARY KEY,
    Version int           NOT NULL,
    Content nvarchar(max) NOT NULL
);

The API built on top of it only had permission to read that table. It could not INSERT or UPDATE, which was deliberate. Publishing legal content was meant to happen through a separate production process rather than through the public-facing API.

Basically, whenever legal approved a new document, a database team applied it manually cos that was deemed "safer".

I had worked on the migrations adding the actual table, and my next job was to write the instructions for those folks updating it.

The content was Markdown, so the obvious approach was to put the whole thing inside an INSERT:

INSERT INTO dbo.TermsAndConditions (Version, Content)
VALUES (14, '# Terms and Conditions

## Cancellation and refunds

You may cancel your subscription at any time. Your access will continue until the end of the current billing period.

Refunds are only available where required by law.');

That runs cleanly.

Until legal decides to change the refund wording:

INSERT INTO dbo.TermsAndConditions (Version, Content)
VALUES (14, '# Terms and Conditions

## Cancellation and refunds

You may cancel your subscription at any time. Your access will continue until the end of the current billing period.

We will refund the current period's charges where required by law.');

On SQL Server, that now fails:

Msg 105, Level 15, State 1, Line 8
Unclosed quotation mark after the character string ');'.

The apostrophe in period's has been interpreted as SQL syntax rather than document content.

That particular error is easy to fix.

The more useful question is why the Markdown was being interpreted as SQL at all.

The apostrophe is a symptom, not the problem

A Markdown document is data.

The moment you paste it between SQL delimiters, some of its characters also acquire meaning in the language carrying it.

An error showing up for an apostrophe is actually one of the better outcomes.

A worse failure is the one that succeeds. The statement runs, and the row is inserted, and nobody notices that the text changed on the way in. A curly apostrophe might become ? because the value crossed a non-Unicode boundary. No error, but a rough outcome indeed.

So the goal is not merely to make the SQL parse.

The goal is to make sure the text entering the database is exactly the text you intended to publish.

The awkward part is getting the string into SQL

One thing that surprised me while researching this was that there is not really a universal way to hand a database a large block of text and say, “turn this into a safe SQL string literal for me”.

At first, I expected there to be some built-in function that would take care of the escaping.

Some databases do have functions that sound close. PostgreSQL has quote_literal(), for example, and MySQL has QUOTE(). SQL Server has various string escaping functions too, although its STRING_ESCAPE() function is intended for formats such as JSON rather than escaping SQL string literals.

The catch is that these functions operate on values the database has already received.

That does not help much when the problem is getting the value into the SQL statement in the first place.

For example, if I try to write something conceptually like:

SELECT some_escape_function(
    'We will refund the current period's charges.'
);

the SQL parser still sees the apostrophe in period's before the function ever gets a chance to run.

That was the part I initially found unintuitive. Once the Markdown is already stored in a variable, passed as a parameter, or read from somewhere the database can access, handling it as text is straightforward. The awkward part is representing arbitrary text safely inside the SQL source code that creates that value.

That is also why the solutions tend to move the problem somewhere else, which I researched and explored below.

Possible solutions

The database team is still applying this change manually, so the first question is how much of that process we actually want to change.

At one end, we can keep the existing process and simply stop asking somebody to escape a large Markdown document by hand. At the other, we can change how the content reaches the database so that it never has to become part of a SQL statement in the first place.

I would think about the options in that order.

Keep the Markdown in a separate file

Another option is not to put the document inside the SQL statement at all.

Instead of turning the Markdown into a string literal, keep it as a .md file and have the database server read that file.

Conceptually, the process becomes:

flowchart TD A["terms-and-conditions.md"] --> B["database server reads the file"] B --> C["content variable"] C --> D["INSERT"]

The important distinction here is between the database client and the database server.

Tools such as SQL Server Management Studio, MySQL Workbench, and DataGrip can submit the SQL, but they do not change where a server-side file operation takes place. If the SQL says to read D:\deploy\terms-and-conditions.md, that normally refers to a file visible to the database server, not a file with that path on the engineer's laptop.

For example, SQL Server can read a file using OPENROWSET(BULK...). Assuming the Markdown file is stored in a Unicode format compatible with SINGLE_NCLOB, the query could look like this:

DECLARE @content nvarchar(max) = (
    SELECT BulkColumn
    FROM OPENROWSET(
        BULK N'D:\deploy\terms-and-conditions.md',
        SINGLE_NCLOB
    ) AS source
);

INSERT INTO dbo.TermsAndConditions (Version, Content)
VALUES (14, @content);

OPENROWSET(BULK...) supports files on local disks and network shares, so this could also point to a UNC path such as:

\\fileserver\deploy\terms-and-conditions.md

The relevant SQL Server security context must then have permission to read that location. For example, when SQL Server authentication is used, Microsoft documents that access is performed using the SQL Server process account.

So if SSMS is running on a developer workstation but is connected to a SQL Server on another machine, this:

D:\deploy\terms-and-conditions.md

means the D: drive on the SQL Server machine.

SSMS itself is not loading the Markdown file. It is simply sending the OPENROWSET statement to SQL Server.

The same idea exists in MySQL. If the query is being run through MySQL Workbench, for example, MySQL's LOAD_FILE() function can read a file from the MySQL server:

SET @content = CONVERT(
    LOAD_FILE('/var/lib/mysql-files/terms-and-conditions.md')
    USING utf8mb4
);

INSERT INTO TermsAndConditions (Version, Content)
VALUES (14, @content);

Again, that path is on the machine running MySQL, not necessarily the machine running Workbench.

MySQL requires the file to be readable by the server, and the database user needs the FILE privilege. The server's secure_file_priv setting can also restrict file access to a particular directory, or disable this kind of file access entirely.

DataGrip works similarly because it supports multiple database engines rather than defining its own SQL file-loading syntax. When connected to SQL Server, you could execute the OPENROWSET version. When connected to MySQL, you could execute the LOAD_FILE() version.

DataGrip can itself open and execute local SQL script files against a selected data source, but that is different from making an arbitrary local Markdown file visible to the database server.

Once the file has been read, an apostrophe such as:

period's

is no longer SQL syntax. It is simply data contained in the file.

That removes the string-literal escaping problem completely.

There are two main catches.

First, the database server needs access to the file. A path on the developer's workstation will not normally work unless that location is exposed to the server, for example through an accessible network share.

Second, the file needs to be decoded using the correct character encoding. SQL Server, MySQL, and other database systems have different mechanisms for controlling how file contents are interpreted. In particular, SINGLE_NCLOB in SQL Server is intended for wide-character Unicode input, so a UTF-8 Markdown file may require different import settings.

This approach can therefore be very clean when the deployment environment already has a controlled location from which the database server is permitted to read files, but awkward in a locked-down production environment.

Generate the SQL from the Markdown

The approach I've researched is to write a small program that reads the Markdown file and generates the SQL automatically. The Markdown becomes the input, and if you want to optimise it further, have it generate a sql query with a .sql file as the output.

Its job is simply to read the .md file, escape the text according to the string-literal rules of the target database, insert that escaped text into a SQL template, and write the completed statement to a .sql file.

A minimal generator in C#, which could run in a terminal, could look like this:

using System.Text;

const string inputPath = "terms-and-conditions.md";
const string outputPath = "terms-and-conditions.sql";
const int version = 14;

var markdown = File.ReadAllText(inputPath, Encoding.UTF8);

// SQL Server represents an apostrophe inside a string
// literal by writing it twice.
var escapedMarkdown = markdown.Replace("'", "''");

var sql = $"""
INSERT INTO dbo.TermsAndConditions (Version, Content)
VALUES ({version}, N'{escapedMarkdown}');
""";

File.WriteAllText(outputPath, sql, Encoding.UTF8);

There is not much code here, and that is part of the appeal.

If the Markdown contains:

The customer's subscription continues until the end
of the current period's billing cycle.

the generator produces:

INSERT INTO dbo.TermsAndConditions (Version, Content)
VALUES (14, N'The customer''s subscription continues until the end
of the current period''s billing cycle.');

Newlines in the Markdown can remain newlines in the generated SQL string. The important transformation for a normal SQL Server string literal is the apostrophe escaping.

The useful part is that this rule now lives in code rather than in somebody's deployment instructions. Nobody has to search a large Terms and Conditions document looking for apostrophes or remember to escape them manually. Whether the document contains one apostrophe or two hundred, the same transformation is applied every time.

You could also create separate utility scripts for handling database-specific rules, whether for SQL Server, PostgreSQL, MySQL, or whichever systems it targets. Or even include parameters in a single script that's able to handle different dialects.

The generator itself does not run inside SSMS, DataGrip, or another database client. It is simply an earlier step that prepares the SQL before the statement reaches the database.

It does not normally need to run on the database server either. It could run on a developer workstation, a build server, in a CI pipeline, or on the workstation of the person preparing the database change.

That distinction matters because introducing a generator also introduces an operational question. Somebody has to run it.

If the database team currently receives only the Terms and Conditions document and then performs the update manually, there are two obvious ways to fit the generator into that process.

The first is to run the generator before the hand-off and give the database team both files:

terms-and-conditions.md
terms-and-conditions.sql

The Markdown remains the document they can review, while the generated SQL is the deployment artefact they can open in SSMS, DataGrip, or another database client and execute.

In that model, the database team does not need access to the source repository, the generator, or the language in which it was written. They only need the generated .sql file, which they can review and run to perform the database update.

The second option is to give the database team the generator and ask them to run it themselves.

In that case, the machine running the generator needs the appropriate tooling. A Python generator requires Python. A PowerShell script requires a suitable PowerShell environment. A C# generator normally requires .NET unless it has been published as a self-contained executable.

That requirement applies to the machine running the generator, not necessarily to the database server.

This is an important trade-off. The generator removes the fragile manual escaping step, but it adds another tool and another step to the deployment process.

In an environment with an existing build or release pipeline, that additional step can be almost invisible. The pipeline can read the Markdown and produce the .sql file automatically.

In a highly manual environment, however, somebody needs to know that the generator exists, know how to run it, and make sure the generated SQL is the version that actually reaches the database team.

That means the most practical workflow may be to keep the Markdown as the source of truth and generate the SQL before the database hand-off:

flowchart TD A["legal updates Markdown"] --> B["generator produces SQL"] B --> C["database team receives both files"] C --> D["database team reviews and runs SQL"]

The generated SQL should normally not be edited by hand. If the Terms and Conditions change, the Markdown should be updated and the SQL generated again.

So the main benefit of a generator is not that it is the most elegant way to insert text into a database. It is that it preserves an existing SQL-based deployment process while moving the error-prone string escaping out of human hands and into repeatable code.

Let the utility perform the update directly

Instead of generating a .sql file, the utility could go one step further and connect to the database itself. In that version, you build an app that reads the Markdown file and executes a parameterised command such as INSERT INTO dbo.TermsAndConditions (Version, Content) VALUES (@version, @content), passing the document as the @content value rather than converting it into a SQL string literal. That removes the need to escape the document for SQL and also removes the second human step of opening and running the generated script. The trade-off is that the utility now needs database connectivity and appropriate credentials, so it becomes part of the deployment process rather than simply a tool for preparing a script for the database team.

Wrap up

What started as a problem with an apostrophe turned out to be more about where the boundary sits between data and SQL.

Markdown is just text, and databases are perfectly capable of storing it. The problems begin when that text is also expected to live safely inside the SQL statement used to insert it.

There is no single best solution for every setup. You can let the database read the content from a file, generate the SQL beforehand, or move the write into a small utility and pass the document as a parameter.

For me, the important part is to avoid making somebody manually prepare a large document for SQL every time it changes. The less humans have to think about escaping rules, encodings, and quoting, the less likely it is that the published content differs from the document that was actually approved.