[b][size=large][u]Table of Contents[/u][/size][/b]
[list=1]
[*]Introduction
[*]Database Setup
[*]Connect?
[*]Authentication Script
[*]Conclusion
[/list]
[hr]
[b][size=large][u]Introduction[/u][/size][/b]
[list]
Just imagine, a database that has 100% uptime, is not open to SQLi, and is incredibly fast? Sounds too good to be true, but it's not!
This was an interesting topic that I looked into a few months ago when trying to create a secure authentication system. I figured, why not host a database on an already secure host, then not allow anyone to modify it unless they are logged into my google account!? Thus, this idea was created and used by myself in several authentication systems for a few months. After a while, I found a faster and more secure method that allows you to do more within the scripts, but this overall works just fine. So I figured, why not share it! Nobody else does this anyways! :D
I'm sure you've all used Google Documents by now, so I'm not going to go into a lot of detail about what it is. I'll leave it at, "You can create a spreadsheet".
In this tutorial, I'm going to show you how to create a [b]BASIC[/b] authentication system. I've also divided it into sections and sub-sections so it's easier to navigate between topics.
[/list]
[hr]
[b][size=large][u]Database Setup[/u][/size][/b]
[list]
First things first, we have to create a spread sheet within our google documents account. You can do this by going to https://drive.google.com, logging in with your google account, then creating a spread sheet. It's VERY simple, so I'm not going to go into detail.
You should end up at a screen like this:
[spoiler=Image][img]http://dl.dropbox.com/u/7870266/Uploads/2013-01-19_23-03-09.png[/img][/spoiler]
I'm going to delete every column and row in my spreadsheet, then, for this example, create 3 columns and 6 rows. You're going to want to delete any unused columns/rows to reduce the response size, thus increasing speed. You should end up with something like this:
[spoiler=Image][img]http://dl.dropbox.com/u/7870266/Uploads/2013-01-19_23-06-17.png[/img][/spoiler]
Now, this step is not needed, but I personally did it in the past so I can keep track of what column is what.
Lets create some column headers so we know what we're doing. In this tutorial, I'm going to name them "ID","USER", and "PASS". Put these in the very top row.
[spoiler=Image][img]http://dl.dropbox.com/u/7870266/Uploads/2013-01-19_23-08-31.png[/img][/spoiler]
Just fill in the first column since this is an example. You can add more users if you want, but I'm only going to add one for this example.
This is what it should look like:
[spoiler=Image][img]http://dl.dropbox.com/u/7870266/Uploads/2013-01-20_00-09-34.png[/img][/spoiler]
Last step, we need to make our document public so we can access it from our script.
First, click "File", then select "Share".
I'm sure you can figure out how to change the access level from Private to "Anyone with the link".
Next, we need to publish our file to the web.
You can do this by clicking "File", then selection "Publish To Web".
Make sure to select "Sheet1" and check "Automatically respublish when changes are made. Then click "Start Publishing".
After you click the "Start Publishing" button, we need the JSON link, but that's not documented anywhere, so we're going to have to get it ourselves.
Under "Get a link to the published data", select "RSS", then "Cells", then copy the link.
For example, my URL is [b]https://spreadsheets.google.com/feeds/cells/0ArqzqB_MMK5udGJXUWpwdlZyQmpQeU10QnJ2UlJfb3c/od6/public/basic?alt=rss[/b]
To format it to json, we simply change the [b]alt=rss[/b] to [b]alt=json[/b].
And now we have our json feed url!
[/list]
[hr]
[b][size=large][u]Connect?[/u][/size][/b]
[list]
Well I'm sure you're wondering might we wondering how we get the data. Well it's very simple! We are just going to use a script that I used in the past.
Make sure you have file_get_contents enabled on your host otherwise this will not work. You could always just use cURL, but I don't like cURL. ^.^
[b]get_spreadsheet_data.php[/b]
[php]<?php
function fetchUserList($url,$cols) {
// Converts the column integer input to a decimal for future use.
$cols = number_format($cols,1);
// Get our information, then parse it.
$jsonData = json_decode(file_get_contents($url),true);
// Store all entries in the $entries array.
$entries = $jsonData['feed']['entry'];;
// Create an array for future use to store the final data in.
$info = array();
$j=-1; // So we can have a 2D array starting at 0.
// Loop through each of the elements in the $entries array so we can get the real data.
for($i=0;$i<count($entries);$i++) {
// If (Current Entry) % 3 is not 0, then place the entry into the current array
if(fmod($i,$cols)!=0) {
$info[$j][$i] = $entries[$i]['content']['$t'];
} else {
// Otherwise, increment J and input the data into the next array
$j++;
$info[$j][$i] = $entries[$i]['content']['$t'];
}
}
// Return our array of information!
return $info;
}
?>[/php]
[/list]
[hr]
[b][size=large][u]Authentication Script[/u][/size][/b]
[list]
Obviously you're going to need to change it to fit your needs, but here's the basic authentication script:
[b]auth.php[/b]
[php]
<?php
require_once("get_spreadsheet_data.php");
// Make sure to change the URL to your own.
// fetchUserList($url,$numberOfColumns);
$users = fetchUserList("https://spreadsheets.google.com/feeds/cells/0ArqzqB_MMK5udGJXUWpwdlZyQmpQeU10QnJ2UlJfb3c/od6/public/basic?alt=json",3);
$username = $_GET['username'];
$password = $_GET['password'];
for($i=1;$i<count($users);$i++) {
$database_id = $users[$i][0];
$database_username = $users[$i][1];
$database_password = $users[$i][2];
if($database_username == $username) {
if($database_password == $password) {
echo("Successful login!");
} else {
echo("Incorrect password!");
}
} else {
echo("Incorrect username!");
}
}
?>
[/php]
[/list]
[hr]
[b][size=large][u]Conclusion[/u][/size][/b]
[list]
And there you have it! A database hosted on google that's not open to SQLi and has 100% uptime!
Also, I should also mention that it is [b]NOT[/b] the best way to use this.
There are ways using other libraries that allow you to have full control over the database. This means reading and writing to the database instead of just reading.
Hint: Zend (Very horrible library, but that's the only one that I know of that will work).
If I need to further explain anything, just let me know and I'll do my best.
[/list]
