I can not insert content with special characters to database

Question

I can not insert content with special characters to database

Navigation

#1 by (0 votes)
#2 by (-1 votes)

0

I am working on importing an Excel with the PHPExcel library with PHP in codeigniter.

It has characters like " " , not allowing the insertion to my database with Postgres. This is my function within the model that inserts the records:

public function excel($table_name,$sql)
    {
        //si existe la tabla
        if ($this->db->table_exists("$table_name"))
        {
            //si es un array y no está vacio
            if(!empty($sql) && is_array($sql))
            {

                //si se lleva a cabo la inserción
                if($this->db->insert_batch("$table_name",$sql))
                {
                    return TRUE;
                }else{
                    return FALSE;
                }
            }
        }
    }

The error that throws me is the following:

Error Number:

ERROR: syntax error at or near ""country_sir,ps_pool"" LINE 1: ...on, comentario, current_cs_pool, id_country_sir," "country_s... ^

INSERT INTO "ept" ("att_site_name, att_node_name, att_name, att_tech, att_cell_id, att_cell_id_name, node_b_m2000, name, latitud, longitud, state, id_state, country, id_country, region, market, distance_from_mex_usa_border_line_miles, range, time_zone, cell_average_heihgt, altitude_m, solution, coverage, infraestructure_type, owner, mcc, mnc, nir, propagation_model, cell_radius_m, status, frec, band_indicator, band, band_width, ul_fc_mhz, dl_fc_mhz, ul_uarfcn_earfcn, dl_uarfcn_earfcn, carrier, cellname, node_b_id, physical_sector, cell_id, local_cell_id, psc_pci, rnc, rnc_id, lac_tal, tac, rac, ura, sac, time_offset, max_transmit_power_of_cell, pcpich_rs_power, antenna_height, geographic_azimuth, magnetic_declination, magnetic_azimuth, mech_tilt, elect_tilt, antenna_count_per_sector, antenna_model, beam_width, root_sequence_idx, tx_rx, ept_date, project, batch, update_type, update_date, vendor, cluster, operador, tracker, asl_local, asl_ift, nir_ift, _911, _89, region_celular, region_pcs, municipio, id_location, location, comentario, current_cs_pool, id_country_sir," "country_sir,ps_pool") VALUES (NULL)

Filename: C:/xampp2/htdocs/rf/system/database/DB_driver.php

Line Number: 691

php codeigniter postgresql

asked by Javier Viveros 02.12.2017 в 00:57

source

2 answers

How to convert a for from C to python, "It has to be programmed in c" how to pass parameter on the route from a controller in laravel?

score 0 · Answer 1

Basically you have to escape the INPUT before sending it to the database, you are not providing much information on how these data are inserted in your database, so I share several general ideas that you can apply:

pg_escape_string: This function escapes a string making it safe to insert

$datos_seguros = pg_escape_string($texto_a_insertar);
pg_query("INSERT INTO table (name, data) VALUES ('texto inseguro', '{$datos_seguros}')");

pg_prepare: Prepare a query to accept parameters only, the parameters sent will be automatically escaped. Useful when the query is executed many times.

pg_prepare($dbconn, "my_query", 'INSERT INTO table (name, data) VALUES ($1, $2)'); //Aquí preparas tu query.
while(){
  $resultado = pg_execute($dbconn, "my_query", [$string_sucio, $string_sin_escapar]);
}

pg_query_params: Prepares and executes an anonymous query. It is very useful when executing the query ONLY once

$resultado = pg_query_params($dbconn, 'INSERT INTO table (name, data) VALUES ($1, $2)', [$string_sucio, $string_sin_escapar]);

REGEX : You can delete ALL that you do not want to insert into your document, this example returns an alphanumeric string.

$resultado = preg_replace("/[^a-zA-Z0-9]+/", "", $string_sucio);

score -1 · Answer 2

You would have to escape the special characters. In programming when you write a String you do it in double quotes "" (Although not in all languages). When you write an SQL statement in programming you write it in a String that starts with ", but if part of the instruction you want to write has double quotes somewhere the compiler understands that the String has been closed in. To avoid this problem, the normal thing is escape the special characters by putting a backslash \ even on them.

For this there are several functions that allow to escape all the special characters of a String as the first proposal that Solrac has made or the one I have put before.

Maybe this will help: link