How to call external API from SQL server trigger

Enabling Advanced Options and xp_cmdshell in SQL Server

---

SQL Server is a relational database management system that supports various advanced features.
However, some of these features are disabled by default for security reasons.
In this blog post,
we will learn how to enable advanced options and xp_cmdshell in SQL Server and use them to call an
external API inside a trigger.

---

Enabling Advanced Options

To enable advanced options in SQL Server, execute the following code:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

The above code enables advanced options, including xp_cmdshell, which is disabled by default.

Enabling xp_cmdshell

Note: that using xp_cmdshell to run external commands from within SQL Server can pose security risks and should be used
with caution.
Make sure to thoroughly vet and validate any external commands or APIs before using them within SQL Server.

The xp_cmdshell is a system stored procedure in SQL Server that allows running command-line
commands on the operating system. However, enabling xp_cmdshell can be a security risk as it can
allow malicious users to execute harmful commands. Therefore, it should be enabled only when needed.

To enable xp_cmdshell, execute the following code:

sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

The above code enables xp_cmdshell, allowing you to run external commands from within SQL Server.

Using CURL for API Requests

Once xp_cmdshell is enabled, you can use it to run external commands from within SQL Server.
One way to make HTTP requests to external APIs is to use the command-line tool curl.

Here's an example of how to use curl to send a request and receive a response within a SQL Server trigger:

CREATE TRIGGER MyTrigger ON MyTable
AFTER INSERT
AS
BEGIN
    DECLARE @url VARCHAR(1000);
    DECLARE @cmd VARCHAR(2000);
    DECLARE @response VARCHAR(MAX);

    -- set the URL to the API endpoint
    SET @url = 'https://api.example.com/some-endpoint';

    -- build the curl command with necessary parameters
    SET @cmd = 'curl -X POST -H "Content-Type: application/json" -d ''{ "param1": "value1", "param2": "value2" }'' ' + @url;

    -- execute the curl command and store the response
    EXEC @response = xp_cmdshell @cmd;

    -- process the response as needed
    -- ...
END

In the above code, we create a trigger named MyTrigger on a table named MyTable, which runs after an insert operation
on the table. Within the trigger, we declare three variables, @url, @cmd, and @response, which will be used to store the
API endpoint URL, the curl command, and the API response, respectively.

We set the @url variable to the API endpoint URL, and then we build the curl command in the @cmd variable.
In this example, we are using the POST method, sending a JSON payload with two parameters, "param1" and "param2"
and setting the "Content-Type" header to "application/json."

We then execute the curl command using the xp_cmdshell stored procedure, and store the API response in the @response
variable.

Finally, we can process the API response as needed within the trigger.

That's pretty much it!
For more articles like this, follow my blog at JuniorDev Blog
Thanks for reading :)

Comments

[Lucas Barret]

Really Cool ! I did not know it was possible to make API call in SQL , I'm gonna see if it work in PostgreSQL too.

[Zulfiqar Ali]

Yea i also found this out recently when i had to work on a project and needed a way to trigger my backend whenever a row was inserted, my first call was to go towards CDC with debezium but the project was really simple and i ended up with this solution.