Re: [Open-Source-CAD] TicketsCAD login issue

[Shawn Stoddard]

Try using MySQL version 7. I seem to recall some issues with MD5. It’s not considered secure for passwords any longer. MySQL may have depreciated it, but I’m having a tough time finding that clearly documented. 

On Sun, Jul 14, 2024, at 08:40, Khalaf Almazrouei wrote:

Absolutely! Here's a revised message to the community that's more informative and likely to get helpful responses:

Revised Message

Hello everyone,

I hope you're all doing well. I've successfully installed TicketsCAD, but I'm running into a problem when trying to log in.

The Issue:

When I try to log in as either the "admin" user or the "guest" user, I get this SQL error:

An error occurred in function '': 'mysql query failed' Error occurred in 'login.inc.php' at line '263' Additional info: '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 '('ADMIN') OR `passwd`=MD5('admin') OR `passwd`=MD5('ADMIN')) LIMIT 1' at line 3' Check your MySQL connection and if the problem persists, contact the author. Execution stopped.

My Setup:

• Hosting: cPanel cloud-based Linux server
  
• PHP version: 7.0 (set via Multiple PHP)
  
• MySQL Server version: 8.0.36-cll-lve (MySQL Community Server - GPL)
  
• phpMyAdmin version: 5.2.1
  

--

You received this message because you are subscribed to the Google Groups "Open Source CAD" group.

To unsubscribe from this group and stop receiving emails from it, send an email to open-source-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/open-source-cad/914e8698-db7d-4680-b1b8-41b0855d75bcn%40googlegroups.com.

[Khalaf Almazrouei]

Hi Shawn thanks for your support, I will try to change it and see if it works. Along with that in the meantime I will try to install XAMPP and host temproarey to see 

Do you have any recomnded shared webhosting or VPS to host the CAD system, if so can you please let me know what are the compney names 

[Shawn Stoddard]

Sadly I don’t. I have always self-hosted on Windows behind IIS or on Linux behind NGINX.

To view this discussion on the web visit https://groups.google.com/d/msgid/open-source-cad/6f4f2595-b877-4cd4-b5df-5a0fcbcf00edn%40googlegroups.com.

[Khalaf Almazrouei]

Quick Question is my SQL 5.7 stable or stable with the latest version or the recommendation would be 7.0

[Khalaf Almazrouei]

Thanks Shawn for the update I will try to see if I can host it with my synology system and I hope that in the fuetaer they can update the core to support mysql verison 8.0+

[Khalaf Almazrouei]

✅ Fixed: SQL Syntax Error in login.inc.php on MySQL 8.0 (Shared Hosting)

Hi everyone,
I hope you're all doing well. If you're facing the same error I encountered when running Tickets CAD on a shared hosting environment, here's how I resolved it.

---

❌ Error Message: An error occurred in function '': 'mysql query failed' Error occurred in 'login.inc.php' at line '263' Additional info: '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 '('ADMIN') OR `passwd`=MD5('admin') OR `passwd`=MD5('ADMIN')) LIMIT 1' at line 3' Check your MySQL connection and if the problem persists, contact the author. Execution stopped.

---

✅ Hosting Environment:

• Hosting: cPanel-based Linux shared hosting

• PHP Version: 7.4 (configured via MultiPHP Manager)

• MySQL Server Version: 8.0.36-cll-lve (MySQL Community Server - GPL)

• phpMyAdmin Version: 5.2.1

---

🔧 Root Cause:

The original SQL query in login.inc.php was using:

• Improper syntax and unmatched parentheses

• A reserved MySQL keyword user without backticks

• Deprecated functions like PASSWORD() (which is removed in MySQL 8.0+)

---

✅ Fix:

I resolved the issue by replacing the broken query at line 263 in login.inc.php with the following:

$query = "SELECT * FROM `" . $GLOBALS['mysql_prefix'] . "user` WHERE `user` = " . quote_smart($_POST['frm_user']) . " AND ( `passwd` = MD5('" . strtolower($_POST['frm_passwd']) . "') OR `passwd` = MD5('" . $_POST['frm_passwd']) . "') ) LIMIT 1";

