Alternative method to provide dynamic arguments into sql query #28

szumielxd · 2024-07-17T23:16:51Z

szumielxd commented

2024-07-17 23:16:51 +00:00

Sometimes you want to create a query with dynamic content. For now, you're limited to using unsafe keyword, but as name states it isn't safe.
So... Here's simple idea to provide query arguments directly as object array. Maybe not 100% foolproof, but in my opinion much better option than self-made sanitization of input.

Some examples:

Select specific column

execute unsafe "SELECT uuid, name, %{_columnName}% FROM stats WHERE displayname = ? LIMIT ?" in {-sql} with arguments {_displayName} and {_count} and store result in {_result::*}

Bulk update

loop all players:
    add "(?, ?, ?)" to {_valuesString::*}
    add uuid of player to {_values::*}
    add name of player to {_values::*}
    add rounded unix timestamp of now to {_values::*}
if size of {_valuesString::*} is bigger than 0:
    execute unsafe "INSERT INTO players (uuid,name,lastjoin) VALUES %join {_valuesString::*} by ", "% ON DUPLICATE KEY UPDATE name=VALUES(name), lastjoin=VALUES(lastjoin)" in {-sql} with arguments {_values::*}

Sometimes you want to create a query with dynamic content. For now, you're limited to using `unsafe` keyword, but as name states it isn't safe. So... Here's simple idea to provide query arguments directly as object array. Maybe not 100% foolproof, but in my opinion much better option than self-made sanitization of input. Some examples: - Select specific column ``` execute unsafe "SELECT uuid, name, %{_columnName}% FROM stats WHERE displayname = ? LIMIT ?" in {-sql} with arguments {_displayName} and {_count} and store result in {_result::*} ``` - Bulk update ``` loop all players: add "(?, ?, ?)" to {_valuesString::*} add uuid of player to {_values::*} add name of player to {_values::*} add rounded unix timestamp of now to {_values::*} if size of {_valuesString::*} is bigger than 0: execute unsafe "INSERT INTO players (uuid,name,lastjoin) VALUES %join {_valuesString::*} by ", "% ON DUPLICATE KEY UPDATE name=VALUES(name), lastjoin=VALUES(lastjoin)" in {-sql} with arguments {_values::*} ```

szumielxd added 3 commits 2024-07-17 23:16:52 +00:00

Added missing Skript repository 4de7dff0ad

Ignore Eclipse internal files 52d111dbfa

Added option to provide PreparedStatement-like query argument binding 110f3982ad

szumielxd added 5 commits 2025-01-30 01:48:14 +00:00

Removed some duplicated code and improved readability 2dc1f70f4f

Fixed missing 'string' field in Skript 2.8 41881dbb7d

Code cleanup 0670f88a4e

Added additional arguments info in prepared statement warning 99be47d4d3

