Text in the mysql statement

MySQL database
#1

I have a page that is basically created using a JSON file. This is processed to create a form that is then posted. The following code is designed to capture the input and write to the database.

<?php

//need to make the connection
include '../authenticate/config.php'; 

$pdo = new PDO($dsn, $DATABASE_USER,$DATABASE_PASS, $options);
$data=array();
foreach ($_POST as $key => $value) {
  
$data[':'.$key]=$value;
}

$sql="INSERT INTO ITcomp ( " . implode(', ',array_keys($_POST)) . ") VALUES (" . implode(', ',array_values($data)) . ")";

$stmt = $pdo->prepare($sql);
$res = $stmt->execute($data);

?>

The issue is that one or more of the 10 inputs in this case contains a text field, so when it tries to write it to the table I get an error. For instance if the input text is THIS then it will say that THIS is an unknown column. How can I solve this? TIA

#2

Have you echoed $sql so that you know what it actually contains? Hint: A prepared query has place-holders in it for the values, not the actual values.

Next, data submitted to your site can come from anywhere, can be anything, and cannot be trusted. Do NOT unconditionally loop over post data and don’t put the submitted field names into an sql query statement, as this will allow sql injection.

Instead, you should have a definition (array) in your code of the expected form fields. You would loop over this definition and access the corresponding submitted form data. You would get the column names and build the place-holders from this definition, not from the submitted form data.

Edit: I see this is continuing/repeating information from your last thread on the forum. No one needs your form in order to submit post data to your site. A hacker/bot can submit any form field names, including ones that contain injected sql, and any field values that they want to your site.

#3

THat’s why I’ve been using a JSON file to build (what will become) each form page. The $_POST field names are set up from the JSON file so I’m not sure how someone then changes the name
of one (I do understand the idea of entering an sql statement into the input of the field but I was under the impression that PDOs put a stop to that).

I did try echoing the statement which is why I think it is down to a string issue. It gave me this:

INSERT INTO ITcomp ( :a, :b, :c, :d, :e, :f, :g, :h, :i, :j) VALUES (2020, scot, 1, 2, 3, 4, 5, 4, 3, 2)
Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'scot' in 'field list' in

Although looking now I see the colons look in the wrong place.

SO the questions are how do I make it work and could you explain a little more to me how someone would inject it if I do it this way. Thanks.

#4

Scrap the first question as I’ve got it going now. Still interested in how injection would work here though - thanks.

#5

What you are doing, putting the post key values directly into the sql query statement, as the column names, ‘works’ for legitimate visitors who intend no harm, but your site must deal with those who intentionally submit requests to your site that contain nefarious content.

I’m not sure what part of this you don’t understand -
A. That a submission can be sent to your site, not even using your form page, that contains post keys and values that can be anything, or
B. How putting external, unknown, dynamic values directly into an sql query statement allows any sql special characters in that external value to break the sql query syntax, which is how sql injection is accomplished.

The main point of a prepared query is that it separates the sql query syntax from the sql data values. Column names are identifiers, not sql data values, and they are part of the sql query syntax. They cannot be supplied via prepared query place-holders, nor can you apply any escape string function to them, since they are not string data values and are not being used in the sql syntax in a string data context. If you did have a case where you need to dynamically supply a column name based on external values, you would either indirectly come up with the actual column name in the server-side code, i.e. map each external value to the corresponding column name, or you would need to validate that the external value is only and exactly a permitted column name before putting it into the sql query syntax.

#6

Slowly getting my head round this now. Bits keep falling into place. What I should be worried about is not someone trying to insert a column but using a key: value pair to add some sql syntax. Is that about right?

AS is stands the form is based on json data (gives it the name / id of the field, its location on a grid, the type of input, etc). Would a better approach be to write the ids into an array and loop that to pick up the field names and then pair them with the corresponding values?

#7

Maybe the following code will help?

/*
 * As long as you have the correct field names as the key and
 * the correct values in the corresponding keys the following
 * procedural function should work with no problem.
 *
 */
    try {
        /* Initialize an array */
        $attribute_pairs = [];

        /*
         * Set up the query using prepared states with the values of the array matching
         * the corresponding keys in the array
         * and the array keys being the prepared named placeholders.
         */
        $sql = 'INSERT INTO ' . $table . ' (' . implode(", ", array_keys($data)) . ')';
        $sql .= ' VALUES ( :' . implode(', :', array_keys($data)) . ')';

        /*
         * Prepare the Database Table:
         */
        $stmt = $pdo->prepare($sql);

        /*
         * Grab the corresponding values in order to
         * insert them into the table when the script
         * is executed.
         */
        foreach ($data as $key => $value)
        {
            if($key === 'id') { continue; } // Don't include the id:
            $attribute_pairs[] = $value; // Assign it to an array:
        }

        return $stmt->execute($attribute_pairs); // Execute and send boolean true:

    } catch (PDOException $e) {

        /*
         * echo "unique index" . $e->errorInfo[1] . "<br>";
         *
         * An error has occurred if the error number is for something that
         * this code is designed to handle, i.e. a duplicate index, handle it
         * by telling the user what was wrong with the data they submitted
         * failure due to a specific error number that can be recovered
         * from by the visitor submitting a different value
         *
         * return false;
         *
         * else the error is for something else, either due to a
         * programming mistake or not validating input data properly,
         * that the visitor cannot do anything about or needs to know about
         *
         * throw $e;
         *
         * re-throw the exception and let the next higher exception
         * handler, php in this case, catch and handle it
         */

        if ($e->errorInfo[1] === 1062) {
            return false;
        }

        throw $e;
    } catch (Exception $e) {
        echo 'Caught exception: ', $e->getMessage(), "\n"; // Not for a production server:
    }
return true;

I put comments in the script and it even helps me when I haven’t worked on it for a long time.

More can be found here: https://github.com/Strider64/phototechguru/tree/master/assets/functions

#8

Thanks for that. Does that deal with the issue raised above re possible injection?

Sponsor our Newsletter | Privacy Policy | Terms of Service