enoying "line return" while concatenating SQL string

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
liveme
Posts: 240
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

enoying "line return" while concatenating SQL string

Post by liveme » Tue Jan 19, 2021 10:14 am

Hi guys,
Dealing with Sqlite queries...
I'm trying to get rid of some enoying "line return" while concatenating a string of 3 parameters.
Somehow, with the 3rd param adding, the results is sent to the next line, breaking the string...

(I had no such breaking under Manjaro but now testing the function under Zorin pose this problem, dont think its an OS issue though)

Does anyone know how to impeed unwanted line return and keep all the whole data line happy together ? :mrgreen:

Code: Select all

  put "'" into tcritera
      put field fcritex after tcritera    # (fcritex input : 66)
      put "%'" into tcriterb
      put tcriterb after tcritera
      put tcritera into tcriterm  
is there some extra symbol to impeed line returns or another setting to modify ?
* any reliable methods to buid such string is welcomed

Code: Select all

SELECT * FROM users WHERE userID LIKE '66
%';
:shock:
*as displayed in the field/content window properties

Expected results : (for tcriterm to be passed to tSQL)

Code: Select all

SELECT * FROM users WHERE userID LIKE '66%';
:roll:

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: enoying "line return" while concatenating SQL string

Post by AxWald » Tue Jan 19, 2021 11:28 am

Hi,

you're concatenating a fields content ("field fcritex") without validating the value (thus allowing SQL-Injection).

In this case the content of your field ends with CR, so only your query misfires.
If you accept user input as parameters for SQL statements, ALWAYS make sure it's what you expect:

Code: Select all

   put word 1 of line 1 of field fcritex into myVar  --  we only need this
   if myVar is not a number then                     --  out param is numeric
      answer error "This is not a value I'd accept: " & myVar titled "Bummer."
      exit to top                                    --  never allow this!
   end if
  put "SELECT * FROM users WHERE userID LIKE '" & myvar & "%';" into tcriterm
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

liveme
Posts: 240
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: enoying "line return" while concatenating SQL string

Post by liveme » Tue Jan 19, 2021 6:13 pm

Hi Axwald, thanks for the recording about injection...
still, my problem is more related to concatenation problem of (param1 + ) input2 + param2 and unwanted line return..
:idea: :!:

liveme
Posts: 240
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: enoying "line return" while concatenating SQL string

Post by liveme » Tue Jan 19, 2021 6:43 pm

>>> ' " ... " ';" << [/code]
Have fun!
Yeah, it works ! Great, Sorry I, had not seen the "%" you added directly at the end of the SQL line. :wink:

The sequence of ' " " ' ...ending with a single " ...still seems quite weird to me thinking they could only work in pairs... :!:
I was trying hard to do that on this line too...but with the extra paired " " ...and it would not work !

Thanks for this big help,
I will look for when "word 1 of the line 1" is meant to be used which also seem required in that case.
Next, yess, SQL anti injection measures are needed :arrow: ALWAYS !
Thanks again :!:

Klaus
Posts: 13806
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: enoying "line return" while concatenating SQL string

Post by Klaus » Tue Jan 19, 2021 6:59 pm

Hi Liveme,

here some handy functions that I use, not only when dealing with databases:

Code: Select all

function q tString
  ## We pass a string and the function will return this string in QUOTES
  return QUOTE & tString & QUOTE
end q
put q("A String") -> "A string"

Code: Select all

function q2 tString
  ## We pass a string and the function will return this string in SINGLE QUOTES
  return "'" & tString & "'"
end q
put q2("A string") -> 'A string'


Best

Klaus

liveme
Posts: 240
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: enoying "line return" while concatenating SQL string

Post by liveme » Wed Jan 20, 2021 8:44 am

Cool, Thanks...

As for dealing with number, I guess prohibiting any text content is enuf against injection
So how about needs to query text columns ?
- do I understand that just by placing those "quote" it always will "deactivate" any injection formula ?
..if such than thats cool !

Beside I've been testing to inject added hacks in my 2nd criteria field "Creator" :

- instead of just 10.
10 OR 14
but it kept ignoring the 'OR 14'

Code: Select all

SELECT * FROM users WHERE name LIKE  'N%' AND Creator =' 10';
Now...
- should I understand that the injection was indeed blocked not to go over creator 10.


:?: :?: :?:
- or maybe I'm trying to use some injected "string" that is not indeed usefull to return any extra content ?

