Error with PDO Prepare

[mongoose00318]

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

[gw1500se]

Yep, the error is quite explicit. If you are having trouble seeing it echo $sql.

[mac_gyver]

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. 

[mongoose00318]

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;
	}

 

[mac_gyver]

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.