Arduino - write highscores to txt file on SD card

Solution 1:

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:

• name: String
• score: String
• hash: String

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>)=====";