Getting error when I updated to the latest version - You have an error in your SQL syntax

Wappler General Need Help
1

PHP 8.2
Old version of site running on thats working..
/*!
App Connect
Version: 1.14.12
@build 2023-03-14 12:22:18
*/

I have been running on ver 6.8 for a long time and today i decided to do a full update and code update eg server connect .. the whole shebang… the latest version of everything :slight_smile:

If i go back to the older version and older server connect it works.. .. is this a simple fix??.. or is the error within wappler.. dont know…. im just worried that if its breaking “older” code that was working that it might.. cause other problems for me down the road as well…

any help would be great… as I would love to “update” my site to the latest tech… :slight_smile:

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND `reserved_price` <= ? AND `received_price` < ? AND `item_sold_live` <> ?)' at line 1"

if it might narrow this down
this is the code snippit directly out of the sc file

          "sql": {
            "type": "SELECT",
            "columns": [
              {
                "table": "auction_items",
                "column": "id",
                "recid": 1
              },
              {
                "table": "auction_items",
                "column": "reserved_price",
                "recid": 2
              },
              {
                "table": "auction_items",
                "column": "received_price",
                "recid": 3
              },
              {
                "table": "auction_items",
                "column": "auction_id",
                "recid": 4
              },
              {
                "table": "auction_items",
                "column": "client_id",
                "recid": 5
              }
            ],
            "table": {
              "name": "auction_items"
            },
            "joins": [],
            "wheres": {
              "condition": "AND",
              "rules": [
                {
                  "id": "auction_items.id",
                  "field": "auction_items.id",
                  "type": "double",
                  "operator": "equal",
                  "value": "{{$_POST.id}}",
                  "data": {
                    "table": "auction_items",
                    "column": "id",
                    "type": "number"
                  },
                  "operation": "=",
                  "table": "auction_items"
                },
                {
                  "id": "auction_items.reserved_price",
                  "field": "auction_items.reserved_price",
                  "type": "double",
                  "operator": "less_or_equal",
                  "value": "{{$_POST.received_price}}",
                  "data": {
                    "table": "auction_items",
                    "column": "reserved_price",
                    "type": "number"
                  },
                  "operation": "<=",
                  "table": "auction_items"
                },
                {
                  "id": "auction_items.received_price",
                  "field": "auction_items.received_price",
                  "type": "double",
                  "operator": "less",
                  "value": "{{$_POST.received_price}}",
                  "data": {
                    "table": "auction_items",
                    "column": "received_price",
                    "type": "number"
                  },
                  "operation": "<",
                  "table": "auction_items"
                },
                {
                  "id": "auction_items.item_sold_live",
                  "field": "auction_items.item_sold_live",
                  "type": "string",
                  "operator": "not_equal",
                  "value": "Yes",
                  "data": {
                    "table": "auction_items",
                    "column": "item_sold_live",
                    "type": "text"
                  },
                  "operation": "<>",
                  "table": "auction_items"
                }
              ],
              "conditional": null,
              "valid": true
            },
            "query": "SELECT id, reserved_price, received_price, auction_id, client_id\nFROM auction_items\nWHERE id = :P1 /* {{$_POST.id}} */ AND reserved_price <= :P2 /* {{$_POST.received_price}} */ AND received_price < :P3 /* {{$_POST.received_price}} */ AND item_sold_live <> 'Yes'",
            "params": [
              {
                "operator": "equal",
                "type": "expression",
                "name": ":P1",
                "value": "{{$_POST.id}}"
              },
              {
                "operator": "less_or_equal",
                "type": "expression",
                "name": ":P2",
                "value": "{{$_POST.received_price}}"
              },
              {
                "operator": "less",
                "type": "expression",
                "name": ":P3",
                "value": "{{$_POST.received_price}}"
              }
            ],
            "orders": []
          }

i have also “opened” this query in Wappler (new version) and saved it.. so i have no idea…

sql q1
sql q11152×648 91.2 KB

3

When I've seen this error, it's because the parameters aren't set up correctly. Have you checked the Parameters tab to make sure they are still there and correct? Do you still have test values for each parameter so you can execute a test query?

I'm still on v6.8 because I don't have the time to regression test my entire site.

4

what i wil do .. i will make a side by side comparision with screenshots of the “older” and “new” version. Where i have not updated any code but just applied the “updates” from wappler extentions/compents. And then i will remove everything from that sc file 1 by 1 from the top and see why it might cause this, if all fails.. just redo the query and see :slight_smile:

5

So this is just to troubleshoot… and see what might cause this if others run into the same in future..

So the “older” code as is, that is working for me…

q1-old
q1-old1238×625 120 KB

