Tickets Plans for this weekend

[Eric Osterberg]

Well, I'm getting around to some testing and coding this weekend.

I have the following plans for the next two weeks.

---

Context

Tickets CAD is a 30-year-old PHP Computer Aided Dispatch system with 808 PHP files and ~250K lines of code. It has critical security vulnerabilities (SQL injection in 353+ files, XSS, MD5 passwords, no CSRF), uses deprecated mysql_* functions via a compatibility shim, and runs on PHP 7.4+. The goal is to modernize the PHP codebase while maintaining backward compatibility for existing installations on diverse platforms (Raspberry Pi, XAMPP/Windows, Docker, Linux LAMP).

The existing installer (install.php) already supports clean install and non-destructive upgrade modes with version tracking in the settings table. We'll build on this.

---

Prerequisites & Testing Setup What We Need

1. XAMPP on this Windows workstation - for local testing (PHP 8.x + MySQL/MariaDB)
2. WSL - already available, for Linux testing
3. The cloned repository - already at tickets/

Testing Strategy

• Install XAMPP and configure it to serve the tickets/ directory
• Import DB_FULL.sql to create a test database
• Run the installer to verify baseline functionality works
• After each batch of changes, verify the app loads, login works, and core dispatch functions operate
• We cannot run automated tests (no test suite exists) — we'll test manually via the browser and verify SQL syntax with PHP lint
• We'll add a php -l lint check on all modified files before each commit

Upgrade Path Design

• The existing install.php already handles upgrades non-destructively (adds missing tables/columns, never drops data)
• We'll increment the version in incs/versions.inc.php for each release
• All our PHP changes are backward-compatible with the same database schema — we're changing HOW queries are built, not WHAT data they access
• No database schema changes needed for security fixes (Phase 1)
• For inexperienced admins: upgrade = download new files, visit /install.php, select "Upgrade", done
• Docker users: pull new image, restart container
• XAMPP users: extract new files over old ones, visit /install.php

---

Branch Strategy

Create branch security-modernization from main in the tickets repo.

---

Implementation Order Step 1: Create Database Abstraction Layer

Files to create: incs/db.inc.php

Create a thin wrapper around mysqli that provides:

• A db() function returning a singleton mysqli connection
• A db_query($sql, $params, $types) function for prepared statements
• A db_fetch_all($sql, $params, $types) convenience function
• A db_fetch_one($sql, $params, $types) convenience function
• A db_escape($string) function for cases where prepared statements can't be used (dynamic table names)
• A db_insert_id() function
• A db_affected_rows() function

This layer will coexist with the mysql2i shim. Files migrate incrementally — old code keeps working, new code uses the new layer. The shim stays until all files are migrated.

Why first: Every subsequent security fix depends on this layer.

Step 2: Add Security Helper Functions

Files to create/modify: incs/security.inc.php, incs/functions.inc.php

Create helper functions:

• e($string) → htmlspecialchars($string, ENT_QUOTES, 'UTF-8') for XSS prevention (already exists in install.php as h(), standardize it)
• csrf_token() → generate and store CSRF token in session
• csrf_verify() → validate CSRF token from form submission
• sanitize_int($val) → (int)$val with null handling
• sanitize_string($val) → trim + basic cleanup

Add require_once 'security.inc.php' to functions.inc.php.

Step 3: Fix Critical SQL Injection — AJAX Endpoints (155 files)

Directory: ajax/

These are the highest-risk files since they accept user input directly. Work through them alphabetically in batches of ~15-20 files per commit:

• Replace $_GET/$_POST variables directly in SQL strings with prepared statements via db_query()
• Replace addslashes() with prepared statement parameters
• Replace quote_smart() with prepared statement parameters
• Replace mysql_real_escape_string() with prepared statement parameters
• Keep $GLOBALS['mysql_prefix'] for table names (these are from config, not user input)

Commit pattern: "Fix SQL injection in ajax/ batch N (files X-Y)"

Step 4: Fix SQL Injection — Core Include Files

