• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

AAC Znote Pagseguro API - Problems

Mr.Caffeine

Member
Joined
Nov 4, 2018
Messages
50
Reaction score
14
Hello, I am trying to introduce the Znote Pagseguro API on my website but I have this error with the "pagseguro_retorno.php" page:

PHP:
string(65) "INSERT INTO `znote_pagseguro` VALUES (null, '', 0, '0', '', 0, 0)"
(query - SQL error)
Type: voidQuery (voidQuery is used for update, insert or delete from database)

Incorrect integer value: '' for column 'points' at row 1

I believe it is a incompatibility of MYSQL version because when I test it on my localhost (which has an older mysql version) the code works.

My mysql version: ver8.0.23

The codes I use are the ones that are available in git:

I can pay for the solution.
 

Snavy

Bakasta
Senator
Joined
Apr 1, 2012
Messages
1,243
Solutions
71
Reaction score
570
Location
Hell
All fields of znote_pagseguro have NOT NULL and yet there's null in first value set.
5th column is points which has data type int(11) and 5th value in the first set is '' (string/varchar)

Try to write a more specific query:
SQL:
INSERT INTO `znote_pagseguro`(`columnA`, `columnB`, `columnC`) VALUES(valueA, valueB, valueC)
 
OP
OP
Mr.Caffeine

Mr.Caffeine

Member
Joined
Nov 4, 2018
Messages
50
Reaction score
14
All fields of znote_pagseguro have NOT NULL and yet there's null in first value set.
5th column is points which has data type int(11) and 5th value in the first set is '' (string/varchar)

Try to write a more specific query:
SQL:
INSERT INTO `znote_pagseguro`(`columnA`, `columnB`, `columnC`) VALUES(valueA, valueB, valueC)

Thanks, Snavy.

If anyone can help me adjust this code I pay for the service, feel free to contact me.

Once I have the code working, I will share it with the community
 
OP
OP
Mr.Caffeine

Mr.Caffeine

