Jump to content

Error with PDO Prepare


Recommended Posts

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

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.