Getting variable from XML, parsing it and then forming a SQL query

Question

I have one config.xml file for configuration and another (steps.xml) that acts as a guide and contains steps that generate scripts for execution.

config.xml:

<CFG>
    <Server>sql-server.com</Server>
    <Database>mySQLDB</Database>
</CFG>

steps.xml:

<Steps>
    <Step no="1">
        <script>
          USE [<v>$configs.CFG.Database</v>]
          UPDATE server_name SET name = '<v>$configs.CFG.Server</v>'
        </script>
    </Step>
</Steps>

I use <v></v> to tell my script where the variables are. I don't know if that is a good way to go about.

Then I run this script to parse the files:

$configFile = 'd:\ps\config.xml'
$stepsfile = 'd:\ps\steps.xml'
[xml]$steps = Get-Content -Path $stepsfile 
[xml]$configs = Get-Content -Path $configFile

$t = $Steps.Steps.Step[0].script.v

foreach ($var in $t){
    $exec = Invoke-Expression $var
    # use $exec to form a working script
}

I can resolve the actual information from configuration file but I am stuck at putting it back together. I know I can use this to get full script text:

$Steps.Steps.Step[0].script."#text"

But I am lost how can I pinpoint the location of variables to put resolved names back into it and form a usable script.

The output I'm trying to achieve should be:

USE [mySQLDB]
UPDATE server_name SET name = 'sql-server.com'

Are the configuration files your own design and you can change, or is that something you have to live with? My first impression, they are really tedious to parse and potentially insecure. — ferrix
– ferrix, Commented Feb 22, 2019 at 6:55
I can change the design of configurations as i go, any suggestions that would make this easier and safer to implement are more than welcome :) — ConfusB
– ConfusB, Commented Feb 22, 2019 at 7:05
Invoke-Expression leads to arbitrary code execution. When the data that you are parsing is going to be SQL, Invoke-Sql is, let's say, only a threat to your database instead the whole system. A good topic to study would be: parametrized sql queries. — ferrix
– ferrix, Commented Feb 22, 2019 at 7:20
I am just generating the queries, I will not execute them from this script. — ConfusB
– ConfusB, Commented Feb 22, 2019 at 7:31
Here's a malicious example: <Steps><Step no=1><script>Restart-Computer</script></Step></Steps>. To avoid that from happening, you should do string manipulation without evaluating it as code. — ferrix
– ferrix, Commented Feb 22, 2019 at 7:36

Ansgar Wiechers · Accepted Answer · 2019-02-23 13:28:21Z

1

Invoke-Expression is considered harmful. Do not use it.

Instead of using nested XML nodes in the script body you could use any kind of placeholder that normally wouldn't appear in the SQL code (e.g. the names of the nodes from $configs in curly brackets: {Server} and {Database}) and then insert the values via string replacements.

foreach ($step in $steps.SelectNodes('//script')) {
    foreach ($cfg in $configs.SelectNodes('/CFG/*')) {
        $step.'#text' = $step.'#text'.Replace("{$($cfg.Name)}", $cfg.'#text')
    }
}

edited Feb 23, 2019 at 13:28

answered Feb 22, 2019 at 11:42

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting variable from XML, parsing it and then forming a SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related