Files: incs/functions.inc.php (5281 lines), incs/functions_major.inc.php (1745 lines), incs/functions_nm.inc.php, incs/functions_major_nm.inc.php, incs/config.inc.php, incs/messaging.inc.php, incs/member.inc.php, incs/login.inc.php

These are the shared libraries called by everything else. Migrate all query functions to use prepared statements.

Step 5: Fix SQL Injection — Root PHP Files (~200 files)

Directory: Root *.php files

Work through alphabetically in batches. Same pattern as Step 3.

Step 6: Fix SQL Injection — Mobile & Portal

Directories: rm/, portal/

Same pattern. ~63 files total.

Step 7: XSS Prevention

All files that output user data to HTML:

• Add e() calls around all $_GET, $_POST, $_SESSION, and database-sourced values in HTML output
• Focus on form values, error messages, and displayed user content
• Add Content-Type: application/json headers to all AJAX responses returning JSON

Step 8: Password Hashing Migration

Files: incs/login.inc.php, incs/functions.inc.php, any file that sets passwords

• Login already supports password_verify() with MD5 fallback (done in recent hardening)
• Add automatic rehashing: when user logs in with MD5 password, rehash with password_hash() and update DB
• Update all password-setting code (user creation, password change) to use password_hash()
• Remove MD5 password hashing from all new user creation paths

Step 9: CSRF Protection

All state-changing forms:

• Add csrf_token() to all forms as a hidden field
• Add csrf_verify() check at the top of all form processing scripts
• AJAX endpoints: add CSRF token to AJAX request headers

Step 10: Security Headers

File: incs/functions.inc.php (in the initialization section)

Add headers to the page initialization:

php

header('X-Frame-Options: SAMEORIGIN'); header('X-Content-Type-Options: nosniff'); header('X-XSS-Protection: 1; mode=block');

Step 11: Update Version & Upgrade Documentation

• Bump version in incs/versions.inc.php
• Update INSTALL.txt and get_started.txt with upgrade instructions
• Add UPGRADING.md with clear step-by-step instructions for:
  • XAMPP/Windows users
  • Linux LAMP users
  • Raspberry Pi users
  • Docker users
• Add CHANGELOG.md documenting all security fixes

---

Commit Strategy

• Each "step" above will have multiple commits (one per batch of files)
• Every commit message describes WHAT was changed and WHY
• Format: [security] Fix SQL injection in ajax/form_post.php, ajax/get_assigns.php, ...
• PHP lint check (php -l) on all modified files before committing
• Test core functionality (login, create ticket, dispatch) after each step

---

Upgrade Path for End Users For All Users (XAMPP, Linux, Raspberry Pi)

1. Back up your database (mysqldump)
2. Back up your incs/mysql.inc.php (contains your DB credentials)
3. Download/extract new files over old ones
4. Visit http://yourserver/tickets/install.php
5. Select "Upgrade" mode
6. Your data, settings, and configuration are preserved
7. Login and verify everything works

For Docker Users

1. Pull new image
2. Restart container
3. Visit install.php and run upgrade
4. Database data persists in volume

What Makes This Safe

• No database schema changes in Phase 1 (security fixes only change PHP code, not DB structure)
• The mysql2i shim remains in place — if any file was missed, it still works
• install.php upgrade mode is non-destructive (never drops tables or data)
• Version tracking in settings._version ensures upgrades are idempotent

---

Files Modified (Summary)

