Access outside database with a Corona app

rxmarccall · August 3, 2011, 9:10pm

Hey guys,
so ive been making apps with corona for a good while now, and i love how easy it makes many tasks. A major question that i have had recently, is… is corona able to communicate with outside databases? I have clients that would like a app built for their business, but my concern is if corona can communicate information to a server or database. Any links or comments would be greatly appreciated, thank you [import]uid: 19620 topic_id: 13273 reply_id: 313273[/import]

mroberti · August 3, 2011, 9:31pm

Yeah definitely a can-do! I use the async http calls to fire off a call to my PHP script living on my server to retrieve info from the database. I’m no web services or PHP programming guru, so if *I* can do it, that says a lot!

I guess it depends on the complexity, etc. of the client’s needs, but I can grab info from my SQL database out there in the cloud upon running, download it and write it to a local database on the device (your bandwidth needs may vary) and then I still fire off a few dozen calls here and there to grab updated info. Lemme know if you want an example!

(EDIT) Hell, I even just have it working with a minimal version of SQL Azure on the MS platform! But don’t ask for any help with asp webservices and webroles, I’m a dunce when it comes to that. [import]uid: 11636 topic_id: 13273 reply_id: 48734[/import]

rxmarccall · August 3, 2011, 10:15pm

Thanks for the reply! I would love it if you could give me some example code, basically my client wants an app that displays a list of items with price, so I would like to be able and update from an online database the list of items. [import]uid: 19620 topic_id: 13273 reply_id: 48736[/import]

renvis1 · August 4, 2011, 12:13am

You can create a .net or php code to return an xml file
the output can be somethign like this
http://technowand.com/xml/clients.xml

now you can parse this XML file to get the data
I used this to parse the xml file

[lua]function main()

– XMLParser.lua file should be in the folder
local parser = require( “XMLParser” )

– calling function to parse XML file
– returns a lua table
–from a local xml file
–local tab1 = parser.ParseXmlFile(“result.xml”)