Member
Joined
Nov 4, 2018
Messages
50
Reaction score
14
PHP:
<?php
    /*
        Instalação
            - Configurando Pagseguro (Acessar https://pagseguro.uol.com.br/preferencias/integracoes.jhtml)
                - Notificação de Transação:
                    - http://you-site/pagseguro_ipn.php
                - Página de redirecionamento:
                    - A. Página fixa de redirecionamento
                        - http://you-site/pagseguro_retorno.php
                    - B. Redirecionamento com o código da transação
                        - transaction
                - Gerar o Token e copiar para a próxima etapa

            - Configurando ZnoteACC
                - config.php
                    - $config['pagseguro']['email']
                        - Seu email da conta do pagseguro que irá receber o pagamento
                    - $config['pagseguro']['token']
                        - Preencher com o Token que pedi pra copiar na primeira etapa
                    - $config['pagseguro']['product_name']
                        - Nome do Produto

            - Instalando Tabelas
                CREATE TABLE IF NOT EXISTS `znote_pagseguro` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `transaction` varchar(36) NOT NULL,
                  `account` int(11) NOT NULL,
                  `price` decimal(11,2) NOT NULL,
                  `points` int(11) NOT NULL,
                  `payment_status` tinyint(1) NOT NULL,
                  `completed` tinyint(4) NOT NULL,
                  PRIMARY KEY (`id`),
                  FOREIGN KEY (account) REFERENCES accounts(id)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

                CREATE TABLE IF NOT EXISTS `znote_pagseguro_notifications` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `notification_code` varchar(40) NOT NULL,
                  `details` text NOT NULL,
                  `receive_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                  PRIMARY KEY (`id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    */

    // Require the functions to fetch config values
    require 'config.php';

    // Require the functions to connect to database
    require 'engine/database/connect.php';

    $pagseguro = $config['pagseguro'];

    // Fetch and sanitize POST and GET values
    function getValue($value) {
        return (!empty($value)) ? sanitize($value) : false;
    }
    function sanitize($data) {
        return htmlentities(strip_tags(mysql_znote_escape_string($data)));
    }

    // Util function to insert log
    function report($code, $details = '') {
        $connectedIp = $_SERVER['REMOTE_ADDR'];
        $details = getValue($details);
        $details .= '\nConnection from IP: '. $connectedIp;
        mysql_insert('INSERT INTO `znote_pagseguro_notifications` VALUES (null, \'' . getValue($code) . '\', \'' . $details . '\', CURRENT_TIMESTAMP)');
    }

    function VerifyPagseguroIPN($code) {
        global $pagseguro;
        $url = $pagseguro['urls']['ws'];

        $cURL = curl_init();
        curl_setopt($cURL, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($cURL, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($cURL, CURLOPT_URL, 'https://' . $url . '/v3/transactions/' . $code . '?email=' . $pagseguro['email'] . '&token=' . $pagseguro['token']);
        curl_setopt($cURL, CURLOPT_HEADER, false);
        curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($cURL, CURLOPT_FORBID_REUSE, true);
        curl_setopt($cURL, CURLOPT_FRESH_CONNECT, true);
        curl_setopt($cURL, CURLOPT_CONNECTTIMEOUT, 30);
        curl_setopt($cURL, CURLOPT_TIMEOUT, 60);
        curl_setopt($cURL, CURLINFO_HEADER_OUT, true);
        curl_setopt($cURL, CURLOPT_HTTPHEADER, array(
            'Connection: close',
            'Expect: ',
        ));
        $Response = curl_exec($cURL);
        $Status = (int)curl_getinfo($cURL, CURLINFO_HTTP_CODE);
        curl_close($cURL);

        return trim($Response);
    }




    $transactionCode = getValue($_GET['transaction']);
    $rawTransaction = VerifyPagseguroIPN($transactionCode);
    $transaction = simplexml_load_string($rawTransaction);

    $transactionStatus = (int) $transaction->status;
    $completed = ($transactionStatus != 7) ? 0 : 1;

    $custom = (int) $transaction->reference;
    $item = $transaction->items->item[0];
    $points = (int) $item->quantity;
    $price = $points * ($pagseguro['price'] / 100);
    mysql_insert('INSERT INTO `znote_pagseguro` VALUES (null, \'' . sanitize($transaction->code) . '\', ' . $custom . ', \'' . $price . '\', \'' . $points . '\', ' . $transactionStatus . ', ' . $completed . ')');

    //print_r($rawTransaction);
    //print_r($_GET);
    header('Location: shop.php?callback=processing');

So @wagmaster helped me with the code. Tables are now being inserted but without getting the values:

Sem título.png

I have the same problem that this guy had (still unsolved):

The table 'znote_pagseguro_notifications' works fine, values are added correctly via ipn.
 
Last edited:

Znote

<?php echo $title; ?>
Staff member
Global Moderator
Premium User
Joined
Feb 14, 2008
Messages
6,947
Solutions
252
Reaction score
1,890
Location
Norway
GitHub
Znote
Rewrote both queries:
PHP:
<?php
    /*
        Instalação
            - Configurando Pagseguro (Acessar https://pagseguro.uol.com.br/preferencias/integracoes.jhtml)
                - Notificação de Transação:
                    - http://you-site/pagseguro_ipn.php
                - Página de redirecionamento:
                    - A. Página fixa de redirecionamento
                        - http://you-site/pagseguro_retorno.php
                    - B. Redirecionamento com o código da transação
                        - transaction
                - Gerar o Token e copiar para a próxima etapa

            - Configurando ZnoteACC
                - config.php
                    - $config['pagseguro']['email']
                        - Seu email da conta do pagseguro que irá receber o pagamento
                    - $config['pagseguro']['token']
                        - Preencher com o Token que pedi pra copiar na primeira etapa
                    - $config['pagseguro']['product_name']
                        - Nome do Produto

            - Instalando Tabelas
                CREATE TABLE IF NOT EXISTS `znote_pagseguro` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `transaction` varchar(36) NOT NULL,
                  `account` int(11) NOT NULL,
                  `price` decimal(11,2) NOT NULL,
                  `points` int(11) NOT NULL,
                  `payment_status` tinyint(1) NOT NULL,
                  `completed` tinyint(4) NOT NULL,
                  PRIMARY KEY (`id`),
                  FOREIGN KEY (account) REFERENCES accounts(id)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

                CREATE TABLE IF NOT EXISTS `znote_pagseguro_notifications` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `notification_code` varchar(40) NOT NULL,
                  `details` text NOT NULL,
                  `receive_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
                  PRIMARY KEY (`id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    */

    // Require the functions to fetch config values
    require 'config.php';

    // Require the functions to connect to database
    require 'engine/database/connect.php';

    $pagseguro = $config['pagseguro'];

    // Fetch and sanitize POST and GET values
    function getValue($value) {
        return (!empty($value)) ? sanitize($value) : false;
    }
    function sanitize($data) {
        return htmlentities(strip_tags(mysql_znote_escape_string($data)));
    }

    // Util function to insert log
    function report($code, $details = '') {
        $code = getValue($code);
        $connectedIp = $_SERVER['REMOTE_ADDR'];
        $details = getValue($details);
        $details .= '\nConnection from IP: '. $connectedIp;
        mysql_insert("
            INSERT INTO `znote_pagseguro_notifications` 
                (`notification_code`,`details`,`receive_at`)
            VALUES ('{$code}','{$details}', CURRENT_TIMESTAMP);
        ");
    }

    function VerifyPagseguroIPN($code) {
        global $pagseguro;
        $url = $pagseguro['urls']['ws'];

        $cURL = curl_init();
        curl_setopt($cURL, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($cURL, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($cURL, CURLOPT_URL, 'https://' . $url . '/v3/transactions/' . $code . '?email=' . $pagseguro['email'] . '&token=' . $pagseguro['token']);
        curl_setopt($cURL, CURLOPT_HEADER, false);
        curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($cURL, CURLOPT_FORBID_REUSE, true);
        curl_setopt($cURL, CURLOPT_FRESH_CONNECT, true);
        curl_setopt($cURL, CURLOPT_CONNECTTIMEOUT, 30);
        curl_setopt($cURL, CURLOPT_TIMEOUT, 60);
        curl_setopt($cURL, CURLINFO_HEADER_OUT, true);
        curl_setopt($cURL, CURLOPT_HTTPHEADER, array(
            'Connection: close',
            'Expect: ',
        ));
        $Response = curl_exec($cURL);
        $Status = (int)curl_getinfo($cURL, CURLINFO_HTTP_CODE);
        curl_close($cURL);

        return trim($Response);
    }

    $transactionCode = getValue($_GET['transaction']);
    $rawTransaction = VerifyPagseguroIPN($transactionCode);
    $transaction = simplexml_load_string($rawTransaction);

    $transactionStatus = (int) $transaction->status;
    $completed = ($transactionStatus != 7) ? 0 : 1;

    $custom = (int) $transaction->reference;
    $item = $transaction->items->item[0];
    $points = (int) $item->quantity;
    $price = $points * ($pagseguro['price'] / 100);
    $transactionCode = sanitize($transaction->code);
    
    mysql_insert("
        INSERT INTO `znote_pagseguro`
            (`transaction`,`account`,`price`,`points`,`payment_status`,`completed`)
        VALUES ('{$transactionCode}','{$custom}','{$price}','{$points}','{$transactionStatus}','{$completed}');
    ");
    
    //print_r($rawTransaction);
    //print_r($_GET);
    header('Location: shop.php?callback=processing');
 
Top