Problem with running php scripts on Webserver with urlconnection

Databases
1

From a desktop app i need to save data in a database on the web.
I know that it’s not a good idea to connect directly to the database because of the reconnect-thing. So i tried to build up small Webservice.

For saving i’m using the urlconnection for the POST request running a php script on the webserver with the INSERT and UPDATE statements for the mySql.

Now the problem, my Webserver regularly rejects my IP-adress because of the suspicion of sql injection.

Thanks for any ideas to prevent this.

2

Your SQL statements are part of the POST request? That’s… not recommended. You should have http endpoints for given tasks that take parameters, and the SQL is in your server scripts. Look up what a REST API is, it’s a pretty common model.

Doing what you’ve done may actually be worse than connecting directly, as you’re essentially just replacing the database-level authentication with your own, which (no offense intended) is probably not as robust, and certainly not as reviewed and tested.

Or I’m understanding wrong.

1 Like
3

Thanks Thom, but my SQL statements are not in the POST request.

I have several php scripts e.g. something like this

   include('../dbcon.php');
    
    //Server-Connection
    $conn = mysqli_connect($host,$user,$pw,$db) or die("Fehler: " . mysqli_error($connection));
    
    // prepare and bind
    $sql="INSERT INTO tblappverlauf (vDatum,vZeit,user_id,vArt) VALUES(?,?,?,?);";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ssii", $vdatum, $vzeit, $userid, $vart);

    $vdatum = $_POST["vdatum"];
    $vzeit = $_POST["vzeit"];
    $userid = $_POST["userid"];
    $vart = $_POST["vart"];

    $stmt->execute();
    $stmt->close();
    $conn->close();

And i call theses scripts from Xojo, for example like this:

Var postdata() As String
postdata.Add("vdatum="+aktdat.SQLDate)
postdata.Add("vzeit="+aktdat.LongTime)
postdata.Add("userid="+currUser.ID.ToString)
postdata.Add("vart="+vArt.ToString)

var data as String=String.FromArray(postdata,"&")
var ConMain as new MainCloudCon
ConMain.SetRequestContent(data, "application/x-www-form-urlencoded")
ConMain.Send("POST", wcon.serverpfad+"/main/addappverlauf")
4

Ok, good.

So in that case, I don’t have any idea which service would be complaining. It sounds like firewall, but I wouldn’t expect it to be doing THAT level of inspection, especially over an TLS connection. Do you know which http server you’re running?

5

Thanks again Thom,

the problem is not with the firewall, the Provider which is hosting my domain told me, that my IP was rejected because of the suspicion of SQL injection. This comes from executing this code like above.

They were speculating that it is because the “POST” is executed from an external app (Windows app) and not from the webserver itself. With “GET” i get no problems, only with “POST”

So i was thinking my way of sending the request content ist wrong.

6

Yeah, that’s why I was wondering which webserver it is. This is an unusual behavior. An HTTP request is an HTTP request. They are doing something to your traffic. They might be injecting cookies to help identify browsers from apps, but it’s hard to say for sure. Or maybe they reject POST requests without a referrer? Whatever it is, it’s not normal.

7

OK, the most important thing for me was, that i’m not completely wrong with this code.

What is the “referrer”? Wouldn’t that be a way to use them?

Sorry, i’m not so familiar with all the HTTP stuff :wink:

8

The referrer tells the server where you came from. Essentially the last url that was visited. It’s mostly used for following links. But you could set it in your request such as

URLConnection1.RequestHeader("Referer") = "https://mywebsite.com/"

You might also set your user agent header to match a real browser:

URLConnection1.RequestHeader("User-Agent") = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.0 Safari/605.1.15"

(Edited to “correct” header spelling)

9

Check your spelling: HTTP referer - Wikipedia

10

That’s funny. I’ve been dealing with HTTP for about 30 years and I can’t say I’ve ever noticed. Good catch.

11

I think it is more likely they have an IPS (intruder prevention system), like fail2ban, scanning the access log file for anomalies. The advice to set a user agent is spot on. Also consider a switch to using json to exchange query parameters and data with the web server.

12

Move from “form like POSTs” to JSON.

13

Oh yes, i was thinking about that too.

14

Log scanning is something I hadn’t considered, and makes a ton of sense. In my second comment I said it sounded like the firewall, but I couldn’t figure out how it’d be breaking into the TLS data. With log scanning, it doesn’t have to, so I agree this sounds like exactly what is going on.

15

Just properly move to JSON. The system will know its not an user attack using a form inserting things like "'; DROP TABLE USERS; – " in the fields.

16

How to get a JSON packet in PHP?

I use this:

mb_internal_encoding("UTF-8");

$json = file_get_contents('php://input');

// Decode the JSON string
$js = json_decode($json);

// check if $js is null for fails, then you can play with the contents as $js->a_field_name
17

Which is honestly pretty silly. An injection attack is just as possible in JSON data. But yes, it should work.

18

Not as silly as an user and a form, that’s the silly level the provider is trying to avoid.
If you want extra security measures, use some authentication scheme and insert unique mutable confirmation codes in your JSON packets, as AUTH protocols usually does.

19

To send a Xojo JSON data and receive a JSON response (fast, slow responses should be async):

Var http As New URLConnection

Var jsonStr As String = "{ ... }" // some valid json content

http.SetRequestContent(jsonStr, "application/json; charset=utf-8") // prepare to send

Try
  jsonStr = http.SendSync("POST", "https://www.thesite.com/api/v1/proper_endpoint.php", 5).DefineEncoding(Encodings.UTF8)
Catch
  jsonStr = "" // fail
End

Var json As JSONItem

Try
  json = New JSONItem(jsonStr)
Catch
  json = New JSONItem // Discard. Garbage received
End

// process the json response
20

Thanks to all!

And special thanks to Rick for the JSON code snippet.

1 Like