PHP - ORA-01747：user.table.column，table.column或列规范无效

Question

I'm trying to update a table called RAC_PV at my oracle database but the query is returning that error when i run my application. I thing everyting is right because i run the same query on sqldeveloper (but with manual values) and when i change the variable $preco_medio to a number like 1, it works on the application. Here's my code:

$preco_medio = number_format(($v_interna_real / $encargo + $vipe_real) / $v_interna_t + $vipe_t , 2, ",", ".") ;

sc_exec_sql("update RAC_PV set PRECO_MEDIO = $preco_medio where rac_anopv = {ano} and rac_mespv = {mes} and codempresa = $id_empresa and codpainel = 6 and cod_prod1 = '$id_produto'");

My table name and the column names are right and checked, my variables like {ano}, {mes}, $id_produto and $preco_medio are all corretly seted. This sc_exec_sql("query here") works fine. Maybe the problem is in the $preco_medio ? I've never used that number_format but when a echo this the number shows right for me like 3,123.03 .

Can anyone help me?

Answer 1

3,123.03

That's not a number literal, that's two numbers, 3 and 123.03, one of which is apparently interpreted as column name. Remove the grouping , in the number literal expression or use TO_NUMBER() and a string literal with the formatted number representation.

The best way however would be not to build queries by string concatenation but parametrized queries. String concatenation is prone to SQL Injection. Also parametrized queries find the right representation for the DBMS (usually and mostly) automatically, avoiding such problems you have here.