– calling function to parse XML from website
local tab1 = parser.getXMLData(“http://technowand.com/xml/clients.xml”)

for i=1,3,1 do
for j=1,5,1 do
print(tab1[i][j])
end
end
end

main()[/lua]

the parser function

[lua] module(…, package.seeall)
– ======================================================================================
– XML Parser
– Renjith Viswanath
– Technowand
– ======================================================================================

http = require(“socket.http”)
ltn12 = require(“ltn12”)

local function parseColumns (xmlTree )
local row = {}
for i,xmlNode in pairs(xmlTree.ChildNodes) do
table.insert(row, xmlNode.Value)
end
return row
end

local function parseRows (xmlTree)
local tab = {}
for i,xmlNode in pairs(xmlTree.ChildNodes) do
local row = parseColumns (xmlNode);
table.insert(tab, row)
end
return tab
end
function ToXmlString(value)
value = string.gsub (value, “&”, “&”); – ‘&’ -> “&”
value = string.gsub (value, " “<”
value = string.gsub (value, “>”, “>”); – ‘>’ -> “>”
–value = string.gsub (value, “’”, “’”); – ‘’’ -> “’”
value = string.gsub (value, “”", “”"); – ‘"’ -> “”"
– replace non printable char -> " "
value = string.gsub(value, “([^%w%&%;%p%\t%])”,
function ©
return string.format("%X;", string.byte©)
–return string.format("%02X;", string.byte©)
–return string.format("%02d;", string.byte©)
end);
return value;
end

function FromXmlString(value)
value = string.gsub(value, “([%x]+)%;”,
function(h)
return string.char(tonumber(h,16))
end);
value = string.gsub(value, “([0-9]+)%;”,
function(h)
return string.char(tonumber(h,10))
end);
value = string.gsub (value, “”", “”");
value = string.gsub (value, “’”, “’”);
value = string.gsub (value, “>”, “>”);
value = string.gsub (value, “<”, " value = string.gsub (value, “&”, “&”);
return value;
end

function ParseArgs(s)
local arg = {}
string.gsub(s, “(%w+)=([”’])(.-)%2", function (w, _, a)
arg[w] = FromXmlString(a);
end)
return arg
end

function ParseXmlFile(xmlFileName)
local path = system.pathForFile( xmlFileName, system.ResourcesDirectory )
local hFile = io.open(path,“r”);
if hFile then
local xmlText=hFile:read("*a"); – read file content
io.close(hFile);
local tab = parseRows (ParseXmlText(xmlText));
return tab
else
return nil
end
end

– download the xml data
– params: url - url to use for downloading the xml data
function getXMLData(url)
local srcfile = “result.xml”
local path = system.pathForFile(srcfile, system.ResourcesDirectory)
local myFile = io.open(path,“w”)
local xmltext = http.request{
url = url,
sink = ltn12.sink.file(myFile),
}
io.close(myFile)
return ParseXmlFile(srcfile)
end [/lua] [import]uid: 71210 topic_id: 13273 reply_id: 48739[/import]

renvis1 · August 4, 2011, 12:14am

You can create a .net or php code to return an xml file
the output can be somethign like this
http://technowand.com/xml/clients.xml

now you can parse this XML file to get the data
I used this to parse the xml file

[lua]function main()

– XMLParser.lua file should be in the folder
local parser = require( “XMLParser” )

– calling function to parse XML file
– returns a lua table
–from a local xml file
–local tab1 = parser.ParseXmlFile(“result.xml”)

– calling function to parse XML from website
local tab1 = parser.getXMLData(“http://technowand.com/xml/clients.xml”)

for i=1,3,1 do
for j=1,5,1 do
print(tab1[i][j])
end
end
end

main()[/lua]

the parser function

[lua] module(…, package.seeall)
– ======================================================================================
– XML Parser
– Renjith Viswanath
– Technowand
– ======================================================================================

http = require(“socket.http”)
ltn12 = require(“ltn12”)

local function parseColumns (xmlTree )
local row = {}
for i,xmlNode in pairs(xmlTree.ChildNodes) do
table.insert(row, xmlNode.Value)
end
return row
end

local function parseRows (xmlTree)
local tab = {}
for i,xmlNode in pairs(xmlTree.ChildNodes) do
local row = parseColumns (xmlNode);
table.insert(tab, row)
end
return tab
end
function ToXmlString(value)
value = string.gsub (value, “&”, “&”); – ‘&’ -> “&”
value = string.gsub (value, " “<”
value = string.gsub (value, “>”, “>”); – ‘>’ -> “>”
–value = string.gsub (value, “’”, “’”); – ‘’’ -> “’”
value = string.gsub (value, “”", “”"); – ‘"’ -> “”"
– replace non printable char -> " "
value = string.gsub(value, “([^%w%&%;%p%\t%])”,
function ©
return string.format("%X;", string.byte©)
–return string.format("%02X;", string.byte©)
–return string.format("%02d;", string.byte©)
end);
return value;
end

function FromXmlString(value)
value = string.gsub(value, “([%x]+)%;”,
function(h)
return string.char(tonumber(h,16))
end);
value = string.gsub(value, “([0-9]+)%;”,
function(h)
return string.char(tonumber(h,10))
end);
value = string.gsub (value, “”", “”");
value = string.gsub (value, “’”, “’”);
value = string.gsub (value, “>”, “>”);
value = string.gsub (value, “<”, " value = string.gsub (value, “&”, “&”);
return value;
end

function ParseArgs(s)
local arg = {}
string.gsub(s, “(%w+)=([”’])(.-)%2", function (w, _, a)
arg[w] = FromXmlString(a);
end)
return arg
end

function ParseXmlFile(xmlFileName)
local path = system.pathForFile( xmlFileName, system.ResourcesDirectory )
local hFile = io.open(path,“r”);
if hFile then
local xmlText=hFile:read("*a"); – read file content
io.close(hFile);
local tab = parseRows (ParseXmlText(xmlText));
return tab
else
return nil
end
end

– download the xml data
– params: url - url to use for downloading the xml data
function getXMLData(url)
local srcfile = “result.xml”
local path = system.pathForFile(srcfile, system.ResourcesDirectory)
local myFile = io.open(path,“w”)
local xmltext = http.request{
url = url,
sink = ltn12.sink.file(myFile),
}
io.close(myFile)
return ParseXmlFile(srcfile)
end [/lua] [import]uid: 71210 topic_id: 13273 reply_id: 48740[/import]

renvis1 · August 4, 2011, 7:28am

wow thats a great code… thanks robmiracle [import]uid: 71210 topic_id: 13273 reply_id: 48798[/import]

Omnigeek_Media · August 4, 2011, 7:45am

I wouldn’t go that far. If I were a real programmer I would have used the PEAR.db abstraction layer, so that the database behind the code was irrelevant instead of hard-coding mySQL calls. But I’m old school…
[import]uid: 19626 topic_id: 13273 reply_id: 48801[/import]

Omnigeek_Media · August 4, 2011, 7:47am

I would highly recommend having your .net or php scripts produce JSON instead of XML. while there are several XML parsers for Lua and Corona out there and Jonathan BeeBee just did an excellent blog post, JSON data is more concise and works much more like Lua tables. Corona has JSON encode and decode routines as part of its core today.

I whipped up a quick set of Lua/Corona frontend and PHP backend to do logins for an online game I’m tinkering with.

Here’s the Corona code to connect to the server, login and fetch results back.

[lua]local mime = require( “mime” )
local json = require(“json”)

local function networkListener( event )
if ( event.isError ) then
print( “Network error!”)
else
print ( "RESPONSE: " … event.response )
local data = json.decode(event.response)
responseText.text = data.result;
messageText.text = data.message;
sessionText.text = data.sessionid;
end
end
– testing purposes only
– in the real client, these would be input from the user using
– native text fields
local username = “myusername”
local password = “mypassword”

network.request( “http://myapi.mysite.com/mylogin.php?loginid=” … mime.b64(username) … “&password=” … mime.b64(password), “GET”, networkListener )

screen = display.newGroup();

responseText = display.newText("", 0, 0, native.systemFont, 20);
responseText.x = 100;
responseText.y = 40;
screen:insert(responseText);

messageText = display.newText("", 0, 0, native.systemFont, 20);
messageText.x = 100;
messageText.y = 70;
screen:insert(messageText);

sessionText = display.newText("", 0, 0, native.systemFont, 20);
sessionText.x = 100;
sessionText.y = 100;
screen:insert(sessionText);[/lua]

Now the PHP code on the other side:

<?php <br>//  
// Login.php for the game  
//  
  
//DB\_DSN, DB\_HOST, DB\_USER and DB\_PASS  
require("database\_constants.php");  
  
function getSessionId($link, $uid) {  
 // for now just return a random number  
 // for real, connect to the db, get an autoincrementfield  
 return(mt\_rand());  
}  
  
function make\_seed()  
{  
 list($usec, $sec) = explode(' ', microtime());  
 return (float) $sec + ((float) $usec \* 100000);  
}  
mt\_srand(make\_seed());  
  
// Connecting, selecting database  
// print("connecting to database\n");  
$link = mysql\_connect(DB\_HOST, DB\_USER, DB\_PASS)  
 or die('Could not connect: ' . mysql\_error());  
mysql\_select\_db(DB\_DSN) or die('Could not select database');  
  
if(isset($\_GET)) {  
 $loginid = base64\_decode($\_GET["loginid"]);  
 $password = base64\_decode($\_GET["password"]);  
 $query = 'SELECT \* FROM players WHERE playername="' . mysql\_real\_escape\_string($loginid) . '" or email="' . mysql\_real\_escape\_string($loginid) . '"';  
 $dbresult = mysql\_query($query, $link);  
 if (!$dbresult) {  
 $result = array();  
 $result["result"] = 401;  
 $result["message"] = "Invalid Login";  
 echo json\_encode($result);  
 mysql\_free\_result($dbresult);  
 exit;  
 }  
 $player = mysql\_fetch\_array($dbresult, MYSQL\_ASSOC);  
 if (strcmp($player["password"],md5($password)) == 0) {  
 // a real user!!!!!  
 $result = array();  
 $result["result"] = 200;  
 $result["message"] = "Success";  
 $result["sessionid"] = getSessionId($link, $player["id"]);  
 echo json\_encode($result);  
 }  
} else {  
 $result = array();  
 $result["result"] = 500;  
 $result["message"] = "Malformed Request";  
 echo json\_encode($result);  
}  
mysql\_free\_result($dbresult);  
exit;  
?\>

Notice how I build an associative array in PHP which is very similar to a Lua Table, and simply use the json_encode on the array and output the results to the connecting stream.

Then in the Corona code, I get that output which is in the event.response variable and run json.decode on it and I have a lovely table to use.

EDITED to remove some commented out code that might cause confusion. [import]uid: 19626 topic_id: 13273 reply_id: 48795[/import]

rxmarccall · August 4, 2011, 9:20am

Thank you so much for everyone’s example code and suggestions, as soon as i get home im going to dive into this, ill let you guys know how it goes. [import]uid: 19620 topic_id: 13273 reply_id: 48823[/import]

earl.d.moore · September 23, 2011, 11:41am

robmiracle - thanks for posting this. I have learned SO MUCH by studying the LUA and PHP elements of this code.

I’m working on the first module of my app, which will check to see if a username given by the user is already in the list of registered users, or if it’s “available” to be registered.

To practice the database call, as I’ve never done one before, I’m using your sample code. I set up a test MySQL database with a PLAYERS table and columns for playername, email, and password. I set up the LUA and PHP files just as you have them.

I’m able to get the code to execute to a point, but I’m stuck. Here is what is output to the terminal:

[blockcode]
http://notelearner.partofthemusic.com/login.php?loginid=ZWFybHk=&password=bXlwYXNz
RESPONSE:
Runtime error
Nil string: ‘’
stack traceback:
[C]: ?
[C]: in function ‘?’
?: in function <?:269>
(tail call): ?
/Users/Earl/Desktop/NL0.1/main.lua:9: in function
[/blockcode]

With some PRINT statements, I think my syntax is correct. I wonder if I’m missing how the MYSQL_ASSOC part of the $player array? Line 28. (Note: the XXX database connection strings below are just for illustration only - my actual code will have the “real” settings brought in through a REQUIRE .php).

<?php <br>  
define ("DB\_DSN", "xxx"); // dsn for database  
define ("DB\_HOST", "xxx"); // set database host  
define ("DB\_USER", "xxx"); // set database user  
define ("DB\_PASS", "xxx"); // set database password  
   
$link = mysql\_connect(DB\_HOST, DB\_USER, DB\_PASS)  
 or die('Could not connect: ' . mysql\_error());  
  
 mysql\_select\_db(DB\_DSN) or die('Could not select database');  
  
if(isset($\_GET)) {  
 $loginid = base64\_decode($\_GET["loginid"]);  
 $password = base64\_decode($\_GET["password"]);  
 $query = 'SELECT \* FROM players WHERE playername="' . mysql\_real\_escape\_string($loginid) . '" or email="' . mysql\_real\_escape\_string($loginid) . '"';  
  
 $dbresult = mysql\_query($query, $link);  
  
 if (!$dbresult) {  
 $result = array();  
 $result["result"] = 401;  
 $result["message"] = "Invalid Login";  
 echo json\_encode($result);  
 mysql\_free\_result($dbresult);  
 exit;  
 }  
 $player = mysql\_fetch\_array($dbresult, MYSQL\_ASSOC);  
 if (strcmp($player["password"],md5($password)) == 0) {  
 // a real user!!!!!  
 $result = array();  
 $result["result"] = 200;  
 $result["message"] = "Success";  
 echo json\_encode($result);  
 }  
} else {  
 $result = array();  
 $result["result"] = 500;  
 $result["message"] = "Malformed Request";  
 echo json\_encode($result);   
}  
mysql\_free\_result($dbresult);  
exit;  
?\>

My main.lua file, if it’s needed:

local mime = require( "mime" )  
local json = require("json")  
   
local function networkListener( event )  
 if ( event.isError ) then  
 print( "Network error!")  
 else  
 print ( "RESPONSE: " .. event.response)  
 local data = json.decode(event.response)  
 responseText.text = data.result;  
 messageText.text = data.message;  
 end  
end  
-- testing purposes only  
-- in the real client, these would be input from the user using   
-- native text fields  
local username = "early"  
local password = "mypass"  
loginURL = "http://notelearner.partofthemusic.com/login.php?loginid=" .. mime.b64(username) .. "&password=" .. mime.b64(password)  
print( loginURL )  
   
network.request( loginURL, "GET", networkListener )  
   
screen = display.newGroup();  
   
responseText = display.newText("", 0, 0, native.systemFont, 20);  
responseText.x = 100;  
responseText.y = 40;  
screen:insert(responseText);  
   
messageText = display.newText("", 0, 0, native.systemFont, 20);  
messageText.x = 100;  
messageText.y = 70;  
screen:insert(messageText);  
   
sessionText = display.newText("", 0, 0, native.systemFont, 20);  
sessionText.x = 100;  
sessionText.y = 100;  
screen:insert(sessionText);

Any advice? I’m just not sure if my problem is in main.lua, in the login.php code, or somewhere in the MySQL database.

Thanks! [import]uid: 81174 topic_id: 13273 reply_id: 57297[/import]

Omnigeek_Media · September 23, 2011, 8:36pm

Okay I think I see the problem.

You can take the URL that’s being printed out on the console:

http://notelearner.partofthemusic.com/login.php?loginid=ZWFybHk=&password=bXlwYXNz

and put that straight into the browser. You should get some JSON output and you are not. You are however getting a PHP warning (but its unrelated to why its not working…).

The warning is the last mysql_free_result(). It needs to move inside the password check if statement.

Anyway, following the logic, there is nothing output in the passwords mismatch. Since you’re just wanting to check the availability of the username, you don’t need to pass in or check the password.

But there should be code in there to return something if the password is wrong. Its a flaw in my sample code above.

Try:

[php]

<?php
// // Login.php for the game // //DB\_DSN, DB\_HOST, DB\_USER and DB\_PASS require("database\_constants.php"); function getSessionId($link, $uid) { // for now just return a random number // for real, connect to the db, get an autoincrementfield return(mt\_rand()); } function make\_seed() { list($usec, $sec) = explode(' ', microtime()); return (float) $sec + ((float) $usec \* 100000); } mt\_srand(make\_seed()); // Connecting, selecting database // print("connecting to database\n"); $link = mysql\_connect(DB\_HOST, DB\_USER, DB\_PASS) or die('Could not connect: ' . mysql\_error()); mysql\_select\_db(DB\_DSN) or die('Could not select database'); if(isset($\_GET)) { $loginid = base64\_decode($\_GET["loginid"]); $password = base64\_decode($\_GET["password"]); $query = 'SELECT \* FROM players WHERE playername="' . mysql\_real\_escape\_string($loginid) . '" or email="' . mysql\_real\_escape\_string($loginid) . '"'; $dbresult = mysql\_query($query, $link); if (!$dbresult) { $result = array(); $result["result"] = 401; $result["message"] = "Invalid Login"; echo json\_encode($result); mysql\_free\_result($dbresult); exit; } $player = mysql\_fetch\_array($dbresult, MYSQL\_ASSOC); if (strcmp($player["password"],md5($password)) == 0) { // a real user!!!!! $result = array(); $result["result"] = 200; $result["message"] = "Success"; $result["sessionid"] = getSessionId($link, $player["id"]); echo json\_encode($result); } else { $result = array(); $result["result"] = 403; $result["message"] = "Hackers Stink. Go away!" $result["sessionid" = 0; echo json\_encode($result); } mysql\_free\_result($dbresult); } else { $result = array(); $result["result"] = 500; $result["message"] = "Malformed Request"; echo json\_encode($result); } exit; ?\> [/php] [import]uid: 19626 topic\_id: 13273 reply\_id: 57338[/import]

earl.d.moore · September 23, 2011, 9:15pm

Yes!

Thank you, good and kind friend. That did the trick. A couple small edits @52 missing the ;, and @53, missing a closing bracket. Threw those in, and I have my very first interaction between the app, PHP, and MySQL.

A days worth of headache just melted away.

Many thanks!!!

Cheers,
-Earl [import]uid: 81174 topic_id: 13273 reply_id: 57340[/import]

earl.d.moore · September 23, 2011, 11:06pm

Say… sorry to trouble you again.

I have the main.lua, PHP, and MySQL files all working without any errors in the Terminal. However, no matter what I try, all I can produce is the condition for 403 / Hackers Stink.

I made sure the user id and password in main.lua and the MySQL database match perfectly, and that the table / column structure used in all 3 are consistent.

Any ideas?

Here is the URL being passed from Corona to PHP. I added a couple print statements after the php’s Decode to ensure the user id and password, once decoded, actually matched the one passed from PHP and the one that’s in the database.

http://notelearner.partofthemusic.com/php_sql_test.php?loginid=ZWFybHk&password=bXlwYXNz [import]uid: 81174 topic_id: 13273 reply_id: 57356[/import]

Omnigeek_Media · September 24, 2011, 8:53am

Well its going to be hard to diagnose this since I can’t see your database tables, but, I suspect your storing your password in clear text or you’re using MySQL’s password() function to encrypt it.

In other words, my SQL statement is:

‘INSERT INTO players (playername,password,email) VALUES ("’ . $playername . ‘", "’ . md5($password). ‘", "’, . $email . ‘");"’

My method does a one way encryption using md5 hashes. So when I create the account, I store the md5 encrypted password.

You need to in your PHP script, print out the value of the password after the base64_decode call and make sure it matches what you thought you were sending.

Then you need to print out md5(base64_decode($password)) to find out the string that your comparing to the database and print out the value of the password column in the database. These last two need to match.

[import]uid: 19626 topic_id: 13273 reply_id: 57401[/import]

Omnigeek_Media · September 24, 2011, 12:06pm

I would like to add a little bit of editorial dialog to this.

First my response codes I’m using in my PHP script were just made up to seem like HTTP request codes. In reality, I should either use my own system, or use the HTTP request codes. Since it is a “web service” model, it makes sense to actually implement HTTP request codes according to:

http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html

Secondly, for security, an “invalid login ID” and a “password mismatch” should return the exact same values. Above I return a different message on invalid ID and on invalid Password. This is fine while your building the resource to help you debug where its failing, but by all means before you put this in production, the person on the other end who should always assume to be untrusted should not know if they have a valid login ID or not. Once they know a login ID is valid, then its a matter of just trying passwords until they get in. By not letting them know which is wrong will slow down dictionary crack attempts.

Along these lines, let me reiterate something I just said:

You should always assume the person on the other end is trying to hack you

Do not trust any input from the user. Back in the day when I worked for an online gaming company and we were bringing in some content from a 3rd party developer, they wondered why the players were able to cheat so much. It was because their game logic was all in the software running on the players hardware and their server side was just a message passer to the other players.

A secure game will treat the software on end users hardware as just a big graphics engine and the server tells them what is real and what is not. All input from the user has to be treated as hostile and untrusted. As an example, lets say you’re building an RPG. You would not let the user software tell the server “I hit Player Y”. Instead the software would say “I want to hit player Y”. The server, code you are 100% in control over, will make the decision if the user actually hit Player Y and then take the responsibility to tell player Y they’ve been hit.

In particular with web services any input coming from GET or PUT requests must be assumed to contain SQL Incjection hacks (username=Fred\gDELETE * FROM players;) and all input must be scrubbed before its allowed near your database.

Security needs to be modeled in from the very beginning. Its much harder to come back and address all of your network layer code after the fact to add security. Start from the beginning and you will find your ability to secure your resources easier.
Good luck!
[import]uid: 19626 topic_id: 13273 reply_id: 57410[/import]

earl.d.moore · September 24, 2011, 8:04pm

Thanks again! I was able to get everything working.

I appreciate you giving me (a noob for certain) so much help!

I have my application talking to the MySQL database using mime64 encoded userIDs, and the PHP code traps for malformed requests.

I will take your advice on the security issues, as well.

Thanks again!
-Earl [import]uid: 81174 topic_id: 13273 reply_id: 57427[/import]