I create a simple view that works on phpmyadmin
SELECT name FROM users
when i use this in serverconnect i get this error in the browser
SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared
What version of PHP are you using and what version of MySQL is installed on your server?
| PHP Version | 7.0.30 |
|---|---|
| MySQL Version | 10.2.16-MariaDB |
So you are using Mariadb server and not MySQL server, is that right?
yes
and they are the same its just the licensee difference as far as i know
Is it possible to check what version of myslq driver are you using exactly?
i dont know how to get those from cPanel but i found this:
- Database client version: libmysql - 5.1.73
- PHP version: 5.6.30
Please create an empty php file in your site root, and add this inside it:
<?php phpinfo() ?>
Upload it to your server and send me a link to it, in a private message - i will check the config.
That is a bug with MySQL/MariaDB, it only happens with views. The solution is to use the option PDO::ATTR_EMULATE_PREPARES => true with PDO, but that causes some security issues and also the results will all be strings then. So an integer table will not return as a number, but as a string. We currently don’t support setting this option, it would also probably break some other actions in Server Connect.
I currently have no solution for this, but will see if there maybe is some workaround. They will not fix this in MySQL, so our only option will probably be to use the emulated prepares with the risk of sql injections.
1 Like
the return type is not a big issue since we can convert to the proper type in wappler.
but how big the risk with SQL injections?
With the emulated prepares you also have some protection for SQL injection, but it is less robust then the native implementation in the database drivers that support it. PDO already falls back to emulated prepares when the driver doesn’t support prepared statements.
Even with the native implementation you are not always secure, I found a nice Stack Overflow topic about it https://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection.
But that is on earlier versions of MySQL and it need specific character encoding also.
im sure you guys have an additional protection on top of the driver
1 Like
I only get this error on my live server. My local server works fine with views (v 5.7.41). Live server is v 5.6.41
How do I set PDO::ATTR_EMULATE_PREPARES => true ?
dmxConnectLib\lib\db\Connection.php
Line 44 (~)
Having same issue now
PHP V7.0 and 7.3
MariaDB 10.2.18
Driver Version 5.0.12 (i think- not 100%)
Can send a phpinfo() link if you need it
@nshkrsh fix works for now, i assume that will be overwritten when Wappler updates?
Could you copy the output of phpinfo() - specifically the PDO and pdo_mysql sections
PM’d a link
It won’t get overwritten. There have been multiple updates to Wappler since I made this change in my code.
I did a lot of research on this issue a month ago and had a conversation with @patrick too, who suggested the change.
Here is one of the reasons why Wappler does not support this, as explained by Patrick.
An other problem with the emulate prepares attribute is that the result values are all strings, for some reason it outputs all the numbers as strings. We had it enabled a long time back, but it gave us more support because of the numbers not being numbers but strings. So we disabled it again since only very few users used views in mysql and that is the only place where the error occurs.
We want to rewrite the code for the database connector to get around this bug and some other bugs with other databases, but it is not high priority at this moment.
If you want to change it in your project, you can enable the option in
/dmxConnectLib/lib/db/Connection.phpline 44.
I also found this official bug report and status on MariaDB’s portal. It was dormant until a month ago, but it seems they are working to resolve it now as I see last updated date as 22nd Jan.
https://jira.mariadb.org/browse/MDEV-17124
2 Likes
It is a ten year old bug in MySQL. They fixed it in MySQL 5.7, older versions are all affected. https://bugs.mysql.com/bug.php?id=42041
MariaDB is a fork of MySQL, it has the same bug. As @nshkrsh mentioned there is an open bug report for it. Seems that MariaDB are still working on the fix.
When having an older unpatched database you can use the workaround by editing the Connection.php file setting PDO::ATTR_EMULATE_PREPARES to true.
1 Like