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

adrianfreitas · Sep 28, 2016

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 · Sep 28, 2016

adrianfreitas said:
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 · Sep 28, 2016

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:

<?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:

B4X:

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:

B4X:

<!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 · Sep 29, 2016

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 · Sep 29, 2016

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

you php script doesn't get executed at all.

adrianfreitas · Sep 29, 2016

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 · Sep 29, 2016

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 · Sep 29, 2016

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 · Sep 29, 2016

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!

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

adrianfreitas

Member

DonManfred

Expert

adrianfreitas

Member

adrianfreitas

Member

sorex

Expert

adrianfreitas

Member

sorex

Expert

sorex

Expert

adrianfreitas

Member

Similar Threads