Payment integration

[theochr72]

Hello,

I am building a website with sqlpage for my partner, who wants to offer a homemade food delivery service. Customers will be able to order online and she will deliver the meals.

I chose sqlpage because I am already a bit used to working with it, and it allows me to make progress without having to do much development. Thank you for this project, it is very useful. Thanks you so much for all.

My question is: have you ever considered adding payment integration in sqlpage?

If not, do you think it would be better to use an external API (like Stripe) or simply redirect users to an external payment link?

What would you suggest?

[lovasoa]

Hi ! Looking at the stripe API, I think you have everything you need to integrate with it directly from sqlpage.

https://docs.stripe.com/api/checkout/sessions/create?lang=curl

Here is what SQL ai from https://editor.datapage.app suggests (fully ai generated, not tested, probably needs tweaking):

create table if not exists stripe_sessions (
    session_id text primary key,
    payment_status text,
    status text,
    amount integer, -- Amount in cents
    currency text,
    created_at timestamp default current_timestamp
    -- you could add a foreign key to your products here
);

-- create_checkout.sql
-- Define your Stripe secret key (replace with actual secure method in prod)
SET STRIPE_SECRET_KEY = 'sk_test_...';
-- !! Replace with your actual Stripe Secret Key, or use an environment variable !!

-- Define the base URL for redirects back to your app
SET YOUR_DOMAIN = 'http://localhost:8080'; -- Adjust if your SQLPage instance runs on a different URL

-- Construct the success and cancel URLs for Stripe to redirect to
SET SUCCESS_URL = $YOUR_DOMAIN || '/return_payment.sql?session_id={CHECKOUT_SESSION_ID}';
SET CANCEL_URL = $YOUR_DOMAIN || '/return_payment.sql?session_id={CHECKOUT_SESSION_ID}';

-- Prepare the form-urlencoded body for the Stripe API call
SET REQUEST_BODY =
  'mode=' || sqlpage.url_encode('payment') ||
  '&success_url=' || sqlpage.url_encode($SUCCESS_URL) ||
  '&cancel_url=' || sqlpage.url_encode($CANCEL_URL) ||
  '&line_items[0][price_data][currency]=' || sqlpage.url_encode('usd') ||
  '&line_items[0][price_data][product_data][name]=' ||
  sqlpage.url_encode('Test Product') ||
  '&line_items[0][price_data][unit_amount]=' || sqlpage.url_encode('1000') || -- $10.00 USD
  '&line_items[0][quantity]=' || sqlpage.url_encode('1');

-- Make the POST request to Stripe to create a Checkout Session
SET STRIPE_RESPONSE = sqlpage.fetch(JSON_OBJECT(
  'url', 'https://api.stripe.com/v1/checkout/sessions',
  'method', 'POST',
  'body', $REQUEST_BODY,
  'headers', JSON_OBJECT(
    'Authorization', 'Bearer ' || $STRIPE_SECRET_KEY,
    'Content-Type', 'application/x-www-form-urlencoded'
  )
));

-- Insert the session details into your local database if successful
INSERT INTO stripe_sessions (
  session_id, payment_status, status, amount, currency
)
SELECT
  $STRIPE_RESPONSE->>'id',
  $STRIPE_RESPONSE->>'payment_status',
  $STRIPE_RESPONSE->>'status',
  1000,
  'usd'
WHERE $STRIPE_RESPONSE->>'id' IS NOT NULL;

-- Redirect to the Stripe Checkout page if a URL was received
SELECT 'redirect' AS component, $STRIPE_RESPONSE->>'url' AS link
WHERE $STRIPE_RESPONSE->>'url' IS NOT NULL;

-- If there was an error and no redirect happened, display an alert
SELECT 'alert' AS component, 'Error' AS title,
       'Failed to create Stripe Checkout session: ' ||
       $STRIPE_RESPONSE AS description,
       'red' AS color
WHERE $STRIPE_RESPONSE->>'url' IS NULL;

-- return_payment.sql
-- Define your Stripe secret key (replace with actual secure method in prod)
SET STRIPE_SECRET_KEY = 'sk_test_...';
-- !! Replace with your actual Stripe Secret Key !!

-- Get the session_id from the URL parameter
SET SESSION_ID = $session_id;

-- Make a GET request to Stripe to retrieve the session status
SET STRIPE_RESPONSE = sqlpage.fetch(JSON_OBJECT(
  'url', 'https://api.stripe.com/v1/checkout/sessions/' || $SESSION_ID,
  'headers', JSON_OBJECT('Authorization', 'Bearer ' || $STRIPE_SECRET_KEY)
));

-- Update the session in your local database
UPDATE stripe_sessions
SET
  payment_status = $STRIPE_RESPONSE->>'payment_status',
  status = $STRIPE_RESPONSE->>'status'
WHERE session_id = $SESSION_ID;