Category
File Count
Description
New: incs/db.inc.php
1
Database abstraction layer
New: incs/security.inc.php
1
Security helper functions
Modified: ajax/*.php
~155
SQL injection fixes
Modified: incs/*.php
~15
Core library modernization
Modified: Root *.php
~200
SQL injection + XSS fixes
Modified: rm/*.php
~40
Mobile module fixes
Modified: portal/*.php
~23
Portal module fixes
New: UPGRADING.md
1
Upgrade instructions
New: CHANGELOG.md
1
Change log

---

What We Are NOT Doing (Yet)

• No framework migration (no Composer, no Laravel — that's Phase 2)
• No database schema changes (that's Phase 3)
• No UI changes (that's Phase 4)
• No Leaflet/jQuery updates (that's Phase 4)
• No REST API (that's Phase 5)
• We keep the mysql2i shim for now — it's removed when ALL files are migrated

---

Risk Mitigation

• If a file breaks: The mysql2i shim catches old-style calls, so partially-migrated state still works
• If prepared statements fail: The db_query() wrapper will log errors with file/line info
• Rollback: Users can restore from their backup + old files
• Testing: Manual testing after each step; php -l syntax checking on every commit

[d.walenczak]

Hey Eric,

I agree with the overall phasing and priority order. One question on Step 1: the mysql2i shim commit I just submitted has a new mysql_prepare_query() which does prepare+bind+execute in one call. Can we skip creating db.inc.php and just migrate mysql_query() calls directly to mysql_prepared_query()? That avoids maintaining two parallel abstractions and cuts the migration to a single pass per file instead of two. I put up a PR with the shim enhancement and initial fixes for the highest-risk files. Can we coordinate so we're not duplicating work on the same files?

What do you think about a regularly scheduled code freeze for testing? Maybe first week / first weekend of each month? Or bimonthly/semi-monthly (I always get confused between every other vs. twice per terminology)? That way I know not to work on things that you're finalizing for release.

Install.php:

I should also give you a heads up about some changes made to the install.php file... It no longer installs TicketsCAD 2.2 and upgrades to 3 on loading the index.php. It's an all-in-one process. It also doesn't use hardcoded default credentials for the superuser account. It prompts you to create one on the install page. It uses Ajax to echo each table created/updated in the install process to the user so there's less chance of a timeout from long execution and also keeps the user informed that is is, in fact, still working. Once the install is complete, the install.php page is locked behind admin+ user authentication. You can do a clean reinstall from there, if needed. If it detects that the version of the install.php differs from the database stored version of TicketCAD, it will redirect user traffic to the install.php (with admin user gating) because the interpretation is that you downloaded new PHP files with the intent of installing an upgrade. And it should be able to do an upgrade-in-place -- I hope/think.

My thought about future iterations of the install.php file would be for it to reach out to the GitHub repo, identify what the current release version is (we would have to include release tags), and include the option to download and update right from the install.php file direct from the repo. I'm not 100% how doable it is with sock PHP... it should be, in theory. I just want to make sure it doesn't require any unusual or non-stock extensions to function. At the very least, I know we could probably use php-curl to pull the current version number in the repo and present a notification that there's a new version available.

Versioning:

I did bump the TicketsCAD version in one of my PRs in the past from... 3.43 to 3.44, I believe? I apologize if raising the version number was a bit presumptive of me. I just assumed that the changes were significant enough to make a note. I probably should have discussed or mentioned it properly.

Ajax files:
Yeah, that does seem to be the largest threat vector, by directory and by function. Probably should prioritize by which ones are externally accessible to unverified users. What do you think about dividing up the work? Like... I start from the top down and you bottom up (or vice versa)... or I take the file sizes that end in odd bytes and you take even? Divide up the work to avoid duplication of efforts? Or we can ask an AI to assign the specific file responsibilities in a manner designed to balance line count (it would suck for one of us to get stuck with 10x the work because a random file had 10x the line count or something)?

Version 4:

I was wondering if you could create a TicketsCAD v4 dev branch. That way, we could do parallel efforts of shoring up the current v3 while also slowly chipping away at the dev process of the 4th version. It would also help if you could give me commit/contributor permissions on that branch directly so that I don't have to do it solely through pull requests. I'd probably start off with the basic authentication system as the start of the development. Here are my thoughts -- please let me know which ideas you'd like me to implement, postpone, or not implement:

• User Login using OAuth or outside authentication providers: Allow admins to, if they wish to enable it and set it up with their identity providers, utilize Google, Microsoft, GitHub, OpenID, etc. services to be used for user authentication. That way, if an organization has, say, Google... they can log in with Google without having to use local accounts.
• User Login CAPTCHA: Allow to admins to enable CAPTCHA on login from either reCAPTCHA, hCAPTCHA, or a local-only CAPTCHA. Probably useful for authentication verification if exposed online, though less useful if they're running it local only.
• Modular Design: No page should ever directly output the data to the user but instead run through user language and style templates. What I mean is, from the phpBB 2.x days, they made use of style and language templates. It would load the display template of either the sitewide template or per-user template (if allowed). This would allow for different designs and customizations. It would then use the user/site's overall language to display that text. I know we have a page where you can change the language/labels. I would like to give the options to use multiple languages simultaneously, per use, as a default. And now, with AI, we could pre-include those language options with every install by default. The navbar can be customized and re-arranged by the site administrator for their workflow. We could also foster a modding/plugin community within TicketsCAD by allowing people to create add-ons without having to know much about TicketsCAD or editing core TicketsCAD files to make them show up. You put them in a modules/plugins folder, go to the admin page to enable it, and it shows up in the navbar. This should survive upgrades to TicketsCAD core files unless the upgrade is a breaking change.
• Expanded Focus: I think we could look at expanded focus as well. It appears that we're focused on emergency / event dispatch. But what if we included a configuration that would work for all other implementations... like lets say a plumbing company wants to run with this as their plumber dispatch platform... or an HVAC company... or, really, anyone having a need for a dispatch platform. We could make sure our main focus is the same emergency services based one... but making it more useful to other fields would help them while also help TicketsCAD grow.
• SIP/VOIP Integration: We might need another repo, for the non-web files, but I would love to see this also include the control of phones for dispatching right through the webpage. We'd probably redistribute (probably just have to verify compatible licensing) a customized version of Asterisk that we could have it connect to someone's phone system/provider or act as the phone server itself. The frontend would probably make use of WebRTC or something similar to handle the real-time audio traffic to the front end from the Asterisk. Not sure if we would rather make this a first "official module/plugin" to not get distracted from the core function of TicketsCAD.
• Radio / Audio Recording: I thought about adding radio recording capability to version 3... whether through an audio input jack or from the audio stream from an SDR, or even Broadcastify / IceCast. The audio recordings would be files, with metadata being pumped in to the database for indexing. We could even make it so we can flag the audio as being associated with a certain ticket (user flagging).
• CAD-to-CAD data exchange: I thought about including the option for the CAD to be able to exchange tickets with other TicketsCAD implementations. Like if South Podunk VFD has TicketsCAD and Podunksville Volunteer Ambulance and Daycare Center has one as well (I'm in the field, so I'm allowed to poke fun! 😅), they could send a call they have to the other CAD. We could either do it at the local level (one knows the URL for the other, and both site admins accept the data exchange request), or we could host a centralized list of all TicketsCAD operators who are A) publicly facing, and B) willing to be publicly listed. We could also use this to keep track of TicketsCAD installs with the intention that if there's a critical vulnerability -- as we have now -- we can reach out to the site admins and let them know they urgently need to upgrade. The tickets exchange would probably use either NEIM, EDD, or EIDO communication standards, which should also allow us to have interconnects with other major CAD vendors -- though I am sure that they would charge their customers an arm and a leg to do so. For instance, Tyler Technologies (which owns New World CAD), wanted $25,000 just to send call data to an EMS ePCR vendor. But, at least we could support it using a known standard and it being free on our end (and that of our users/implementers).
• Radio Integration: I've seen some neat radio integrations using SDRs, Pi-Star, and other kinds of systems. I think it would be neat if we could also include that capability within TicketsCAD (also probably as part of an add on module as well). This way, CAD, Phones, and Radios can be integrated right into one single pane of glass interface and covers everyones use cases and needs.
• Position reporting: I'd like to see if we could include realtime tracking of unit positions. I think that was part of TicketsCAD functionality, but I'm not 100% sure. I think TicketsCAD did it through Radio GPS data, which I have heard of and own a compatible radio. I just never toyed with it that much. Alternatively, they could be logged into TicketsCAD as a unit leader, which would send back user location. My problem is that programs I have written tend to go to sleep when not in the browser foreground. Or, maybe an Android/Windows app, but that is 100% out of my wheelhouse and knowledge base. Lastly, many emergency services use standardize TAIP or NEMA TCP/UDP gps position transmissions, which we could include a method for parsing from the data. I've dealt with both of those before.

Automated Testing

Maybe there is something we can do. I imagine we could use Python and Selenium to automate our testing. It's a library that automatically controls your browser. I've used it to control freestanding kiosks that display webpages. Some of them needed a method to automatically log in and navigate to a certain page. Perhaps we could use the same thing? We could have it put in 100 tickets, geocode some. Maybe put in apostrophes, HTML characters, or attempt SQL Injections on every field to "jiggle the handle" to make sure things are locked down and we have proper error handling. Maybe load every page at least once. Could have to listen to the webserver and PHP error loads and display any errors after the testing? Or if an input isn't handled properly or generates an error page, we could report that in the output as well. Maybe I should make that my top priority as it would streamline all future testing.

Anyways, sorry for the long-winded post. I'll try and keep it shorter in the future!

[Eric Osterberg]

Last night at 11 PM, there were no pending pull requests and I checked out the latest version from GitHub. I've been feverishly coding away making security updates to hundreds and hundreds of files.

I see your pull request this morning that affects a small handful of files. I am afraid they have little chance of merging smoothly and I have likely targeted fixing most of the issues you listed.

I'll wrap up this current session before the end of the day tomorrow. We should sync up then and see what you think.  Maybe we should plan for a phone call for Monday or Tuesday if you have availability. I am in the central time zone.

 -Eric

--
You received this message because you are subscribed to the Google Groups "Open Source CAD - Dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to open-source-cad-...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/open-source-cad---dev/a4086ae0-cef6-4dda-ab6c-8fc800a8063an%40googlegroups.com.

[Eric Osterberg]

BTW, I would love automated testing. I'm currently pouring through the UI and finding lots of bugs. Some bugs existed prior to my changes and many others yet related to DB changes and function calling changes for compatibility with newer PHP versions.

If you wanted to put any time to that this weekend, we will see an immediate benefit to the effort.

[d.walenczak]

  No worries, I didn't submit the PR until I saw this. Hoped I could sneak one in last minute. I had it sitting in a branch of my own fork for a while. So, that's on me.

Would love to do a phone call and touch base. I have a firm time commit Tuesday 10 - 11 AM EDT, but outside of that I'm free anytime. Feel free to call - (716) 908 - 2556. Since you're not in my phonebook, you'll most likely get my Google automated call screening. Just so you're not surprised. Tell it who you are and that it's about TicketsCAD. If I see it pop up, I'll definitely answer. Or, if you want to share your phone number here, I'll pre-emptively add you to my contacts so you bypass that.

I did make an automated test program. I'm afraid I haven't gotten very far. It's a little embarrassing, actually. Here you are, fixing hundred of bugs and vulnerabilities and the best I can contribute is a poorly written 700 line python file. It requires Selenium. I had AI write the Readme Instructions, because I'm also too lazy to do that. It should be relatively easy to install and set up. Right now, it only checks the login (bad login first, then the correct one), then just makes sure that each tab loads. 

dwalenczak/ticketscad-smoketest: Automated test suite for TicketsCAD development to test logins, page navigation, and other functionality.

I also invited you as a contributor, so you can add some unit tests as you find a need for them. I was going to give it SSH access (or local file path) to the test server and have it watch the error log for anything that crops up on loading. But, also planning on having it enter a bunch of units, personnel, natures, assignments, and tickets, change passwords, etc. Currently, it's very limited in function, but I'd consider it a pre-alpha version.

Let me know if you have any questions! I'll try not to bother you while you're busy.

[Eric Osterberg]

Sounds great. I too am letting AI do the heavy and light lifting here.

Eric Osterberg 612-501-7803 (cell) text is the best way to reach me for an urgent response. Email is fine too for anything not urgent.

To view this discussion visit https://groups.google.com/d/msgid/open-source-cad---dev/704f58bc-1d29-47f8-abb1-691d1d4b750dn%40googlegroups.com.