---

🟢 Result:

This corrected the SQL syntax and allowed the login process to work correctly with MySQL 8.0 and PHP 7.4 without needing to downgrade or change hosting.

Hope this helps others facing the same issue!

---

[Jeff Carrier]

Appreciate you sharing this!!!  Many times folks get help/support (on many various forums) and likely correct their issue but rarely reply that the issue was resolved and how it was resolved.

To view this discussion visit https://groups.google.com/d/msgid/open-source-cad/603531e9-5d4b-41b1-b3bf-2bbbd5772924n%40googlegroups.com.

[AA0PB]

Howdy,

I tried your fix and it site broke with a  Error code: 500 Internal Server Error.

Was there a different string that needed to be updated?  This is fresh install.

[AA0PB]

Forgot the screenshot

[Dominick Walenczak]

Just need to give an urgent warning about the critical vulnerability in the PHP below:

$query = "SELECT * FROM `" . $GLOBALS['mysql_prefix'] . "user` WHERE `user` = " . quote_smart($_POST['frm_user']) . " AND ( `passwd` = MD5('" . strtolower($_POST['frm_passwd']) . "') OR `passwd` = MD5('" . $_POST['frm_passwd']) . "') ) LIMIT 1";

This is subject to SQL injection. Simply by sending a password of " ') OR 1=1); --", you can log in as any user. And that is the least of the dangers of SQL injection. The reason is you are concatonating it into the query string without input sanitization or parameterization.  The above query becomes:

"SELECT * FROM `prefix_user` WHERE `user` = "user" AND ( `passwd` = MD5(' ') OR 1=1); --) . "') OR `passwd` = MD5('" . $_POST['frm_passwd']) . "') ) LIMIT 1";

The remainder after the double hyphen is treated as a comment and not processed (I haven't bothered to stringify the rest in the example because it really doesn't matter). The 1=1 is always true (in this universe, at least). The correct solution would be to use parameterization and prepared statements to prevent injection. Also, as noted, MD5 is insufficient for password hashes. It would be best to switch to BCRYPT. This would keep it functional with modern implementations of PHP and MySQL.

[Dominick Walenczak]

I'm not sure if I found the latest / correct TicketsCAD repo. I'm not one to point out a problem without a solution:

$stmt = mysqli_prepare($mysqli, "SELECT * FROM `" . $GLOBALS['mysql_prefix'] . "user` WHERE `user` = ? LIMIT 1"); 

mysqli_stmt_bind_param($stmt, "s", $_POST['frm_user']); 

mysqli_stmt_execute($stmt) or do_error("", 'mysql query failed', mysqli_error($mysqli), basename(__FILE__), __LINE__); 

$result = mysqli_stmt_get_result($stmt); 

$row = mysqli_fetch_assoc($result); 

$authenticated = $row && ( password_verify($_POST['frm_passwd'], $row['passwd']) 

                                                 || $row['passwd'] === md5(strtolower($_POST['frm_passwd'])) 

                                                 || $row['passwd'] === md5($_POST['frm_passwd']) );
