|
|
|
|
Bookmark this on Delicious
Share on Facebook
 Slashdot It!
Digg  pg_query_params
Submits a command to the server and waits for the result, with the ability to pass parameters separately from the SQL command text.
(PHP 5 >= 5.1.0)
Example 1964. Using pg_query_params()<?phpCode Examples / Notes » pg_query_paramsjsnell
When inserting into a pg column of type bool, you cannot supply a PHP type of bool. You must instead use a string "t" or "f". PHP attempts to change boolean values supplied as parameters to strings, and then attempts to use a blank string for false. Example of Failure: pg_query_params('insert into table1 (bool_column) values ($1)', array(false)); Works: pg_query_params('insert into lookup_permissions (system) values ($1)', array(false ? 't' : 'f')); cc+php
This is a useful function for preventing SQL injection attacks, so, for those of us who are not yet able to upgrade to PHP5.1, here is a replacement function which works similarly on older versions of PHP... <?php # Parameterised query implementation for Postgresql and older versions of PHP if( !function_exists( 'pg_query_params' ) ) { function pg_query_params__callback( $at ) { global $pg_query_params__parameters; return $pg_query_params__parameters[ $at[1]-1 ]; } function pg_query_params( $db, $query, $parameters ) { // Escape parameters as required & build parameters for callback function global $pg_query_params__parameters; foreach( $parameters as $k=>$v ) $parameters[$k] = ( is_int( $v ) ? $v : "'".pg_escape_string( $v )."'" ); $pg_query_params__parameters = $parameters; // Call using pg_query return pg_query( $db, preg_replace_callback( '/\$([0-9]+)/', 'pg_query_params__callback', $query ) ); } } // Example: pg_query_params( $db_resource, "SELECT * FROM table WHERE col1=$1 AND col2=$2", array( 42, "It's ok" ) ); ?> dt309
If you need to provide multiple possible values for a field in a select query, then the following will help. <?php // Assume that $values[] is an array containing the values you are interested in. $values = array(1, 4, 5, 8); // To select a variable number of arguments using pg_query() you can use: $valuelist = implode(', ', $values); $query = "SELECT * FROM table1 WHERE col1 IN ($valuelist)"; $result = pg_query($query) or die(pg_last_error()); // You may therefore assume that the following will work. $query = 'SELECT * FROM table1 WHERE col1 IN ($1)'; $result = pg_query_params($query, array($valuelist)) or die(pg_last_error()); // Produces error message: 'ERROR: invalid input syntax for integer' // It only works when a SINGLE value specified. // Instead you must use the following approach: $valuelist = '{' . implode(', ', $values . '}' $query = 'SELECT * FROM table1 WHERE col1 = ANY ($1)'; $result = pg_query_params($query, array($valuelist)); ?> The error produced in this example is generated by PostGreSQL. The last method works by creating a SQL array containing the desired values. 'IN (...)' and ' = ANY (...)' are equivalent, but ANY is for working with arrays, and IN is for working with simple lists. mledford
If you are trying to replicate the function pg_query_params, you might also want to support NULL values. While is_int returns true for a NULL value, the formatting for the SQL. function pg_query_params( $db, $query, $parameters ) { // Escape parameters as required & build parameters for callback function global $pg_query_params__parameters; foreach( $parameters as $k=>$v ) { if ( is_null($v) ) { $parameters[$k] = 'NULL'; } else { $parameters[$k] = ( is_int( $v ) ? $v : "'".pg_escape_string( $v )."'" ); } } $pg_query_params__parameters = $parameters; // Call using pg_query return pg_query( $db, preg_replace_callback( '/\$([0-9]+)/', 'pg_query_params__callback', $query)); } |
Change Language
pg_affected_rows pg_cancel_query pg_client_encoding pg_close pg_connect pg_connection_busy pg_connection_reset pg_connection_status pg_convert pg_copy_from pg_copy_to pg_dbname pg_delete pg_end_copy pg_escape_bytea pg_escape_string pg_execute pg_fetch_all_columns pg_fetch_all pg_fetch_array pg_fetch_assoc pg_fetch_object pg_fetch_result pg_fetch_row pg_field_is_null pg_field_name pg_field_num pg_field_prtlen pg_field_size pg_field_table pg_field_type_oid pg_field_type pg_free_result pg_get_notify pg_get_pid pg_get_result pg_host pg_insert pg_last_error pg_last_notice pg_last_oid pg_lo_close pg_lo_create pg_lo_export pg_lo_import pg_lo_open pg_lo_read_all pg_lo_read pg_lo_seek pg_lo_tell pg_lo_unlink pg_lo_write pg_meta_data pg_num_fields pg_num_rows pg_options pg_parameter_status pg_pconnect pg_ping pg_port pg_prepare pg_put_line pg_query_params pg_query pg_result_error_field pg_result_error pg_result_seek pg_result_status pg_select pg_send_execute pg_send_prepare pg_send_query_params pg_send_query pg_set_client_encoding pg_set_error_verbosity pg_trace pg_transaction_status pg_tty pg_unescape_bytea pg_untrace pg_update pg_version |
