mysql - Reuse parameterized (prepared) SQL Query -

i've coded activedirectory logging system couple of years ago... never become status greater beta still in use...

i got issue reported , found out happening... serveral filds in such activedirectory event witch userinputs, i've validate them! -- of course didnt...

so after first user got brilliant idea use singlequotes in specific foldername crashed scripts - easy injection possible...

so id make update using prepared statements im using in php , others.

now powershell script.. id this:

$mysql-obj.commandtext = "insert `table-name` (i1,i2,i3) values (@k1,@k2,@k3)"  $mysql-obj.parameters.addwithvalue("@k1","value 1") $mysql-obj.parameters.addwithvalue("@k2","value 2") $mysql-obj.parameters.addwithvalue("@k3","value 3")  $mysql-obj.executenonquery() 

this work fine - 1 times. script runs endless service , loops within while($true) loop.

powershell clams param set...

exception calling "addwithvalue" "2" argument(s): "parameter '@k1' has been defined."

how can reset "bind" without closing database connection? id leave connection open because script faster without closing , opening connections when event fired (10+ / sec)

example code (shortend , not tested)

##start function db_prepare(){     $mysqlconnection = new-object mysql.data.mysqlclient.mysqlconnection     $mysqlconnection.connectionstring = "server=$mysqlservername;user id=$username;password=$password;database=$mysqldatenbankname;pooling=false"     $mysqlconnection.open()     $mysqlcommand = new-object mysql.data.mysqlclient.mysqlcommand     $mysqlcommand.connection = $mysqlconnection     $mysqlcommand.commandtext = "insert `whatever` (col1,col2...) values (@va1,@va2...)" } while($true){     if($mysqlconnection.state -eq 'closed'){ db_prepare() }      ## event reading , data formating stuff     ## bild variables set sql param values      $mysqlcommand.parameters.addwithvalue("@va1",$variable_for_1)     $mysqlcommand.parameters.addwithvalue("@va2",$variable_for_2)     .     .     .      try{  $mysqlcommand.executenonquery() | out-null }     catch{ <# error handling #> }  } 

change logic db_prepare() method initializes mysql connection , mysql command parameters. set parameter values pre-declared parameter names in loop. so,

function db_prepare(){     # ...     # add named parameters     $mysqlcommand.parameters.add("@val1", <datatype>)     $mysqlcommand.parameters.add("@val2", <datatype>) } while($true) {     # ...     # set values named parameters     $mysqlcommand.parameters.setparameter("@val1", <value>)     $mysqlcommand.parameters.setparameter("@val2", <value>)     $mysqlcommand.executenonquery()      # ... }