Erro no comando SQL -- não consigo achar

[suprete (jackson suprete)]

Olhei alguns tópicos aqui e os que achei que talvez pudessem ter o que procuro diz que a página não existe mais.

Quando dá esse erro a página da aplicação fica em branco e não exibe a mensagem de sucesso, mas ele importa o arquivo para o banco.

Esse é o SQL:

   /*****************************************************************************************
*					Processamento Principal da Carga do Excel
******************************************************************************************
*/

//=====	Realiza o chamado da biblioteca PHPExcel, que esta incluida no SC
sc_include_lib("excel");

//=====	Variavel que possui o endereço do Planilha
$rutaXLS = $_SESSION['scriptcase']['controlCargaExcel']['glo_nm_path_doc'].'/'.{arquivo};

//=====	Carga do Arquivo informado
$objPHPExcel=PHPExcel_IOFactory::load($rutaXLS);

// pega o nome do arquivo
$nome = $arquivo;

// pega regional
$regional = {regionais};

//data-testes
$dt_insert = {dia};


//=====	Leitura das linhas do Excel
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet){ 

	//=====	Ttitulo da Celula do Excel
	$worksheetTitle     = $worksheet->getTitle();
	//=====	Quantidade de Linhas 
	$highestRow         = $worksheet->getHighestRow();
	//=====	Quantidade de Colunas
	$highestColumn      = $worksheet->getHighestColumn();
	
	//=====	iremos acessar a linha que tem informações, iniciar pela linha 1
	//=====	linha 1 contem o cabeçalho e/ou informações
	for ($row=2;$row<= $highestRow; ++ $row){

		//===== Coluna A
		$cell = $worksheet -> getCellByColumnAndRow (0, $row);
		$colunaA = $cell ->getValue();
		//===== Coluna B
		$cell = $worksheet -> getCellByColumnAndRow (1, $row);
		$colunaB = $cell ->getValue();
		//===== Coluna C
		$cell = $worksheet -> getCellByColumnAndRow (2, $row);
		$colunaC = $cell ->getValue();
		//===== Coluna D
		$cell = $worksheet -> getCellByColumnAndRow (3, $row);
		$colunaD = $cell ->getValue();
		//===== Coluna E
		$cell = $worksheet -> getCellByColumnAndRow (4, $row);
		$colunaE = $cell ->getValue();
		//===== Coluna F
		$cell = $worksheet -> getCellByColumnAndRow (5, $row);
		$colunaF = $cell ->getValue();
		//===== Coluna G
		$cell = $worksheet -> getCellByColumnAndRow (6, $row);
		$colunaG = $cell ->getValue();
		//===== Coluna H
		$cell = $worksheet -> getCellByColumnAndRow (7, $row);
		$colunaH = $cell ->getValue();
		//===== Coluna I
		$cell = $worksheet -> getCellByColumnAndRow (8, $row);
		$colunaI = $cell ->getValue();
		//===== Coluna J
		$cell = $worksheet -> getCellByColumnAndRow (9, $row);
		$colunaJ = $cell ->getValue();
		//===== Coluna K
		$cell = $worksheet -> getCellByColumnAndRow (10, $row);
		$colunaK = $cell ->getValue();
		//===== Coluna L
		$cell = $worksheet -> getCellByColumnAndRow (11, $row);
		$colunaL = $cell ->getValue();
		//===== Coluna M
		$cell = $worksheet -> getCellByColumnAndRow (12, $row);
		$colunaM = $cell ->getValue();
		//===== Coluna N
		$cell = $worksheet -> getCellByColumnAndRow (13, $row);
		$colunaN = $cell ->getValue();
		//===== Coluna O
		$cell = $worksheet -> getCellByColumnAndRow (14, $row);
		$colunaO = $cell ->getValue();
		//===== Coluna P
		$cell = $worksheet -> getCellByColumnAndRow (15, $row);
		$colunaP = $cell ->getValue();
		//===== Coluna Q
		$cell = $worksheet -> getCellByColumnAndRow (16, $row);
		$colunaQ = $cell ->getValue();
		//===== Coluna R
		$cell = $worksheet -> getCellByColumnAndRow (17, $row);
		$colunaR = $cell ->getValue();
		//===== Coluna S
		$cell = $worksheet -> getCellByColumnAndRow (18, $row);
		$colunaS = $cell ->getValue();
		//===== Coluna T
		$cell = $worksheet -> getCellByColumnAndRow (19, $row);
		$colunaT = $cell ->getValue();
		//===== Coluna U
		$cell = $worksheet -> getCellByColumnAndRow (20, $row);
		$colunaU = $cell ->getValue();
		//===== Coluna V
		$cell = $worksheet -> getCellByColumnAndRow (21, $row);
		$colunaV = $cell ->getValue();
		//===== Coluna W
		$cell = $worksheet -> getCellByColumnAndRow (22, $row);
		$colunaW = $cell ->getValue();
		//===== Coluna X
		$cell = $worksheet -> getCellByColumnAndRow (23, $row);
		$colunaX = $cell ->getValue();
		//===== Coluna Y
		$cell = $worksheet -> getCellByColumnAndRow (24, $row);
		$colunaY = $cell ->getValue();
		//===== Coluna Z
		$cell = $worksheet -> getCellByColumnAndRow (25, $row);
		$colunaZ = $cell ->getValue();

		$insert_sql = "INSERT INTO timbr (dt_insert, regional, nome, colunaA, colunaB, colunaC, colunaD, colunaE, colunaF, colunaG, colunaH, colunaI, colunaJ, colunaK, colunaL, colunaM, colunaN, colunaO, colunaP, colunaQ, colunaR, colunaS, colunaT, colunaU, colunaV, colunaW, colunaX, colunaY, colunaZ)  
		VALUES ('$dt_insert'=sc_sql_injection($dt_insert), '$regional'=sc_sql_injection($regional), '$nome'=sc_sql_injection($nome),   
'$colunaA'=sc_sql_injection(colunaA);
'$colunaB'=sc_sql_injection(colunaB);
'$colunaC'=sc_sql_injection(colunaC);
'$colunaD'=sc_sql_injection(colunaD);
'$colunaE'=sc_sql_injection(colunaE);
'$colunaF'=sc_sql_injection(colunaF);
'$colunaG'=sc_sql_injection(colunaG);
'$colunaH'=sc_sql_injection(colunaH);
'$colunaI'=sc_sql_injection(colunaI);
'$colunaJ'=sc_sql_injection(colunaJ);
'$colunaK'=sc_sql_injection(colunaK);
'$colunaL'=sc_sql_injection(colunaL);
'$colunaM'=sc_sql_injection(colunaM);
'$colunaN'=sc_sql_injection(colunaN);
'$colunaO'=sc_sql_injection(colunaO);
'$colunaP'=sc_sql_injection(colunaP);
'$colunaQ'=sc_sql_injection(colunaQ);
'$colunaR'=sc_sql_injection(colunaR);
'$colunaS'=sc_sql_injection(colunaS) ;
'$colunaT'=sc_sql_injection(colunaT);
'$colunaU'=sc_sql_injection(colunaU);
'$colunaV'=sc_sql_injection(colunaV);
'$colunaW'=sc_sql_injection(colunaW);
'$colunaX'=sc_sql_injection(colunaX);
'$colunaY'=sc_sql_injection(colunaY);
'$colunaZ'=sc_sql_injection(colunaZ)";
	
		sc_exec_sql($insert_sql);
		sc_commit_trans();
		
	}
}

Esse é o erro que dá no modo debug:

(pdo-mysql): INSERT INTO timbr (dt_insert, regional, nome, colunaA, colunaB, colunaC, colunaD, colunaE, colunaF, colunaG, colunaH, colunaI, colunaJ, colunaK, colunaL, colunaM, colunaN, colunaO, colunaP, colunaQ, colunaR, colunaS, colunaT, colunaU, colunaV, colunaW, colunaX, colunaY, colunaZ)
 VALUES ('2021-03-08'=->qstr(2021-03-08), '5'=->qstr(5), 'ENG-SI-0027-19-ACE-Ativação 4G-TNO-NLMNSO33.xlsx'=->qstr(ENG-SI-0027-19-ACE-Ativação 4G-TNO-NLMNSO33.xlsx), ''=->qstr(colunaA); ''=->qstr(colunaB); ''=->qstr(colunaC); ''=->qstr(colunaD); ''=->qstr(colunaE); ''=->qstr(colunaF); ''=->qstr(colunaG); ''=->qstr(colunaH); ''=->qstr(colunaI); ''=->qstr(colunaJ); ''=->qstr(colunaK); ''=->qstr(colunaL); ''=->qstr(colunaM); ''=->qstr(colunaN); ''=->qstr(colunaO); ''=->qstr(colunaP); ''=->qstr(colunaQ); ''=->qstr(colunaR); ''=->qstr(colunaS ; ''=->qstr(colunaT); ''=->qstr(colunaU); ''=->qstr(colunaV); ''=->qstr(colunaW); ''=->qstr(colunaX); ''=->qstr(colunaY); ''=->qstr(colunaZ)
 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->qstr(2021-03-08), '5'=->qstr(5), 'ENG-SI-0027-19-ACE-Ativação 4G-TNO-NLMNSO3' at line 2

ADOConnection._Execute(INSERT INTO timbr (dt_insert, regional, nome, colunaA, colunaB, colunaC, colunaD, colunaE, colunaF, colunaG, colunaH, colunaI, c..., false) % line 1149, file: [adodb.inc.php](file:///C:/Program%20Files/NetMake/v9-php73/wwwroot/scriptcase/prod/third/adodb/adodb.inc.php) ADOConnection.Execute(INSERT INTO timbr (dt_insert, regional, nome, colunaA, colunaB, colunaC, colunaD, colunaE, colunaF, colunaG, colunaH, colunaI, c...) % line 1908, file: [controlCargaExcel_apl.php](file:///C:/Program%20Files/NetMake/v9-php73/wwwroot/scriptcase/app/projetoBetween/controlCargaExcel/controlCargaExcel_apl.php) controlCargaExcel_apl.Valida_campos(null, null, null) % line 1151, file: [controlCargaExcel_apl.php](file:///C:/Program%20Files/NetMake/v9-php73/wwwroot/scriptcase/app/projetoBetween/controlCargaExcel/controlCargaExcel_apl.php) controlCargaExcel_apl.controle() % line 2177, file: [index.php](file:///C:/Program%20Files/NetMake/v9-php73/wwwroot/scriptcase/app/projetoBetween/controlCargaExcel/index.php)

[jlboutin61 (Jean-Luc Boutin)]

You should insert only the value of the field, not the function

$dt_insert = sc_sql_injection($dt_insert);
$regional = sc_sql_injection($regional);
…

$insert_sql = "INSERT INTO timbr (dt_insert, regional, …) VALUES ($dt_insert, $regional, …)

[suprete (jackson suprete)]

With or without “sc_sql_injection” the error persists.

[jlboutin61 (Jean-Luc Boutin)]

If you have some field with ’ you need to filter them

[suprete (jackson suprete)]

Yes, in the spreedshhet many cells have ’ in the content. that’s why i’m using “sc_sql_injection”.

[jlboutin61 (Jean-Luc Boutin)]

sql_injection doesn’t filter ', it filter html / JS code for bad behavior

you need to do str_replace to remove the ’

$str = str_replace("'", "''", $str);

[suprete (jackson suprete)]

I got it wrong how _sql_injection works then. But how do I insert str_replace into my query? I’m new in this tool and not manage yet how to use all macros and other things.

[suprete (jackson suprete)]

My code read a spreedsheet and put the results in the database. For this i have:

//===== Calling PHPExcel library
sc_include_lib(“excel”);

//===== Get the spreedsheet adress
$rutaXLS = $_SESSION[‘scriptcase’][‘controlCargaExcel’][‘glo_nm_path_doc’].’/’.{arquivo};

//===== loading the file
$objPHPExcel=PHPExcel_IOFactory::load($rutaXLS);

//===== readinh the lines
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet){

//=====	Columm Title
$worksheetTitle     = $worksheet->getTitle();
//=====	Number of lines 
$highestRow         = $worksheet->getHighestRow();
//=====	Columm qty
$highestColumn      = $worksheet->getHighestColumn();

//=====	iremos acessar a linha que tem informações, iniciar pela linha 1
//=====	linha 1 contem o cabeçalho e/ou informações
for ($row=2;$row<= $highestRow; ++ $row){

	//===== Coluna A
	$cell = $worksheet -> getCellByColumnAndRow (0, $row);
	$colunaA = $cell ->getValue();
	//===== Coluna B
	$cell = $worksheet -> getCellByColumnAndRow (1, $row);
	$colunaB = $cell ->getValue();
	//===== Coluna C
	$cell = $worksheet -> getCellByColumnAndRow (2, $row);
	$colunaC = $cell ->getValue();
	//===== Coluna D
	$cell = $worksheet -> getCellByColumnAndRow (3, $row);
	$colunaD = $cell ->getValue();

and so on

I guess I have to use str_replace somewhere here

[jlboutin61 (Jean-Luc Boutin)]

$cell = $worksheet -> getCellByColumnAndRow (0,$row);
$colunaA = str_replace("'","''", $cell ->getValue());

$cell = $worksheet -> getCellByColumnAndRow (1,$row);
$colunaB = str_replace("'","''", $cell ->getValue());

…

It will be more efficient to get the data in an array, and then save it in the database but that’s another problem

How to use phpexcel to read data and insert into database?

[suprete (jackson suprete)]

I want to thank you a lot mr Jean-Luc Boutin. Your help save my project