Connecting with online Mysql database via JUCE and php

General JUCE discussion
1

I’m having a little trouble, and I’m not sure if it’s my mis-use of JUCE, or overall misunderstanding of the process:

I want to store some of my users’ information in an online Mysql database, when they first open the app. After they register and put in a valid email and serial # I call:

String url = "http://www.syntorial.com/onlinePHPFileToCall.php?email=" + email + "&computer=" + SystemStats::getComputerName();
URL authURL(url);
InputStream *input = authURL.createInputStream(true);
String result = input->readString();

The online php code:

<?php
    
    $result = "";
    
    $mysqli = new mysqli('localhost','username','password','dbname');
    
    if (mysqli_connect_errno())
    {
        $result = "connection failed";
    }
    else
    {
        $mysqli->select_db("UserInfo");
        
        $email = $_GET['email'];
        $computer = $_GET['computer'];
        
        $query = "UPDATE UserInfo SET computer = '$computer' WHERE email = '$email'";
        
        if ($queryResult = $mysqli->query($query))
        {
            $result = "true";
        }
        else
        {
            $result = "false";
        }
    }
    
    echo $result;
?>

My app gets “true” back every time. But when I go online and look in my database the computer name hasn’t been added. Furthermore, if I just go into a browser and enter that url in manually, it works. Any suggestions?

2

I made a little headway. I changed:

URL authURL(url);
InputStream *input = authURL.createInputStream(true);
String result = input->readString();

to:

WebBrowserComponent web;
web.goToURL(url);

And it worked, but I’m not sure how to get the result of the call from the php file. Any ideas?

3

Firstly I’d take a look in your apache log file. You should see the requested URL shown. This’ll immediately give you clues if something is getting mangled.

That said:

should be called ‘false’, no? Your test is performed using a GET and your PHP is expecting GET vars, but you’re doing a POST here if I’m reading the docs right[1].

[1] I do a fair amount of HTTP work, but I actually have never used any of JUCE’s classes for this.

4

That’s it! I guess it was ignoring the GETS. Thanks valley.

5

BTW, I’m sure you know this, but please don’t do:

on a production server, even one that should be safely firewalled off from the Internet at large; it makes little Bobby Drop Tables get all squirrelly. Even in a sandboxed world where only your app can talk to the HTTP server, you’re still opening a possibility for errant code on the client side to inadvertently send a hostile request to the web server.

6

How else can I update the table without that query?

7

Use PHP’s mysqli:prepare method at the least. This is an old but reasonably decent treatment of the subject: http://www.databasejournal.com/features/php/article.php/3599166/Connecting-and-prepared-statements-with-the-mysqli-extension.htm

Essentially what you’re trying to avoid is someone passing a value that contains some kind of escape character. The most simple example is fred’; Inserted directly into your query string that’ll allow an arbitrary SQL instruction to be appended to the request. I.E.

UPDATE UserInfo SET computer = ‘fred’ WHERE 1 = 0;; --’ WHERE email = ‘foo@bar’

(the first clause fails to match, so is ignored, the second could be a big assed mess, and the third is just a comment, so is ignored). See also: http://xkcd.com/327/

There’s a bunch of strategies for sanitizing inputs to SQL queries taken from HTTP requests. The oldskool mysql_real_escape_string will do at a push, but I have in mind it has weaknesses. Numbers are easy as you can use printf to ensure that what you’re passing the database is in fact a number. Using stored procedures on the SQL server, rather than dynamically generated SQL is nearly always preferable. mysqli:prepare, as mentioned above is a decent compromise if stored procedures are overkill for the task.

8

Lots of good info. I’ll look into that. Thanks again.

9

I also have the same question…I guess it can be updated in grids without using that query…If so kindly specify the prior method…

Check out Big Data Companies

10

You can use the query but you need to validate and escape all data that you receive from the client. Here is a description how to do it: http://www.php.net/manual/en/security.database.sql-injection.php