I have an ESP32 that currently writes highscores down to the EEPROM.
each entry consists of a name and the score.
I would like to replace this working solution with a SD card. I would have a txt file on the SD card containing the top 100 scores. a file would be like this:
Player1 97.3 ROWCHECKSUM
Some player name 92.3 ROWCHECKSUM
Player1 91.2 ROWCHECKSUM
Player1 90.3 ROWCHECKSUM
Player2 87.1 ROWCHECKSUM
Player4 guy 77.3 ROWCHECKSUM
Player2 64.3 ROWCHECKSUM
Player1 61.1 ROWCHECKSUM
Player7 51.1 ROWCHECKSUM
.
.
.
Player9 21.1 ROWCHECKSUM
the rowchecksum would make sure that nobody changed the file manually. Each time there is a new highscore, I'd have to update the txt file. what would be a good aproach to this? Deleting the file and regenerating it every time? it seems like a lot of data to write just to delete the last row and insert the new one in the proper place.
I solved this by writing the data to a sqlite database on the SD card. this way I can simply add rows, and then use a query to return the top 10 players.
UPDATE AS REQUESTED IN THE COMMENTS.
my table is called highscores and its structure is:
has is sha256(name + score + salt) and ensures that nobody modified either the name or the score.
#include <WiFi.h>
#include <WebServer.h>
#include <ArduinoJson.h>
#include <sqlite3.h>
#include <SPI.h>
#include <FS.h>
#include "SD.h"
#include "mbedtls/md.h"
#include "highscores.h"
[...]
WebServer server(80);
int rc;
sqlite3_stmt *res;
int rec_count = 0;
const char *tail;
sqlite3 *db1;
char *zErrMsg = 0;
File myFile;
[...]
void setup(void) {
[...]
server.on("/highscores", handleHighscores);
server.on("/gethighscores", handleGetHighscores);
sqlite3_initialize();
if (openDb("/sd/mydb.db3", &db1))
return;
[...]
}
void loop(void) {
server.handleClient();
if (progStep == SAVEHIGHSCORE)
{
String tmp = shooter + rankingAvgScore + "fajlo5a5%9k";
const char *payload = tmp.c_str();
byte shaResult[32];
mbedtls_md_context_t ctx;
mbedtls_md_type_t md_type = MBEDTLS_MD_SHA256;
const size_t payloadLength = strlen(payload);
mbedtls_md_init(&ctx);
mbedtls_md_setup(&ctx, mbedtls_md_info_from_type(md_type), 0);
mbedtls_md_starts(&ctx);
mbedtls_md_update(&ctx, (const unsigned char *) payload, payloadLength);
mbedtls_md_finish(&ctx, shaResult);
mbedtls_md_free(&ctx);
String shastr;
for (int i = 0; i < sizeof(shaResult); i++) {
char str[3];
sprintf(str, "%02x", (int)shaResult[i]);
shastr = shastr + str;
}
String sql = "insert into highscores ('name','score','hash') values ('";
sql += shooter;
sql += "','";
sql += rankingAvgScore;
sql += "','";
sql += shastr;
sql += "')";
rc = db_exec(db1, sql.c_str());
sql = "Select * from highscores order by score desc limit 10";
rc = sqlite3_prepare_v2(db1, sql.c_str(), 1000, &res, &tail);
if (rc != SQLITE_OK) {
String resp = "Failed to fetch data: ";
resp += sqlite3_errmsg(db1);
Serial.println(resp.c_str());
return;
}
rec_count = 0;
while (sqlite3_step(res) == SQLITE_ROW) {
highscoreNames[rec_count] = (const char *) sqlite3_column_text(res, 1);
highscoreScores[rec_count] = sqlite3_column_double(res, 2);
highscoreHash[rec_count] = (const char *) sqlite3_column_text(res, 3);
rec_count++;
}
sqlite3_finalize(res);
progStep = END;
}
}
this sends the webpage over on request.
void handleHighscores() {
Serial.println("GET /");
server.send(200, "text/html", htmlhighscores);
}
this reads the values from the database when requested by the webpage..
void handleGetHighscores()
{
/* read highscores from sqlite db*/
String sql = "Select * from highscores order by score desc limit 10";
rc = sqlite3_prepare_v2(db1, sql.c_str(), 1000, &res, &tail);
if (rc != SQLITE_OK) {
String resp = "Failed to fetch data: ";
resp += sqlite3_errmsg(db1);
Serial.println(resp.c_str());
return;
}
rec_count = 0;
while (sqlite3_step(res) == SQLITE_ROW) {
highscoreNames[rec_count] = (const char *) sqlite3_column_text(res, 1);
highscoreScores[rec_count] = sqlite3_column_double(res, 2);
highscoreHash[rec_count] = (const char *) sqlite3_column_text(res, 3);
rec_count++;
}
sqlite3_finalize(res);
/* end read highschores from sqlite db*/
StaticJsonDocument<2000> doc;
JsonObject root = doc.to<JsonObject>();
JsonObject values = root["highscores"].to<JsonObject>();
//String highscoreNames[10];
//double highscoreScores[10];
for (int i = 0; i <= 9; i++) {
String tmp = highscoreNames[i] + highscoreScores[i] + "fajlo5a5%9k";
const char *payload = tmp.c_str();
Serial.println(tmp);
byte shaResult[32];
mbedtls_md_context_t ctx;
mbedtls_md_type_t md_type = MBEDTLS_MD_SHA256;
const size_t payloadLength = strlen(payload);
mbedtls_md_init(&ctx);
mbedtls_md_setup(&ctx, mbedtls_md_info_from_type(md_type), 0);
mbedtls_md_starts(&ctx);
mbedtls_md_update(&ctx, (const unsigned char *) payload, payloadLength);
mbedtls_md_finish(&ctx, shaResult);
mbedtls_md_free(&ctx);
String shastr;
for (int i = 0; i < sizeof(shaResult); i++) {
char str[3];
sprintf(str, "%02x", (int)shaResult[i]);
shastr = shastr + str;
}
String tmp2 = "name" + (String)(i+1);
values[tmp2] = highscoreNames[i];
tmp2 = "score" + (String)(i+1);
values[tmp2] = highscoreScores[i];
Serial.println(highscoreHash[i]);
Serial.println(shastr);
if ((highscoreHash[i] != shastr) && (highscoreScores[i] > 0))
{
values[tmp2] = (String)highscoreScores[i] + (String)" Hacked.. :D";
}
}
Serial.println("Json SENT");
Serial.println(highscoreScores[0]);
String json;
serializeJsonPretty(doc, json);
//Serial.println(json);
server.send(200, "text/plane", json);
}
highschores.h file
const char htmlhighscores[] PROGMEM = R"=====(
<html>
<style>
div.divshooter{
text-align: center;
font-size: 18px;
font-weight: bold;
}
div.minimalistBlack {
border: 0px solid #000000;
width: 100%;
text-align: left;
border-collapse: collapse;
}
.divTable.minimalistBlack .divTableCell, .divTable.minimalistBlack .divTableHead {
border: 1px solid #000000;
padding: 5px 4px;
}
.divTable.minimalistBlack .divTableBody .divTableCell {
font-size: 13px;
}
.divTable.minimalistBlack .divTableHeading {
background: #CFCFCF;
background: -moz-linear-gradient(top, #dbdbdb 0%, #d3d3d3 66%, #CFCFCF 100%);
background: -webkit-linear-gradient(top, #dbdbdb 0%, #d3d3d3 66%, #CFCFCF 100%);
background: linear-gradient(to bottom, #dbdbdb 0%, #d3d3d3 66%, #CFCFCF 100%);
border-bottom: 3px solid #000000;
}
.divTable.minimalistBlack .divTableHeading .divTableHead {
font-size: 15px;
font-weight: bold;
color: #000000;
text-align: left;
}
.minimalistBlack .tableFootStyle {
font-size: 14px;
font-weight: bold;
color: #000000;
border-top: 3px solid #000000;
}
.minimalistBlack .tableFootStyle {
font-size: 14px;
}
/* DivTable.com */
.divTable{ display: table; }
.divTableRow { display: table-row; }
.divTableHeading { display: table-header-group;}
.divTableCell, .divTableHead { display: table-cell;}
.divTableHeading { display: table-header-group;}
.divTableFoot { display: table-footer-group;}
.divTableBody { display: table-row-group;}
</style>
<script>
function getData() {
var xhttp = new XMLHttpRequest();
xhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
var jsonval = JSON.parse( this.responseText );
document.getElementById("name1").innerHTML = jsonval.highscores["name1"];
document.getElementById("score1").innerHTML = jsonval.highscores["score1"];
document.getElementById("name2").innerHTML = jsonval.highscores["name2"];
document.getElementById("score2").innerHTML = jsonval.highscores["score2"];
document.getElementById("name3").innerHTML = jsonval.highscores["name3"];
document.getElementById("score3").innerHTML = jsonval.highscores["score3"];
document.getElementById("name4").innerHTML = jsonval.highscores["name4"];
document.getElementById("score4").innerHTML = jsonval.highscores["score4"];
document.getElementById("name5").innerHTML = jsonval.highscores["name5"];
document.getElementById("score5").innerHTML = jsonval.highscores["score5"];
document.getElementById("name6").innerHTML = jsonval.highscores["name6"];
document.getElementById("score6").innerHTML = jsonval.highscores["score6"];
document.getElementById("name7").innerHTML = jsonval.highscores["name7"];
document.getElementById("score7").innerHTML = jsonval.highscores["score7"];
document.getElementById("name8").innerHTML = jsonval.highscores["name8"];
document.getElementById("score8").innerHTML = jsonval.highscores["score8"];
document.getElementById("name9").innerHTML = jsonval.highscores["name9"];
document.getElementById("score9").innerHTML = jsonval.highscores["score9"];
document.getElementById("name10").innerHTML = jsonval.highscores["name10"];
document.getElementById("score10").innerHTML = jsonval.highscores["score10"];
console.log(this.responseText);
}
};
xhttp.open("GET", "gethighscores", true);
xhttp.send();
}
</script>
<body onload="getData();">
<h2>Highscores</h2>
<div class="divTable minimalistBlack">
<div class="divTableHeading">
<div class="divTableRow">
<div class="divTableHead">Rank</div>
<div class="divTableHead">Name</div>
<div class="divTableHead">Score</div>
</div>
</div>
<div class="divTableBody">
<div class = "divTableRow">
<div class = "divTableCell"><b><u>1</u></b></div>
<div class = "divTableCell" id="name1"></div>
<div class = "divTableCell" id="score1"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell"><b>2</b></div>
<div class = "divTableCell" id="name2"></div>
<div class = "divTableCell" id="score2"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell"><b>3</b></div>
<div class = "divTableCell" id="name3"></div>
<div class = "divTableCell" id="score3"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell">4</div>
<div class = "divTableCell" id="name4"></div>
<div class = "divTableCell" id="score4"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell">5</div>
<div class = "divTableCell" id="name5"></div>
<div class = "divTableCell" id="score5"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell">6</div>
<div class = "divTableCell" id="name6"></div>
<div class = "divTableCell" id="score6"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell">7</div>
<div class = "divTableCell" id="name7"></div>
<div class = "divTableCell" id="score7"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell">8</div>
<div class = "divTableCell" id="name8"></div>
<div class = "divTableCell" id="score8"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell">9</div>
<div class = "divTableCell" id="name9"></div>
<div class = "divTableCell" id="score9"></div>
</div>
<div class = "divTableRow">
<div class = "divTableCell">10</div>
<div class = "divTableCell" id="name10"></div>
<div class = "divTableCell" id="score10"></div>
</div>
</div>
</div>
</div>
</br></br>
<form action="/">
<center> <input type="submit" value="Back to start" /></center>
</form>
</body>
</html>)=====";