q2-old
q2-old1238×625 123 KB

q3-old
q3-old1238×625 118 KB

result-old
result-old1591×800 280 KB

Then when just doing updates not changing any code.. i get this weird error.

result-new
result-new1920×1080 314 KB

q1-new
q1-new1026×601 28.8 KB

q2-new
q2-new1163×622 35.5 KB

q3-new
q3-new1160×616 33.7 KB

so next step will be to delete the complete query and redo it again.. to see if this is the problem.. if not.. then will delete – step by step :slight_smile: …

6

OK…. Found why its not working.. I guess i did not get the memo.. :slight_smile: or its jus the way it works now.

All my POST values are set to NUMBER… but they dont work in the query.

post_as_number
post_as_number346×365 3.29 KB

So in the “updated” version i have to add …. .toNumber from my POST values… I was assuming it would “pick it up as a number from the POST if set as Number” .. but what now worked.. i had to set every value with a formatter to .toNumber

have to add toNumber
have to add toNumber827×181 4.55 KB

so by adding the .toNumber converted them… and working fine.. was hoping the if the POST vars are set as Number it would do it by itself… :slight_smile:

@patrick is it possible … if the $_POST type is set to say Number.. that i dont have to add… in my query eveytime now $_POST.client_id.toNumber() so that it will just pick up that $_POST.client_id is a number from the type in the $_POST values?

so i guess the question is.. is it possible that when you set the type in the $_POST

"$_POST": [
      {
        "type": "number",
        "name": "client_id"
      },
      {
        "type": "text",
        "name": "auction_token"
      },
      {
        "type": "number",
        "name": "auction_id"
      },
      {
        "type": "number",
        "name": "item_id"
      },
      {
        "type": "number",
        "name": "item_lot_number"
      },
      {
        "type": "number",
        "name": "price"
      },

that it will automatily then update the POST values with a .toNumber() if that is now the new way of doing it? so that i can just go into my files and resave them…. the it would check the type on the POST values and if numer or date or text it will update the values by default.. or would this just create anoter disaster for everybody that has now already formatted other things…

"condition": "AND",
              "rules": [
                {
                  "id": "auction_items.id",
                  "field": "auction_items.id",
                  "type": "double",
                  "operator": "equal",
                  "value": "{{$_POST.id}}",   
                  // to  value": "{{$_POST.id.toNumber()}}
                  // as its not picking up that my id is a number :(
                  "data": {
                    "table": "auction_items",
                    "column": "id",
                    "type": "number"
                  },
                  "operation": "=",
                  "table": "auction_items"
                },
                {
                  "id": "auction_items.reserved_price",
                  "field": "auction_items.reserved_price",
                  "type": "double",
                  "operator": "less_or_equal",
                  "value": "{{$_POST.received_price}}",
                  "data": {
                    "table": "auction_items",
                    "column": "reserved_price",
                    "type": "number"
                  },
7

I know the UI is confusing for the server input variables. The type that you set is only as metadata for the data picker, it does not do anything with the data. We don't know which variables are available and it is to the user to let the editor know which variables are being used. For standard HTML form post the variables are always strings, that is how the form sends them. The $_POST can contain other types if it was for example a JSON post, that is why you can select different types.

8

Hi Patrick. Thank you for the reply. I understand that. But does this now mean I have to go and update all my server connect files to use .toNumber() now? As it used to work before without having to do this…. is there an easy solution to this?

eg from this

q2-old
q2-old1238×625 123 KB

now to this

have to add toNumber
have to add toNumber827×181 4.55 KB

9

The behavior for this never changed and is like this since the first Wappler release.

I think the problem is with the database connection, try setting the Prepare Statements option to True in the database connection options. It will then use the PDO prepared statements instead of the database native prepared statements, the native is stricter about the types and doesn't auto cast it.

10

thanks ill give that a go.. and will revert back..

11

just use the option as Patrick suggested, there is no need to revert back ...

12

im using a direct connection — so there is no option for this…
So can I just add "emulatePrepares": true ?? as below

<?php
$exports = <<<'JSON'
{
    "name": "db",
    "module": "dbconnector",
    "action": "connect",
    "options": {
        "server": "mysql",
        "connectionString": "mysql:host=localhost;charset=utf8;sslverify=false;dbname=auctiondb;user=root;password=*****",
        "meta": false,
        "emulatePrepares": true
    }
}
JSON;
?>
13

Set this in Server Connect > Globals > Database Connections > your database connection options

Screenshot 2026-02-20 at 14.09.25
Screenshot 2026-02-20 at 14.09.251824×827 121 KB

1 Like
15

Thank you @patrick and @Teodor

1 Like