0

I get always the message "Suit informed doesn't exist in DB."

But even when I put a suit number that exists in DB, it keeps giving same information. And if I remove this part from script, the final message "Update OK" appears as if DB update had occurred, but when I check the DB, nothing has changed.

I'm wondering if the interaction with DB is not occurring, for some reason I do not know what it is.

Form is this:

<html>

<style>

::-webkit-input-placeholder  { color:#CDCDCD; }
input:-moz-placeholder { color:#CDCDCD; }
textarea:-moz-placeholder { color:#CDCDCD; }

</style>


<form name="saque" action="https://equadsaude.000webhostapp.com/bancodados_atualizar.php" method="POST">

<table>

<tr>
<td>Processo</td>         </tr>

<tr>
<td><input name="n1" placeholder="somente algarismos"></td>
</tr>

<tr>
<td>Valor total sacado</td>   </tr>

<tr>
<td><input name="n4" placeholder="00000.00"></td>
</tr>

<tr>
<td>Observações e Data </td> </tr>

<tr>
<td><input type="text" name="n3" ></td>
</tr>

<tr>
<td col span="3"><input type="submit" name="submit" value="Atualizar"></td>
</tr>
</table>
</form>
</html>

And the archive .php is this:

<?php

$conectar = new mysqli("localhost","id1019345_dados_zzzz","xxxx", "id1019345_sobras") or die(mysqli_error());
$processo = $_POST[ 'n1' ] ;
$valor_sacado = $_POST[ 'n4' ] ;
$observacoes = $_POST[ 'n3' ] ;

//testind POST in DB
$teste = mysqli_query($conectar, "SELECT 'id' FROM 'Tab_Index' WHERE 'Processo' = '$processo' ");
while (mysqli_num_rows($conectar, $teste) == 0)
{
echo "<p>Suit informed doesn't exist in DB.</p>";  exit(mysqli_error());
}


//subtract
$sql_seleciona = mysqli_query($conectar, "SELECT 'Valor_sobra' FROM 'Tab_Index' WHERE 'Processo' = '$processo' ");
while ($query_row = mysqli_fetch_assoc($conectar, $sql_seleciona))
    {
    foreach($query_row as $key => $value)
                       {
                       $resultado = $value-$valor_sacado;
                       }
    }

//adding observations
$sql_seleciona2 = mysqli_query ($conectar, "SELECT 'Observacoes' FROM 'Tab_Index' WHERE 'Processo' = '$processo' ");
while ($query_row2 = mysqli_fetch_assoc($conectar, $sql_seleciona2))
    {
    foreach($query_row2 as $key => $value)
                       {
                       $resultado2 = $query2."/". $observacoes;
                       }

     }

//Update DB
$sql_alterar = mysqli_query($conectar, "UPDATE 'Tab_Index' SET 'Valor_sobra' =  '$resultado1', 'Observacoes' =  '$resultado2' WHERE 'Processo' = '$processo' ");

if  ( isset ($sql_alterar) )
{
print "<h3> Update OK </h4>\n" ;
}
else 
{ 
print "<h3> Error updating </h4>\n" ;
}


  ?>

DB has 4 columns: id, Processo (BIGINT), Valor_sobra (DECIMAL 7,2), Observacoes (VARCHAR). HOST: localhost USERNAME: id1019345_dados_zzzz SENHA: xxxx DB: id1019345_sobras

Paul Schreiber
  • 12,531
  • 4
  • 41
  • 63
Ricardo
  • 1
  • Don't write code like `"SELECT 'Valor_sobra' FROM 'Tab_Index' WHERE 'Processo' = '$processo'` — you're asking for an SQL injection hack. Use prepared statements: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php – Paul Schreiber Mar 14 '17 at 19:47
  • mysqli_num_rows takes in 1 parameter, which is mysqli_result. aka change your code from `mysqli_num_rows($conectar, $teste)` to `mysqli_num_rows($teste)` and see if it works – Dimi Mar 14 '17 at 19:51

3 Answers3

1

It looks like your SQL is invalid. If you check the value of mysqli_error(), you'll probably see something.

You shouldn't have quotes around column names or table names. (You can use backticks.)

SELECT 'id' FROM 'Tab_Index' WHERE 'Processo' = '$processo'

totally invalid.

SELECT 'id' FROM Tab_Index WHERE 'Processo' = '$processo'

will select the literal string 'id' instead of the id column

SELECT id FROM Tab_Index WHERE Processo = '$processo'

This is what you want.

See also my note above about SQL injection vulnerabilities and this SO answer:

How to create a secure mysql prepared statement in php?

Community
  • 1
  • 1
Paul Schreiber
  • 12,531
  • 4
  • 41
  • 63
  • +1 If that is all true, you have cleared out a 4 year old confusion about how to write sql queries inside quotes. I knew how to write them and write them properly and they work, but I always got the feeling of doing something incorrect which might not accommodate future scaling. hehe. Thanks. – coderodour Mar 14 '17 at 20:34
0

Remove single quote around the 'Tab_Index' .write instead `Tab_Index`. Because variable inside single quote is treated as plain string

So write query like following "SELECT `id` FROM `Tab_Index` WHERE `Processo` = $processo" and so on ...

Rafiqul Islam
  • 1,636
  • 1
  • 12
  • 25
0

I agree with @scaisEdge regarding being careful with sql injection as passing the variable directly to the query is not sanitized and thus leaving you exposed to attacks.

Following that comment I would propose you take a look at PreparedStatements (http://php.net/manual/en/mysqli.prepare.php)

 $conexion = new mysqli(...);
 $prepStmt = $conexion->stmt_init();

 $prepSql  = 'SELECT `id` FROM `Tab_Index` WHERE `Processo` = ?';
 $prepStmt->prepare($conexion, $prepSql);
 $prepStmt->bind_param('s', $proceso);
 $prepStmt->execute();

 $numResultados = $prepStmt->num_rows;

Note: though the function mysqli_num_rows exist, it is not directly mentioned on the documentation other than as a procedural function to $mysqli_result->num_rows

Lastly, by looking at the code, I notice that you are implementing everything straight, I would recommend you look into some framework that would help you abstract and simplify all this functionality and give you also more structure and scalability.

Some of the ones you could take a look are (try to look for the latest versions)

  • Laravel
  • Zend
  • Phalcon
  • Yii
AlphaZygma
  • 228
  • 2
  • 9