medoo select term doesn't work correct

Question

I have the debug modus on and get this

mySQL string:

SELECT "dmd_key","id" 
FROM "keys" 
WHERE "dmd_key" = '140ec37b981042c8549b07d6d4589295' 
  AND "website" = 'test.de'

But that string doesn't work for me. I get a database error message. (the standard message..)

If I change my string into this:

SELECT `id`,`dmd_key` 
FROM `keys` 
WHERE `dmd_key` = '140ec37b981042c8549b07d6d4589295' 
  AND `website` = 'test.de'

I get results. I think I have to change something in my settings but I don't know what.

It is my first time with medoo and I think medoo doesn't love me...

Thanks for your help.

The second code block is correct, in the first one you are using incorrect quotes. What is the problem with using the second? — Pekka, Aug 28 '15 at 18:28
The first block is the output of the db framwork medoo.. So I can't change it... — cgee, Aug 31 '15 at 06:04
It seems hard to believe that a Framework would generate broken code like this, are you sure it doesn't come from your initializing the fields with additional quotes somewhere? — Pekka, Aug 31 '15 at 07:30
Nope. It is the framework. The framework set a option of mysql to convert the double quotes into one back quote. But this convert doesn't work on my server. I don't know why.. It is not a managed server so I can't change settings of mysql. Maybe that is the problem.. — cgee, Aug 31 '15 at 09:50

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

This appears to be a problem within Medoo. You're not the only person seeing the issue (1,2). According to this bug report, Medoo creates mySQL code that escapes table names with double quotes, and then changes a mySQL option to cater for it:

case 'mysql':
    // Make MySQL using standard quoted identifier
    $commands[] = 'SET SQL_MODE=ANSI_QUOTES';

This option seems to be disabled on your web host, rendering Medoo unusable in your environment.

It's tough to put in words how insane this is. A framework should generate SQL that is compatible with the database it runs on. I would switch to a different framework.

edited May 23 '17 at 12:14

Community

1
1

answered Aug 31 '15 at 10:04

Pekka

442,112
142
972
1,088

Yes that is the switch case in the medoo framwork! I found it too. I think I have to switch the framework. Can you recommend one realy small db framework? It should use mysqli.. Thank you for your answer. I'll check it as correct answer after my lunch. – cgee Aug 31 '15 at 10:12
Maybe one comment more.. If I input the SET command and my SQL query in phpmyadmin (SQL with double quotes) it works fine. – cgee Aug 31 '15 at 10:17
Are you using the same login credentials in phpMyAdmin and your script? Then it's strange it doesn't work – Pekka Aug 31 '15 at 10:19
Yes it is the same login data.. If I try to set the comment global like set global SQL_MODE ... It doesn't work. I get a permission denied. – cgee Aug 31 '15 at 10:58
That's not a permission denied. Can you show the exact syntax you are using? – Pekka Aug 31 '15 at 12:23
If I only insert this: SELECT "dmd_key","id" FROM "keys" WHERE "dmd_key" = '140ec37b981042c8549b07d6d4589295' AND "website" = 'test.de' I got a error message below. If I insert SET GLOBAL SQL_MODE=ANSI_QUOTES I got a permission denied message. – cgee Aug 31 '15 at 13:25
Can you quote the exact permission denied message? – Pekka Aug 31 '15 at 13:33
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation – cgee Aug 31 '15 at 13:55
And this works when you log in through phpMyAdmin using the exact same database credentials? That seems strange, if not impossible. Are you really really sure? – Pekka Aug 31 '15 at 15:36
Yes.. If I insert the SET SQL_MODE .. and insert in the same text area my SELECT query it works in phpmyadmin. The Query of the db framework doesn't work. the credentials are the same to 100%. – cgee Sep 01 '15 at 07:46
Not sure why the SET doesn't work in the Medoo script then. Perhaps it is a bug and it never runs, do you have the latest version installed? – Pekka Sep 01 '15 at 07:54
Yes I've the latest version and took a look into the github forum. Some people have the same problem. – cgee Sep 01 '15 at 07:59

score 0 · Answer 2 · answered Aug 17 '16 at 06:28

SET SQL_MODE=ANSI_QUOTES becomes invalid if MySQL restarts and needs to execute it again.

Or you can goto your medoo.php and update functions to use backticks as below:

protected function table_quote($table)
{
    return '`' . $this->prefix . $table . '`';
}

protected function column_quote($string)
{
    preg_match('/(\(JSON\)\s*|^#)?([a-zA-Z0-9_]*)\.([a-zA-Z0-9_]*)/', $string, $column_match);

    if (isset($column_match[ 2 ], $column_match[ 3 ]))
    {
        return '`' . $this->prefix . $column_match[ 2 ] . '`.`' . $column_match[ 3 ] . '`';
    }

    return '`' . $string . '`';
}

medoo select term doesn't work correct

2 Answers2