Error while submitting form data with spaces to sqlsrv from php

Question

I'm very new to programming php and html. I've got a form I'm using to update info to a SQL DB.

I can get the form to send information into the SQL, but once I add a space to my inputs or they get too long it stops working.

I've gotten to the point where all I'm trying to do is send the $test variable in. I've also tried sending spaces into the SQL using the SP directly itself and it works just fine. Any advice is appreciated.

<div class="form-popup"  id="myForm">
    <form action="../processors/unload.php"  method = "POST" class="form-container" >
        <h1>EDIT BOX</h1>

        <label for="part-id"><B>PART ID</B></label>
        <input type="text" placeholder="enter part ID" name="partID" required>

        <label for="part-id"><B>BOW</B></label>
        <input type="text" placeholder="BOW" name="bow" >

        <label for="partid"><B>TUBE HEIGHT</B></label>
        <input type="text" placeholder="TUBE HEIGHT" name="tube">

        <label for="notes"><B>NOTES</B></label>
        <input type="text" placeholder="notes" name="notes">

        <br>
        <label for="quality"><B>QUALITY</B></label>
        <select name='quality' class='defectDrop'>
            <option value='ok'>OK</option>
            <option value='NOK'>NOK</option>
            <option value='online'>ONLINE</option>
        </select>

        <label for="defect"><b>DEFECT</b></label>                          

        <button type="submit" class="submit-button" id ="boo" >SUBMIT</button>
        <button type="button" class="cancel-button" onclick=hideIt();>CLOSE</button>

    </form>
</div>



<?php
    include('../connect.php');
    $partID = $_POST['partID'];
    $quality = $_POST['quality'];
    $notes = $_POST['notes'];
    $bow = $_POST['bow'];
    $tube = $_POST['tube'];

    $test = "what up";

    $sql = "EXEC dbo.php_test  
                    @part_ID = " .$partID. "
                    , @notes = " .$test. " ";
    echo $sql;
    sqlsrv_query($conn, $sql);
    sqlsrv_close($conn);

    header("location: ../html/test.html");

If you enable errors (https://stackoverflow.com/a/21429652/6362129), do you see any? And I think you are currently at risk of being attacked with SQL injection, which is a HUGE security risk that can expose your whole server (https://blogs.msdn.microsoft.com/brian_swan/2010/03/04/whats-the-right-way-to-prevent-sql-injection-in-php-scripts/). — Tim, Sep 16 '19 at 08:44

Zhorov · Answer 1 · 2019-09-16T11:18:10.937

Explanations:

Don't concatenate strings to generate a statement. Always use prepared statements and parameterized queries to prevent SQL injection. With PHP Driver for SQL Server, function sqlsrv_query() does both statement preparation and statement execution and can be used to execute parameterized queries.

What is probably a reason for your error is wrong T-SQL statement. The second parameter is probably a varchar/nvarchar data type, so you need to enclosed it in single quotation marks - '' for varchar data type and N'' for nvarchar data type. Use next statement only for test.

$sql = "EXEC dbo.php_test @part_ID = ".$partID.", @notes = '" .$test. "' ";

Solution:

As a solution, you need to rewrite your code using parameterized statements and error checking.

<?php
    include('../connect.php');

    $partID  = $_POST['partID'];
    $quality = $_POST['quality'];
    $notes   = $_POST['notes'];
    $bow     = $_POST['bow'];
    $tube    = $_POST['tube'];

    // Parameterized query   
    $sql = "EXEC dbo.php_test @part_ID = ?, @notes = ?";
    $params = array($partID, $notes);

    // Execution and error checking
    echo $sql;
    $stmt = sqlsrv_query($conn, $sql, $params);
    if ($stmt === false ) {
        echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
        exit;
    }

    sqlsrv_free_stmt($stmt);
    sqlsrv_close($conn);

    header("location: ../html/test.html")
?>

That all makes sense and this worked perfectly. Thank you for the help! — monty, Sep 18 '19 at 01:58
@monty Glad to help. If you think that this or any other answer is the best solution to your problem, you may accept it (just click on the check mark beside the answer to toggle it from greyed out to filled in). — Zhorov, Sep 18 '19 at 05:35

Error while submitting form data with spaces to sqlsrv from php

1 Answers1