Escaping problem
There is a problem with values escaping, making it impossible for now to input many columns-values with colons in strings.
As it is important to make it work before going further, a more temporary solution was applied. Payload must manually assign a number of values to columns, making it harder to insert many columns at once, as it will need more code.
There is ( if not deleted yet, still should be in the link below ) the class Escape in bot/packages/database.py that was used to ( well, tried to ) escape characters that potentially could cause problems, like ' or ". As with all this class working, there would not be this issue, escaping didn't work.
Code snippet that was used to execute SQL query for insert:
cur.execute( # - Build and execute SQL query with table, columns, values. -
"""
INSERT INTO %s (%s)
VALUES (%s);
""", (AsIs(table), AsIs(','.join(column for column in columns)), AsIs(values))
);
I'am not showing all the code in here, because as well it can be viewed in the link below.
There is psycopg2 function quote_ident(string, cur);, that may be what will fix all of this issue's problems. It is most likely to fix it, but can be a dead end as well, please look into it.
For now, all references as well as improvements will be developed in ways to make it as easy as possible to implement this fix. With keeping changes inside Database class, and keeping one style of calls this should be no problem.
Here is link to file with changes made after the temporary solution:
https://github.com/lukaqueres/plan-it/blob/40b8bebee19f40a311fe7211382369a8d990250e/bot/packets/database.py#L63
Escaping problem
There is a problem with values escaping, making it impossible for now to input many columns-values with colons in strings.
As it is important to make it work before going further, a more temporary solution was applied. Payload must manually assign a number of values to columns, making it harder to insert many columns at once, as it will need more code.
Tries
There is ( if not deleted yet, still should be in the link below ) the class Escape in bot/packages/database.py that was used to ( well, tried to ) escape characters that potentially could cause problems, like
'or". As with all this class working, there would not be this issue, escaping didn't work.Code snippet that was used to execute SQL query for insert:
I'am not showing all the code in here, because as well it can be viewed in the link below.
Possible fix
There is psycopg2 function
quote_ident(string, cur);, that may be what will fix all of this issue's problems. It is most likely to fix it, but can be a dead end as well, please look into it.For now, all references as well as improvements will be developed in ways to make it as easy as possible to implement this fix. With keeping changes inside Database class, and keeping one style of calls this should be no problem.
Here is link to file with changes made after the temporary solution:
https://github.com/lukaqueres/plan-it/blob/40b8bebee19f40a311fe7211382369a8d990250e/bot/packets/database.py#L63