Help calling variables from within [=[ callVarHere ]=]

Good afternoon,

I'm having some difficulty figuring out how to escape out of a [=[ ... ]=] in order to call a variable name (forcing me to use some very code-intensive DatabasePrepare(INSERT INTO ...) etc. etc. routines. What I would like to do is:

NOTE: Not actual code from script,but similar

DatabaseExec(db, [=[
INSERT INTO table (x, y, z) VALUES ('value1', 'callvariablevalueX', 'value3');]=])

The problem I'm having (and this is probably a very simple thing to do, I just don't know it) is I can't escape out of the script to call the variable (in this case it would be locating in callvariablevalueX location) values. Am I relegated to not using the [=[ sqlscript ]=] approach in order to accomplish this?

Thanks for any help anyone can provide!

-DK

The [[]] quotes (note that they don't have to have any equals signs) are just like "" and '', excepting that you don't need to slash-escape " and ' inside the [[]] strings. So just like with any other string, you can use .. to concatenate multiple string values. For example:



Each of those strings used different quotes, if you noticed - it doesn't matter, they're all strings internally.

Does the "=" in between the brackets ([[) have any syntactical significance? Or is this just a matter of technique?

Twisol,

Thanks for the help! I actually tried using it as just a concatenation symbol at one point and had the same dBerror generated (severely throwing my troubleshooting off track).

I just realized that I forgot a semi-colon in one of the insert sequences and it runs beautifully. Thanks again! :)

The [[]] quoting can have any number of equal signs between the first and second ['s, so long as the same number are between the first and second ]'s. I believe this is mainly so that you can use [[ and ]] literallly in the string, like this:



That prints "Some [[brackets]] in here".



No problem! Glad I could help.

In your case I would probably use string.format to imbed stuff, like this:



Now value1, value2, and value3 can be variables. Make sure that if they might contain single quotes that you double them (there is suggested code for that in the http://www.gammon.com.au/sql page).

It's a nice way to guarantee that the string is "safe", or in other words, that you can directly evaluate code and be absolutely sure that nobody is terminating your string prematurely. Imagine you have something like this:


(Never mind the contrived example, there are very real uses for this.)
If 'a' contains any double quotes, you've just introduced a nasty bug into your program whereby a malicious user can run arbitrary code! (Oops.)
So you might use [[ and ]] instead... but ok, now you have to be sure that they don't use ]] in their code.

The solution: count the longest sequence of equal signs; let that number be n. Then use: [(n+1)*=[ as your quote delimiter. This way, you are absolutely sure that everything inside will be part of the string. This is a lot easier than trying to parse the input to see if it's appropriate.

You make a good point, David, but I don't think that was a very good example. It doesn't matter if there are any quotes in the 'a' variable, because it's all concatenated before being evaluated. There are many reasons why direct evaluation of code like that is dangerous, but this - at least in this case - isn't one of them.

I think you're thinking of SQL injection attacks, right? You could easily craft an injection attack even with your final 'solution'. Just set 'a' to something like this:



Not a single quote in sight, except those used around the execute() argument. The difference is that I have a ) at the beginning (to match the Note) and am lacking a ) at the end (because your eval provides it for me).


EDIT: i.e. the issue isn't the outer quotes - the ones defining the strings being concatenated - but the "inner" quotes, the ones delimiting the data to be executed. At any rate, this is all very applicable to the issue at hand - he IS querying a database - I just think you went off in the wrong direction. :D

Assuming you meant:



there is a simpler way...



The %q operator quotes strings, putting backslashes in front of newlines, backslashes themselves, and double-quotes, thus making it a safe string.

However in the case of SQL, my suggestion of string.format is what you really want because you need to double the single-quote character, not put a backslash in front of it.

If he did mean that, then it makes 100 times more sense and my reply is pointless. Oops, heh.

Yes, I meant single quotes. The point was merely that the [=[ ... ]=] construct is one way to force safety of a string. It's also very useful when you have several levels of embedded strings; sometimes you're not protecting against a malicious user but trying to preserve some sort of structure in your string (and I'm not sure that %q would always do the right thing here, although I haven't really thought about it much).