Servers automatically renaming Plugin Database

angriff · May 15th 2018

Quote from Minotorious

ok sure will test it, I don't think i have even been on your server so I will join this evening (central europe time) and let you know

PatrickBronke visited and I sent him the databases. He is looking into it. The routine is not updating the new database and the old one writes a new entry everytime you log in.. though the routine keeps all the entrees the same as far as money math is concerned so it is transparent. Will make way too big a database though. The new routine is not updating the database at all.

yahwho · May 15th 2018

Ah well just a thought, I was always taught to use escape backslashes.

An odd problem Indeed.

( Edits due to butter fingers )

angriff · May 15th 2018

Quote from yahwho
Code
getSQLiteConnection(getPath() + "/database/" + getDescription("name") + "-" + this.world.getName() + ".db");
I would write the path:
Code
getSQLiteConnection(this.getPath() + "\\database\\iconomy.db");
The first one actually creates a path similar to (on Windows at least) C:\risingworld\path/database/description-name.db when executed on the JVM.

I'm only surmising that this may be a cause. . .

There really is no need for a unique database name for each server plugin it's complicating things for no reason.

KISS mentality, Keep It Sweet (n) Simple.
Display More

Quote from yahwho

Ah well just a thought, I was always taught to use escape backslashes.

An odd program indeed.

I am going to send him your comments because that might be another issue. The database issue is not solved with the old one just the entries. I bet you could manually enter the database information and it would start working but new people would not have any money and it would be a pain to maintain.

yahwho · May 15th 2018

Sql should be giving you errors if there are problems?

angriff · May 15th 2018

Quote from yahwho

Sql should be giving you errors if there are problems?

Nope no errors just no money and the database remains blank.

yahwho · May 15th 2018

Maybe the queries are wrong? Using update statements without inserts? Sorry not going to be much help here.

Minotorious · May 15th 2018

Quote from yahwho

Ah well just a thought, I was always taught to use escape backslashes.

I think front slashes already take account of this thus you don't need the escape character on backslashes. i.e. / and \\ are equivalent characters in a file path string.

Quote from yahwho

Sql should be giving you errors if there are problems?

This is true if the queries happen and are failing there should be SQL exceptions thrown, and if caught by the plugin itself there should be an error message appearing if the plugin creator has coded one in.

Quote from yahwho

Maybe the queries are wrong? Using update statements without inserts? Sorry not going to be much help here.

I will have a look at the decompiled code again to check but no way to know for certain without the full source code

Minotorious · May 15th 2018

Second post in a row but I just checked the decompiled code again and I think I see the problem.

In the SQL statements it is recommended to use the keyword LIKE instead of an equals = sign because in some systems that causes problems, also in the SQL statements again there are single quotes ` instead of '. I would recommend removing the single quotes all together since many systems confuse them and don't recognise table names if they are in single quotes.

Something else I noticed is that the iConomy plugin is not using prepared statements for its SQL statements, that doesn't cause problems but it is not a good practice since a player could use an SQL Injection attack to alter anything they wanted in the database.

angriff · May 16th 2018

Now when a new player logs on this error showed up. The routine still works for those that were in the database prior to the newer version.

It does not seem to recognize short single player UIDs versus Steam but this is an observation as two players one steam and one not both in the database act differently. The Steam UID functions normally and the player receives money and notification of money being transferred. The non-steam player does not get a report but the sending person gets a report that was sent.

Minotorious · May 16th 2018

Quote from angriff

It does not seem to recognize short single player UIDs versus Steam

both steam and standalone players have a type long UID. Unless you mean something else by that?

As I said the plugin probably has bugs in it as it is still in development, I would recommend only using it for testing purposes and to provide feedback to the developer, it is clearly not ready for players to play with.

angriff · May 16th 2018

I have already had the developer over to my server and tested it with him. I have sent him config and databases. He feels he knows part of the problem so he is working on it. I forwarded this thread to him so that he might have the insight brought out by you two also.

So players can attack the SQL database and alter the databases? That is not good. Hope Patrick is listening.

yahwho · May 16th 2018

Yes, its called a SQL injection attack.

Link to some info:

https://www.acunetix.com/websitesecurity/sql-injection/

It all depends on how the application is written as to whether there is a vulnerability or not.

On a basic level you can execute extra commands. Like drop table or add extra table info / modifications.

Machete · May 16th 2018

I can not resist adding my mustard!
1. Do not use hardcoded path name separators! Not "/" and not "\\"!

Please make use of the getSeparator()-function:

Java

String SEP = FileSystems.getDefault().getSeparator();
String filePath = plugin.getPath() + SEP + "data" + SEP + "mydatabasefile.db";

2. Avoiding SQL injection attacks: always use prepared statements and then everything is in the paint again!

Prepared Statement Example: (excerpt from abm rancher update routine)

Java

try(PreparedStatement prep = connection.prepareStatement(
      "UPDATE rancher SET playerName=?, offspringCounter=?, rewardCounter=? WHERE PlayerId=?;"))
    {
      connection.setAutoCommit(false);
      for(Rancher rancher : list)
      {
        prep.setString(1, rancher.getPlayerName());
        prep.setInt(2, rancher.getOffspringCounter());
        prep.setInt(3, rancher.getRewardCounter());
        prep.setInt(4, rancher.getPlayerId());
        prep.addBatch();
      }
      prep.executeBatch();
      connection.commit();
    }

Display More

Happy coding!

Minotorious · May 16th 2018

Quote from Machete
I can not resist adding my mustard!
1. Do not use hardcoded path name separators! Not "/" and not "\\"!

Please make use of the getSeparator()-function:
Java
String SEP = FileSystems.getDefault().getSeparator();
String filePath = plugin.getPath() + SEP + "data" + SEP + "mydatabasefile.db";

As far as I am aware you only need to use the .getSeparator() function if you are using the code on a JVM with multiple partitions each with its own filesystem. If you are using a single JVM then a / is translated to the filesystem's default. I don't think I am wrong but am I?

Machete · May 16th 2018

You are not wrong! It is normalized, f.e. when using java.io new File("a/b/c").
But on the other hand there are cases where you pass a path with no normalization (native libraries?) when the target system is unknown.
I simply do not want to care about this.

Minotorious · May 16th 2018

Quote from Machete

I simply do not want to care about this.

fair point

yahwho · May 16th 2018

Lol! Or we could even use path . . .

Machete · May 17th 2018

...now we are talking!

angriff · May 17th 2018

Well I dont expect any further updates until the next patch comes out. Seems everyone is waiting till they have checked it out with the new stuff running first.

angriff · May 18th 2018

Quote from Minotorious

both steam and standalone players have a type long UID. Unless you mean something else by that?
As I said the plugin probably has bugs in it as it is still in development, I would recommend only using it for testing purposes and to provide feedback to the developer, it is clearly not ready for players to play with.

No actually this routine sees people with the extended Steam UID but the shorter individual user UID is not recognized. Both are in the database. I assume that this is just a problem with this routine but I wanted to ask.

Also can the shorter personal UID cause issues with other routines such as permissions? Seems I have a user that says he can fly when it it is clearly turned off in the permissions groups until someone is promoted to specialized group of dedicated builders. No other player has informed me of this.

Participate now!