- or this is just the results of using : put word 1 ..meaning it will not accept anyting else after the last caracter of Word 1 ?

-..in which case your use of "return QUOTE & tString & QUOTE" is adding Quotes,
but doesnt limit the value to just the First Word, correct ?

Sorry have so many questions : my first real use of SQL so far...
Thanks for you help guys...

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: enoying "line return" while concatenating SQL string

Post by AxWald » Wed Jan 20, 2021 11:59 am

Hi,

I'll try to explain:

1.) "word 1 of line 1 of field fcritex"
We know it's 1 number that we expect as argument. Right?

What could happen if we'd allow longer strings? Well, there's the immortal XKCD example ;-)
Only this wouldn't work in Livecode, you cannot use 2 commands in 1 string.
But you could enter this into field fcritex:

Code: Select all

1' OR id LIKE '
The SQL statement resulting would be:

Code: Select all

SELECT * FROM users WHERE userID LIKE '1' OR id LIKE '%';
and, assumed there's a row "id", return all records.

The "of line 1" part is to make sure you'd not accidentally get CRs from the field - it's not really needed. "Word 1 of field ..." would be enough already, it returns everything in the field until the first space char.

But this would also accept "empty" (word 1 of empty = empty), and empty would return all records, again:

Code: Select all

SELECT * FROM users WHERE userID LIKE '%';
So we test if our result really is a number, and this makes sure we query for what we actually intend to do.

Experience shows that it's crucial to "sanitize" all & any direct user input - even the most agreeable, guileless, common user is easily able to blow up your carefully crafted code with sophistically malicious input, if given a chance. And if it's only copying a cell from Excel & pasting it - the obvious reason for the CR problem in the OP.

2.) "' " " '" :)
It's actually quite simple. When creating SQL statements to use with LC it's common that you want a variable somewhere in your code. A quick way to insert those is to have a certain "clip" handy, to copy:

Code: Select all

" & MyX & "
Then you write your SQL statement with a placeholder:

Code: Select all

SELECT * FROM users WHERE userID LIKE '99%';
Now select the placeholder "99", paste, and you have:

Code: Select all

SELECT * FROM users WHERE userID LIKE '" & MyX & "%';
Change "myX" to the desired variable name, Bingo.


Klaus' approach is a bit different. He uses quoting functions to make sure we have correct quoting pairs. So, since we want our LIKE param as a string (which needs single quotes; may myVar be 99):

Code: Select all

myVar & "%"  --> 99% = anything that starts with 99
he'd use:

Code: Select all

q2(myVar & "%")  --> '99%'
and since we need to append this to:

Code: Select all

"SELECT * FROM users WHERE userID LIKE "
he'd actually write:

Code: Select all

q(q2(myVar & "%"))  --> "'99%'"
So:

Code: Select all

put "SELECT * FROM users WHERE userID LIKE " & q(q2(myVar & "%")) into StrSQL
results in:

Code: Select all

SELECT * FROM users WHERE userID LIKE '99%'
Since we can omit the trailing semicolon this is a working SQL statement.


Guess the confusion in your last post is mainly about the mixing up of:
  1. the string concatenation in LC (which uses double quotes) and
  2. the required string quoting in SQL (that uses single quotes)
Adding to the confusion can be table and field names in your database that are reserved words (date, text, order ...) - such MUST be quoted, too! Best use "back tick", "grave", "`" for this, it works in most SQL dialects.

Advanced SQL statements alone can be quite a mess of brackets, single quotes and back ticks.
Concatenating such from LC strings, using variables, greatly adds to the chaos.
The result can easily be close to incomprehensible.

This is where "SQL Yoga" (topic of this forum) comes to aid - it tries to help here. If this isn't enough, you may try the way I do it (and I do this for a living):
  1. I create my desired SQL statement in a SQL manager (here: SQLite manager, HeidiSQL, MS Access) - using real variables. Until the query works flawlessly, gives the desired results in an acceptable time, in the correct order. Then I copy the raw SQL.
  2. This is pasted into a good text editor (here: EditPad pro). I use premade "clips" & search/replace settings to replace my placeholders with variable names, adjust line lengths & add the "decoration" for LC ("quote" at each line start, "quote, space, &, space, backslash" at each line end but the last).
  3. This then is pasted into LC between "put " & " into StrSQL". A tab formats it, done.
Always works (if I didn't mess up ...), even with most voluminous & cryptic statements. ;-)

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Post Reply

Return to “SQL Yoga”