Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Project Nami 2.2.2 - Weird SQL error... #366

Open
Ozkovacs opened this issue Aug 26, 2019 · 1 comment

Comments

@Ozkovacs
Copy link

commented Aug 26, 2019

Hello there.
I was trying a plugin called "User Login History" - using the Basic version 1.7.3.
One of the pages threw a weird error, and I had a look at the code.
The PHP code is as follows:

            $table = $wpdb->prefix . $this->table_name;
            $sql = " SELECT"
                    . " FaUserLogin.*, "
                    . " UserMeta.meta_value, datediff(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration"
                    . " FROM " . $table . "  AS FaUserLogin"
                    . " LEFT JOIN $wpdb->usermeta AS UserMeta ON ( UserMeta.user_id=FaUserLogin.user_id"
                    . " AND UserMeta.meta_key LIKE  '" . $wpdb->prefix . "capabilities' )"
                    . " WHERE 1=1 ";

The error I get is as follows:

[[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'CASE'. Code - 156]
SELECT TOP 20 FaUserLogin.*, UserMeta.meta_value, dated CASE WHEN f(SECOND THEN FaUserLogin.time_login ELSE FaUserLogin.time_last_seen END as duration FROM tbl_fa_user_logins AS FaUserLogin LEFT JOIN tbl_usermeta AS UserMeta ON ( UserMeta.user_id=FaUserLogin.user_id AND UserMeta.meta_key LIKE 'tbl_capabilities' ) WHERE 1=1 ORDER BY id DESC

Notice that the datediff got replaced by some kind of CASE statement, which doesn't make sense. Is it possible that there is some translation being made on the DB library for "if"?

Thanks!

@summer600

This comment has been minimized.

Copy link

commented Oct 12, 2019

Would rather say there is some string manipulation done on sql statements in an attempt to make them t-sql compatible where "if" is replaced with "case when". Then until the "," that gets replaced with " THEN " and the next "," becomes " ELSE " and the final ")" becomes "END" to make a T-SQL CASE statement from the mysql if() statement

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.