Insert Data into MSSQL DB using PHP

Insert Data into MSSQL DB using PHP

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Diagnosing and Fixing MSSQL Insert Errors in PHP

The error message Invalid object name 'dbo.CAGD$Leave Plan' (note the missing dollar sign in the error log versus the code) reveals a classic mix of syntax errors and deprecated API usage. While your goal is simply to insert form data into a SQL Server database, the provided code snippet contains several critical flaws that prevent execution: incorrect variable interpolation, mismatched column names, and reliance on an obsolete extension.

The Variable Interpolation Trap

The most immediate barrier preventing your query from running is how you are constructing the SQL string. You are using single quotes around variable placeholders like ('$no') and then concatenating raw values directly into the statement without escaping.

In PHP, when you write $query = "VALUES ('$no', ...)";, the database receives the literal characters $, n, o. Unless you are running inside a SQL Server stored procedure where variables are declared first, this will fail or inject unintended data. Furthermore, passing raw $_POST values directly into an SQL string is a massive security vulnerability known as SQL Injection. An attacker could manipulate the form input to alter your database schema or exfiltrate sensitive data.

You must use prepared statements. The legacy mssql_* functions do not support prepared statements in the same way PDO does, and relying on them for anything other than trivial scripts is generally discouraged by Microsoft. The modern standard is to use PDO with SQL Server driver or the newer sqlsrv extension. Below is an example using PDO, which handles escaping and parameter binding securely:

$dbc = new PDO("sqlsrv:Server=Server-PC;Database=database", "user", "password");
// Disable emulated prepared statements for production safety
$dbc->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 

$stmt = $dbc->prepare("INSERT INTO dbo.[CAGD\$Leave Plan] 
                       ([No_], [Employee No_], [Employee Name], 
                        [Leave Name], [Start Date], [Leave Days], Satus) 
                       VALUES (?, ?, ?, ?, ?, ?, ?)");

// Note: The dollar sign in the table name must be escaped for PDO
$stmt->execute([
    $_POST['no'],
    $_POST['empno'],
    $_POST['name'],
    $_POST['leave'],
    $_POST['date'],
    $_POST['days'],
    // Handle Satus appropriately based on your logic
    null 
]);

Syntax and Column Mismatches

Even if you fix the variable binding, your specific error message indicates a deeper issue: Invalid object name 'dbo.CAGD Plan'. Notice that the dollar sign $ is missing in the error log. In SQL Server, dollar signs are often used to denote special characters in identifiers (like temporary tables or external references), but they are rarely used inside standard table names unless explicitly required by specific naming conventions or system objects.

Your INSERT INTO statement attempts to reference [CAGD$Leave Plan], but your error log suggests the database is looking for dbo.CAGD Plan. This discrepancy usually stems from one of two reasons:

  1. Table Name Typo: The actual table name might not include the dollar sign, or it includes a space that isn't being handled correctly by the brackets.
  2. Case Sensitivity: If your database is configured with case-insensitive collation, CAGD$Leave Plan and CAGD$leave plan are treated as the same object. However, if the table was created as CAGD$Leave_Plan (with an underscore) but you are querying CAGD$Leave Plan, SQL Server will throw an "Invalid object name" error because it cannot find a match for the space or the specific casing.

Always verify your schema using the INFORMATION_SCHEMA.TABLES view in SQL Management Studio:

SELECT TABLE_NAME, TABLE_SCHEMA 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%Leave%';

Modernizing the Stack

The code snippet uses the mssql_* functions (mssql_connect, mssql_query). These were deprecated in PHP 7.0 and removed entirely in PHP 7.1. If you are running on a modern server, these functions simply will not exist. Even if your environment still loads them (via legacy compilation), they lack the robustness of PDO.

Switching to PDO provides several benefits:

  • Security: It enforces prepared statements, eliminating SQL injection risks by default.
  • Portability: The same code logic can often be adapted for MySQL or PostgreSQL with minimal changes.
  • Error Handling: PDO offers better exception handling capabilities compared to the die-on-error approach of mssql_query.

If you are building a larger application, consider using a framework like Laravel. It abstracts database connections and provides an elegant query builder that handles these identifier escaping issues automatically. For more on how to manage database interactions safely in modern PHP architectures, resources like LaravelCompany offer guidance on best practices for enterprise-grade development.

Conclusion on Implementation

To resolve the error you are facing, stop using mssql_query and migrate to PDO. Ensure your table name matches exactly what exists in the database schema, paying close attention to special characters like $, spaces, and underscores. By adopting prepared statements, you secure your application against injection attacks while ensuring the data types and column names align perfectly with your database definition.

Our network