-- Display success or failure message based on the payment status
SELECT 'alert' AS component,
       CASE WHEN $STRIPE_RESPONSE->>'payment_status' = 'paid' THEN 'Payment Succeeded!'
            ELSE 'Payment Failed or Cancelled.'
       END AS title,
       CASE WHEN $STRIPE_RESPONSE->>'payment_status' = 'paid' THEN 'Thank you for your purchase.'
            ELSE 'Your payment could not be processed or was cancelled.'
       END AS description,
       CASE WHEN $STRIPE_RESPONSE->>'payment_status' = 'paid' THEN 'green' ELSE 'red' END AS color;

-- Add a button to go back to the home page or view orders
SELECT 'button' AS component, 'Return to Home' AS title, 'index.sql' AS link;

[lovasoa]

I added sqlpage.hmac for signed webhooks in 0.38 https://sql-page.com/functions.sql?function=hmac#function

[theochr72]

Hey!

Sorry, I haven’t had much time lately.

I still need to test the payment method for the Stripe API — I should be able to do that sometime next week.

Thanks a lot for the feature, and I’m really looking forward to v0.38.

Thanks again, and have a great day!

[Floflobel]

For information regarding Stripe, everything is working quite smoothly on my end.

Thank you very much for adding this feature.

[theochr72]

As for me, I used Payzen, and it’s much more complicated to use the sqlpage.hmac function. Their calculation system uses all the return variables in the URL (GET or POST), which must then be sorted alphabetically, then the key must be added. After that, you compute the HMAC of everything and check that it matches the signature.

Unfortunately, as soon as I retrieve the data to sort it, the encoding changes. It becomes impossible to obtain a signature identical to the one Payzen provides.

I had to use sqlpage.exec and rely on an external Python script.

Thanks again for the feature, but my use case was unfortunately too specific.

[lovasoa]

Unfortunately, as soon as I retrieve the data to sort it, the encoding changes.

Can you be more specific? This sounds like something that can be fixed

[theochr72]

Here is the procedure provided by PayZen for computing the signature:

https://payzen.io/en-EN/form-payment/quick-start-guide/computing-the-signature.html

I am convinced that the issue comes from an encoding change when I transform the vads_xxx values returned by the site. To sort them, I was doing something like this:

Here is an example of a problematic value: vads_brand_management: {"userChoice":false,"brandList":"CB|MASTERCARD","brand":"CB"}

and this my way to order the values

SET raw_data = (
  WITH params AS (
    SELECT key, value
    FROM json_each_text(sqlpage.variables('POST')::json)
  ),
  vads_fields AS (
    SELECT substring(key from 6) AS sort_key, value
    FROM params
    WHERE key LIKE 'vads_%'
      AND key != 'vads_signature'
  )
  SELECT string_agg(value, '+' ORDER BY sort_key) || '+' || $secret_key
  FROM vads_fields
);

I compared the result with Python, and this following method works correctly. Here is what gives me the correct result:

SET params_for_verification = (
  SELECT json_object_agg(key, value)
  FROM json_each_text($all_params::json)
  WHERE key != 'signature'
    AND key != 'secret_key'
    AND key LIKE 'vads_%'
);

SET verification_result = sqlpage.exec(
  '/srv/demo-sqlpage-1/verify_payzen.py',
  $params_for_verification,
  $signature_recue,
  $secret_key
);

And here is the Python script:

#!/usr/bin/env python3
import hmac
import hashlib
import base64
import sys
import json

def verify_payzen(params_json, signature_recue, certificate_key):
    """Verify the PayZen signature and return VALID or INVALID"""
    try:
        params = json.loads(params_json)

        # Filter vads_* parameters (except vads_hash)
        vads_params = {k: v for k, v in params.items()
                       if k.startswith('vads_') and k != 'vads_hash'}
        
        # Sort and concatenate
        sorted_keys = sorted(vads_params.keys())
        signature_string = '+'.join([str(vads_params[k]) for k in sorted_keys])
        signature_string += '+' + certificate_key

        # Compute HMAC-SHA256
        computed_hash = hmac.new(
            certificate_key.encode('utf-8'),
            signature_string.encode('utf-8'),
            hashlib.sha256
        ).digest()

        computed_signature = base64.b64encode(computed_hash).decode('utf-8')

        # Secure comparison
        if hmac.compare_digest(computed_signature, signature_recue):
            return "VALID"
        return "INVALID"
    except Exception as e:
        return f"ERROR: {str(e)}"

if __name__ == "__main__":
    if len(sys.argv) != 4:
        print("ERROR: Usage: verify_payzen.py <params_json> <signature> <key>")
        sys.exit(1)

    result = verify_payzen(sys.argv[1], sys.argv[2], sys.argv[3])
    print(result)
    sys.exit(0 if result == "VALID" else 1)

We need to retrieve the raw POST or GET data from the URL and sort the parameters without altering their original format. I believe this would make the signature verification work correctly.

[theochr72]

I can send you privately the values that caused the issue so you can run tests on your side, if you want.