if ( $authenticated ) {

This does a couple things. First of all, it uses a prepared statement, so the smartquotes is no longer required. It is no longer vulnerable to injection. MySQL doesn't have a native bcrypt function, to my knowledge, so it moves the validation to the PHP side. Because of this, it lets us use password_verify, which lets us test against bcrypt hashing while preserving the legacy MD5. Could probably even have another variable called $md5auth to detect if the password was hashed with the legacy md5. If so, prompt the password to update their password (and hash it via bcrypt, which requires a separate edit... and likely an update to expand the size of the passwd column to accomodate a bcrypt over just an md5 length).

Let me know if there's a good way to contribute this to the correct GitHub repo.

[Eric Osterberg]

Thank you for the report and recommendation... Yes I think you have the latest repo. This particular file was updated 5 years ago and the repository's last update was just days over 2 years ago.

I don't have an environment online at the moment, but I will try to produce a patch here soon.

This appears to be the file needing a pull-request/update: https://github.com/openises/tickets/blob/main/incs/login.inc.php

To view this discussion visit https://groups.google.com/d/msgid/open-source-cad/d9c50629-95eb-4d37-a475-510eadd3e790n%40googlegroups.com.

[Eric Osterberg]

I have hacked in a patch at line 259 of  https://github.com/openises/tickets/blob/main/incs/login.inc.php

If anyone is able, please run a git pull to fetch the latest code and let me know if anything is broken.

[Dominick Walenczak]

I tested it on Debian 13.3, PHP 8.4, MariaDB as a backend.

Unfortunately, the code did not work. Looks like I didn't quite realize the mysqli shim / wrapper that was in place and just assumed that there was a $mysqli variable (well, to be honesst, I assumed and didn't actually check). I tracked down the shim and how it worked to translate to the mysql_* calls for backwards compatability (as that had been depricated with... PHP 7, was it?). Anyways, my apologies for the half-assed code. Here's the new and improved fix:

// THIS CODE NEEDS TESTING
$stmt = mysqli_prepare(mysql2i::$currObj, "SELECT * FROM `" . $GLOBALS['mysql_prefix'] . "user` WHERE `user` = ? LIMIT 1");

mysqli_stmt_bind_param($stmt, "s", $_POST['frm_user']);

mysqli_stmt_execute($stmt) or do_error("", 'mysql query failed', mysqli_error(mysql2i::$currObj), basename(__FILE__), __LINE__);

$result = mysqli_stmt_get_result($stmt);
$row = mysqli_fetch_assoc($result);
            $authenticated = $row && ( password_verify($_POST['frm_passwd'], $row['passwd'])
                                                 || $row['passwd'] === md5(strtolower($_POST['frm_passwd']))
                                                 || $row['passwd'] === md5($_POST['frm_passwd']) );
            if ( $authenticated ) {

            // END UNTESTED CODE

//$row = stripslashes_deep(mysql_fetch_assoc($result));
$row = stripslashes_deep($row);

I changed it to mysql2i::$currObj instead of $mysqli. Also commented out the second mysql_fetch_assoc because the $result was already exhausted above. I'm assuming that's fine and not going to cause problems? I apologize if it does, as I am not fully versed in the overall codebase. I'm slowly working my way through and getting more of a grasp on it.

[Dominick Walenczak]

Eric,

If you're able to email me directly, there's a couple other things that I'd like to go over with you without generating excess noise/clutter in the group. A few points on:

• Providing the same protection to the mobile login function.
• Fixng injection vulnerability in profile password change (less urgent since these are, in theory, already trusted, authenticated users).
• Updating to bcrypt (which would require sending the raw password over to the server for server-side processing)
• Adding Flowroute / Twilio / Telnyx to the messaging stack for SMS.
• Considering major overhaul to deprecate shim/wrapper for mysqli and just write native.
• More prepared statements

I'm thinking that I could fork the project, make the edits and fixes, and then perhaps do a merge request. I'd just hate to have you worry about resolve merge conflicts with any conflicting changes, though the project hasn't had terribly many writes in a short period of time, so that's likely not an issue. Give me a shout (or, an email) and let me know your thoughts.

On Tuesday, February 24, 2026 at 10:53:13 AM UTC-5 Eric Osterberg wrote:

[Eric Osterberg]

That would all be very welcome! I appreciate the interest. These are all great ideas.

I'd also like to begin adding REST API support to make future integration more modular.

An uplift of the GUI interface, including updating the LeafletJS code would also be welcome. :)

You can email me directly at ejost...@gmail.com

To view this discussion visit https://groups.google.com/d/msgid/open-source-cad/c8490799-d768-4427-bbf1-72eb047e8607n%40googlegroups.com.