Android Question [SOLVED] UPDATE SQL Query to Web Service: can't edit multiple fields

[adrianfreitas]

Hello everyone!
I'm using the web service model in php provided by Erel, as well as the standard code from httputils2: ExecuteRemoteQuery.
These codes have hardly any changes. I'm making it all work before improving web service security, etc.
Everything works ok in queries INSERT, DELETE and UPDATE with 1 field. However, UPDATE queries editing more than one field simply do not work, and my project will need to use them massively.
This is a limitation of available web service model? Is there any other web service sample that works with UPDATE with multiple fields? My php knowledge is not enough to resolve the issue without help.
Thankful already!

 

[DonManfred]

This is a limitation of available web service model?

No
Without seeing how you implement it we hardly can answer

Post your project (or enough code to see) and maybe the server side php file you are using...

Let me say: it IS possible if you implement it right in your php/b4a-code....

 

[adrianfreitas]

Hi DonManfred,
How I said, the code is basically the samples found here at this forum.
When the query edits only 1 field, it works fine (for example: "UPDATE Itens SET valor = '100,00' WHERE Itm = 1000").
But, if it edits multiple fields, not works: (for example: "UPDATE Itens SET item = "Test', valor = '100,00' WHERE Itm = 1000").

The code follows:

Web Service:

<?php
$databasehost = "localhost";
$databasename = "myDB";
$databaseusername ="myUser";
$databasepassword = "123456";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());

mysql_query("SET CHARACTER SET utf8"); // This is the line I added

$queryin = file_get_contents("php://input");
//$query = $_GET["query"];  forget this one, was used just for tests..No need to be here

$sth = mysql_query($queryin);

if (mysql_errno()) {
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error();
}
else
{
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
    }
    print json_encode($rows);
}
?>

And the B4A, the code that execute the query:

Sub ExecuteRemoteQuery(Query As String, JobName As String)
Dim job As HttpJob
ProgressDialogShow("Obtendo dados...")
job.Initialize(JobName, Me)
job.PostString(sDBSvrHost, "UPDATE itens SET item = '" & etItem.Text.Trim & "', valor = '" & etValor.Text.Trim & "', descr = '" & etDescr.Text.Trim & "' WHERE itemid = " & sItID)
End Sub

BTW: the web service returns this message:

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html><head>
<title>403 Forbidden</title>
</head><body>
<h1>Forbidden</h1>
<p>You don't have permission to access /wservc.php
on this server.<br />
</p>
<p>Additionally, a 403 Forbidden
error was encountered while trying to use an ErrorDocument to handle the request.</p>
</body></html>

But I already re-checked the privileges of the user, and other web server settings. So, all other operations work 100%, only the multiple fields update query still not working.

 

[adrianfreitas]

Really need help... searching in google apparently is a limitation with php script used, mas I don't know enough php to fix it.

 

[sorex]

it's security on the serverside that blocks that request.

you php script doesn't get executed at all.

 

[adrianfreitas]

sorex, if I simply change the query to update 1 field at time, ir works perfectly.
I already check privileges and everything is fine. Asking for help to hosting support they said that is not a blocking from server, but some problem in php code. i
Another crazy information: on monday the same error has appeared in a SELECT query that relations two tables... well, it was the first forbidden error in SELECT queries, so I change it a few times, and after rewriting ir using INNER JOIN and use single quote in a value, it works (it was integer so I can't understand why needed single quotes, but is the only way that works)

 

[sorex]

the priveledges has nothing to do with it, not filesystem nor mysql wise.

it's mod_security or something that's on.

try disabling it in .htaccess by creating a .htaccess file on the root of your site (public_html folder?) and copy this in it

<IfModulemod_security.c>
SecFilterEngine Off
SecFilterScanPOST Off
</IfModule>

 

[sorex]

it's bad to send over full queries like that it doesn't need much efforts to trash or modify your data since you send over your database structure over the net.

and it's probably that security stuff that sees it as injection attempts.

 

[adrianfreitas]

Wow!! Days searching the web without a clear answer...
Thank you very much Sorex!!
My hosting allows to turn off mod_security. Just made it and problems stopped!