Connecting with online Mysql database via JUCE and php


#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