1

I tried to update a table from xsls file, but the problem is that I have space when I parse the lines.

Function:

def updateclientadress(nom, cnom, cplt_adr, adr, lieudit, cp, ville, numcli):
   #nom = str(nom)
   query = "update d_client set NOM = {}, CNOM = {}, CRUE = {}, RUE = {}, COMMUNE = {}, CODPOST = {}, VILLE = {} where NUMCLI = {}".format(nom, cnom, cplt_adr, adr, lieudit, cp, ville, numcli)
   print(query)
   cursorOracle.execute(query)

Printing the query:

update d_client set NOM = STE SAS GIG, CNOM = nan, CRUE = Zone Industrielle de Pariacabo, RUE = Rue, COMMUNE = BP 81, CODPOST = nan, VILLE = nan where NUMCLI = 270

As you can see, there are spaces in "NOM", "CRUE", so the oracle doesn't accept the update status,

The Error:

error:ORA-00933: la commande SQL ne se termine pas correctement

Note that the variable is extracted from an excel file. And the goal is to parse the 200k lines to update the database.

Blue Robin
  • 847
  • 2
  • 11
  • 31
Ookmai
  • 21
  • 4
  • Please only use `code` formatting for code. If you want to divide your problem into segments use different formatting options. – UnsanitizedInput Mar 30 '23 at 15:51
  • You aren't putting quotes around the variable values (`set NOM = '{}'`); but you [should be using bind variables](https://stackoverflow.com/a/33882805/266304) rather than embedding the values in the query string. – Alex Poole Mar 30 '23 at 17:07
  • @AlexPoole, if i use the '{}', the script gonna take nan values as string, and i am gonna have 'Nan' value instead of empty – Ookmai Mar 31 '23 at 12:52
  • Well you would only do that for string values... but like I (and MTO) already said you shouldn't be doing this at all, you should be using bind variables. – Alex Poole Mar 31 '23 at 14:08

1 Answers1

2

NEVER use string concatenation (or template strings) to build queries; that is how you introduce SQL injection vulnerabilities.

Instead, you should use a parameterised query and bind variables:

def updateclientadress(nom, cnom, cplt_adr, adr, lieudit, cp, ville, numcli):
   query = "update d_client set NOM = :1, CNOM = :2, CRUE = :3, RUE = :4, COMMUNE = :5, CODPOST = :6, VILLE = :7 where NUMCLI = :8"
   cursorOracle.execute(query, [nom, cnom, cplt_adr, adr, lieudit, cp, ville, numcli])
MT0
  • 143,790
  • 11
  • 59
  • 117