Merge pull request 'cleaning' (#1 ) from cleaning into master 39cbb6b397

Reviewed-on: szumielxd/skript-db#1

Govindas commented

2025-02-09 09:35:39 +00:00

hey, sorry for noticing this so late, I like the idea, once I find time to test it, I will merge if it works ok.

Govindas commented

2025-02-09 10:08:45 +00:00

"with arguments" seems to be only working if the arguments are variables, not if submitting the value directly, but other than that it's working :)

 Stack trace:
 ch.njol.skript.SkriptAPIException: UnparsedLiterals must be converted before use
     at ch.njol.skript.lang.UnparsedLiteral.invalidAccessException(UnparsedLiteral.java:272)
     at ch.njol.skript.lang.UnparsedLiteral.getAll(UnparsedLiteral.java:282)
     at ch.njol.skript.lang.ExpressionList.getAll(ExpressionList.java:102)
     at ch.njol.skript.lang.ExpressionList.getArray(ExpressionList.java:92)
     at com.btk5h.skriptdb.skript.EffExecuteStatement.parseQuery(EffExecuteStatement.java:143)
     at com.btk5h.skriptdb.skript.EffExecuteStatement.execute(EffExecuteStatement.java:77)
     at com.btk5h.skriptdb.skript.EffExecuteStatement.walk(EffExecuteStatement.java:137)
     at ch.njol.skript.lang.TriggerItem.walk(TriggerItem.java:89)
     at ch.njol.skript.command.Commands.handleEffectCommand(Commands.java:290)
     at ch.njol.skript.command.Commands$3$1.call(Commands.java:220)
     at ch.njol.skript.command.Commands$3$1.call(Commands.java:217)
     at org.bukkit.craftbukkit.v1_16_R3.scheduler.CraftFuture.run(CraftFuture.java:88)
     at org.bukkit.craftbukkit.v1_16_R3.scheduler.CraftScheduler.mainThreadHeartbeat(CraftScheduler.java:468)
     at net.minecraft.server.v1_16_R3.MinecraftServer.b(MinecraftServer.java:1503)
     at net.minecraft.server.v1_16_R3.DedicatedServer.b(DedicatedServer.java:449)
     at net.minecraft.server.v1_16_R3.MinecraftServer.a(MinecraftServer.java:1416)
     at net.minecraft.server.v1_16_R3.MinecraftServer.w(MinecraftServer.java:1142)
     at net.minecraft.server.v1_16_R3.MinecraftServer.lambda$a$0(MinecraftServer.java:293)
     at java.base/java.lang.Thread.run(Thread.java:840)
 
 Version Information:
   Skript: 2.6.4
     Flavor: skriptlang-github
     Date: 17:14:25.041218900
   Bukkit: 1.16.5-R0.1-SNAPSHOT
   Minecraft: 1.16.5
   Java: 17.0.14 (OpenJDK 64-Bit Server VM 17.0.14+1-void-r1)
   OS: Linux amd64 6.12.6_3
 
 Server platform: Paper
 
 Current node: null
 Current item: execute unsafe "UPDATE userprofile SET emeralds = ? where UUID = ?" in {-sql} (as com.zaxxer.hikari.HikariDataSource)
 
 Thread: Server thread
 
 Language: english
 Link parse mode: LENIENT
 
 End of Error.

"with arguments" seems to be only working if the arguments are variables, not if submitting the value directly, but other than that it's working :) ``` Stack trace: ch.njol.skript.SkriptAPIException: UnparsedLiterals must be converted before use at ch.njol.skript.lang.UnparsedLiteral.invalidAccessException(UnparsedLiteral.java:272) at ch.njol.skript.lang.UnparsedLiteral.getAll(UnparsedLiteral.java:282) at ch.njol.skript.lang.ExpressionList.getAll(ExpressionList.java:102) at ch.njol.skript.lang.ExpressionList.getArray(ExpressionList.java:92) at com.btk5h.skriptdb.skript.EffExecuteStatement.parseQuery(EffExecuteStatement.java:143) at com.btk5h.skriptdb.skript.EffExecuteStatement.execute(EffExecuteStatement.java:77) at com.btk5h.skriptdb.skript.EffExecuteStatement.walk(EffExecuteStatement.java:137) at ch.njol.skript.lang.TriggerItem.walk(TriggerItem.java:89) at ch.njol.skript.command.Commands.handleEffectCommand(Commands.java:290) at ch.njol.skript.command.Commands$3$1.call(Commands.java:220) at ch.njol.skript.command.Commands$3$1.call(Commands.java:217) at org.bukkit.craftbukkit.v1_16_R3.scheduler.CraftFuture.run(CraftFuture.java:88) at org.bukkit.craftbukkit.v1_16_R3.scheduler.CraftScheduler.mainThreadHeartbeat(CraftScheduler.java:468) at net.minecraft.server.v1_16_R3.MinecraftServer.b(MinecraftServer.java:1503) at net.minecraft.server.v1_16_R3.DedicatedServer.b(DedicatedServer.java:449) at net.minecraft.server.v1_16_R3.MinecraftServer.a(MinecraftServer.java:1416) at net.minecraft.server.v1_16_R3.MinecraftServer.w(MinecraftServer.java:1142) at net.minecraft.server.v1_16_R3.MinecraftServer.lambda$a$0(MinecraftServer.java:293) at java.base/java.lang.Thread.run(Thread.java:840) Version Information: Skript: 2.6.4 Flavor: skriptlang-github Date: 17:14:25.041218900 Bukkit: 1.16.5-R0.1-SNAPSHOT Minecraft: 1.16.5 Java: 17.0.14 (OpenJDK 64-Bit Server VM 17.0.14+1-void-r1) OS: Linux amd64 6.12.6_3 Server platform: Paper Current node: null Current item: execute unsafe "UPDATE userprofile SET emeralds = ? where UUID = ?" in {-sql} (as com.zaxxer.hikari.HikariDataSource) Thread: Server thread Language: english Link parse mode: LENIENT End of Error. ```

Govindas commented

2025-02-09 10:14:37 +00:00

another interesting issue: when using UUID datatype in the database (PostgreSQL) and putting variable containing uuid of player in the arguments:
execute unsafe "UPDATE userprofile SET emeralds = ? where UUID = ?" in {-sql} with arguments {-test::*}
it gives error:

ERROR: operator does not exist: uuid = character varying   Hint: No operator matches the given name and argument types. You might need to add explicit type casts.   Position: 49

but it can be fixed by changing ? to UUID(?) so it's explicitly casted as UUID.

execute unsafe "UPDATE userprofile SET emeralds = ? where UUID = UUID(?)" in {-sql} with arguments {-test::*}

but this is not an issue if using '%uuid of player%' in the query directly, maybe this is how it should be, given that it is trying to avoid SQL query injection.

I will test a bit more to be sure all is right, but these issues are not critical and given that the pull request fixes another important issue, I think I will release with this.

another interesting issue: when using UUID datatype in the database (PostgreSQL) and putting variable containing uuid of player in the arguments: execute unsafe "UPDATE userprofile SET emeralds = ? where UUID = ?" in {-sql} with arguments {-test::*} it gives error: ``` ERROR: operator does not exist: uuid = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 49 ``` but it can be fixed by changing ? to UUID(?) so it's explicitly casted as UUID. ``` execute unsafe "UPDATE userprofile SET emeralds = ? where UUID = UUID(?)" in {-sql} with arguments {-test::*} ``` but this is not an issue if using '%uuid of player%' in the query directly, maybe this is how it should be, given that it is trying to avoid SQL query injection. I will test a bit more to be sure all is right, but these issues are not critical and given that the pull request fixes another important issue, I think I will release with this.

Govindas merged commit 5a81846081 into master

2025-02-09 10:43:50 +00:00

Govindas referenced this issue from a commit

2025-02-09 10:43:50 +00:00

Merge pull request 'Alternative method to provide dynamic arguments into sql query' (#28) from szumielxd/skript-db:master into master

Sign in to join this conversation.

No reviewers