CODEWITHSUNDEEP
sqltcl
vinay
vinay

Reputation: 51

need to add string in between a SQL statement using TCL

I have a SQL statement stored inside a variable:

I get this "statement" from the ".sql" file using file operations

set statement "SELECT build_package,
replace(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(substring(build_package, length(build_package) - position('/' in reverse(build_package)) + 2), '-BSD',''),'-LNX',''),'-esxi',''),'.repo',''),'.vmdk',''),'.qcow2',''),'.rpm',''),'.tbz',''),'-version',''),'.el7.x86_64','') ,'-NR',''),'-kvm',''),'-x86_64',''),'ptsvpl','svpts'),'spbvpl','svspb'),'sdevpl','svsde'),'-amd64',''),'.noarch',''),'.el6',''),'.x86_64',''),'tsevpl','svtse')
FROM scheduler_jobs WHERE id = '1617075' order by id DESC"

here the where clause is e.g.: WHERE id = 1617075 the id is dynamically generated.

So I need to first store the sql without WHERE id = 1617075 clause and add the where clause later when i get it inside the 'statement' variable.

Basically add some data called $id in the statement.

Upvotes: 0

Views: 192

Answers (2)

SQB
SQB

Reputation: 4078

Use a prepared statement with bound variables.

The example from the documentation:

set statement [db prepare {
    select phone_num from directory
    where first_name = :firstname and last_name = :lastname
}]
set firstname Fred
set lastname Flintstone

As the documentation explains,

The prepare object command against the connection accepts arbitrary SQL code to be executed against the database. The SQL code may contain bound variables, which are strings of alphanumeric characters or underscores (the first character of the string may not be numeric), prefixed with a colon (:). If a bound variable appears in the SQL statement, and is not in a string set off by single or double quotes, nor in a comment introduced by --, it becomes a value that is substituted when the statement is executed. A bound variable becomes a single value (string or numeric) in the resulting statement.
Drivers are responsible for ensuring that the mechanism for binding variables prevents SQL injection.

In other words, while it is the driver's responsibility, you should be protected against SQL Injection. In other words, if the id isn't simply 1617075 but rather 1617075; DROP TABLE scheduler_jobs;--, you should still be okay, because the driver has properly escaped this (instead of you having to do this, missing an edge case which leaves you vulnerable).

For your example, leaving out all the REPLACEs, this would become

set statement [db prepare {
    SELECT build_package 
    FROM scheduler_jobs
    WHERE id = :scheduler_job_id
    ORDER BY id DESC
}]
set scheduler_job_id 1617075

Upvotes: 2

Mario Santini
Mario Santini

Reputation: 3003

Just use string map.

First save your string as a template with a tag in it and remove any part you want to substitute:

set statement "SELECT build_package,
replace(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(substring(build_package, length(build_package) - position('/' in reverse(build_package)) + 2), '-BSD',''),'-LNX',''),'-esxi',''),'.repo',''),'.vmdk',''),'.qcow2',''),'.rpm',''),'.tbz',''),'-version',''),'.el7.x86_64','') ,'-NR',''),'-kvm',''),'-x86_64',''),'ptsvpl','svpts'),'spbvpl','svspb'),'sdevpl','svsde'),'-amd64',''),'.noarch',''),'.el6',''),'.x86_64',''),'tsevpl','svtse')
FROM scheduler_jobs <WHERE_CLAUSE> order by id DESC"

Here the tag I choose is , but you could use what you prefer.

And then:

set query_cmd [string map {<WHERE_CLAUSE> "WHERE id = '123456'"} $statement]

puts $query_cmd

This could be done for many different part of the string you need to change, just use another tag.

set query_cmd [string map {<TAG1> $v1 <TAG2> $v2...}  $statement]

You can of course put inside the string a quoted variable like:

...WHERE id = '\$id'...

And then use subst, but I think in this case the string map could be more useful.

Upvotes: 0

Related Questions