mongoose00318 Posted May 4, 2020 Share Posted May 4, 2020 I'm trying to clean up all my functions with any queries which take dynamic parameters using PDO prepared statements. I originally thought I was using prepared statements and was told later I wasn't so it's been on my to-do list to go and clean them up. I have cleaned up a lot of them and tested them and they are working fine. This one is giving me a problem though.. /*fetch production data*/ if( isset( $field ) && isset( $type ) && isset( $value ) ) { $sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date FROM production_data WHERE :field :type :value ORDER BY enterprise, job_number, line_item LIMIT :offset, :records_per_page'; $stmt = $pdo->prepare($sql); $stmt->execute( [ 'field' => $field, 'type' => $type, 'value' => $value, 'offset' => $offset, 'records_per_page' => $records_per_page ] ); } else { $sql = 'SELECT id, job_number, enterprise, description, line_item, as400_ship_date FROM production_data ORDER BY enterprise, job_number, line_item LIMIT '. $offset . ', '. $records_per_page; } It takes values from some drop downs (I'm using tabulator to generate a table and this part is related to it's pagination functions)...here is the HTML for those elements. <div class="table-controls"> <div class="form-row"> <div class="col"> <label for="filter-field" class="col-form-label-sm">Field: </label> <select id="filter-field" class="form-control form-control-sm"> <option></option> <option value="Job Number">Job Number</option> <option value="Enterprise">Enterprise</option> </select> </div> <div class="col"> <label for="filter-type" class="col-form-label-sm">Type: </label> <select id="filter-type" class="form-control form-control-sm"> <option value="like" selected="selected">Like</option> <option value="=">Equal to</option> </select> </div> <div class="col"> <label for="filter-value" class="col-form-label-sm">Value: </label> <input id="filter-value" class="form-control form-control-sm" style="float: left;" type="text" placeholder="Value to filter..."> </div> <div class="col d-flex align-content-end flex-wrap"> <button id="filter-clear" class="btn btn-primary btn-sm rounded-0">Clear Filters & Sorting</button> </div> </div> </div> After attempting to put adjust the query to a prepared statement, I get the following error: Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '? ? ORDER BY enterprise, job_number, line_item LIMIT ?, ?' at line 3 in C:\wamp64\www\test\scripts\order_status.php on line 125 Quote Link to comment Share on other sites More sharing options...
gw1500se Posted May 4, 2020 Share Posted May 4, 2020 Yep, the error is quite explicit. If you are having trouble seeing it echo $sql. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 4, 2020 Share Posted May 4, 2020 you can only supply data values via prepared query place-holders. you cannot supply identifiers (column, table, database names) or sql syntax/keywords (comparison operators.) to safely do what you are trying, you must validate that the submitted column name and comparison operator are exactly and only permitted values, before building the sql query statement with them in it. Quote Link to comment Share on other sites More sharing options...
mongoose00318 Posted May 4, 2020 Author Share Posted May 4, 2020 17 minutes ago, mac_gyver said: you can only supply data values via prepared query place-holders. you cannot supply identifiers (column, table, database names) or sql syntax/keywords (comparison operators.) to safely do what you are trying, you must validate that the submitted column name and comparison operator are exactly and only permitted values, before building the sql query statement with them in it. I am checking the data coming in; here is my code for doing that and then setting the value's of the variables: //filters are set if(isset($_GET['filters'])){ //check field for manipulation $field = $_GET['filters'][0]['field']; if ( $field == 'Job Number' ) $field = 'job_number'; else if ( $field == 'Enterprise' ) $field = 'enterprise'; else exit(); //check type for manipulation $type = $_GET['filters'][0]['type']; if ( $type == '=' ) $type = '='; else if ( $type == 'like' ) $type = 'LIKE'; else exit(); //check value for job_number as numeric $value = $_GET['filters'][0]['value']; if ( $field == 'job_number' && trim( !is_numeric ( $value ) ) ) exit(); else if ( $type == 'LIKE' ) $value = "'%" . $value . "%'"; $filter_data = TRUE; } Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted May 4, 2020 Share Posted May 4, 2020 16 minutes ago, mongoose00318 said: if ( $field == 'Job Number' ) $field = 'job_number'; else if ( $field == 'Enterprise' ) $field = 'enterprise'; i would create arrays with the choices in them, then just use in_array() to validate the submitted values. once you have an array of the choices, you can dynamically build the <option>...</option> markup, so that just by altering the defining arrays, you can change what the code does without writing out conditional logic or html markup for every possible choice. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.