RE: 4D SQL Implementation

[Aaron via 4D_Tech]

You can parameterize with SQL Execute using "SQL SET PARAMETER"
So any part that User enter should be using that.
 
You cannot parameterize and array. 
I’m not aware that IN is parametrizable in MYSQL or MSSQL either (though I 
suppose I could be wrong)
  MYSQL/PHP for example only supports a int,doubles, strings and blobs 
according to their documentation.
  mysql does have FIND_IN_SET which does take a findval and a comma delimited 
list, but that function is not in 4D to my knowledge.

  An IN list would need to be built dynamically if you need to use it.
  (but if it was you need to do you own injection checks)
  use at your own risk

sample of PARAMETERIZED using only local variables 


ARRAY TEXT($Test22;0)
C_Longint($a)
$a:=1

SQL LOGIN(SQL_INTERNAL;"";"") 
SQL SET PARAMETER($a;SQL param in)
SQL EXECUTE("SELECT field_2 FROM table_1 WHERE id = ? ";$test22)
If (Not(SQL End selection))  // If at least one record is found
SQL LOAD RECORD(SQL all records)  // Load all the records
end if
SQL LOGOUT


> 
> Message: 7
> Date: Mon, 17 Apr 2017 15:47:27 +
> From: Justin Will <jw...@willwerks.com <mailto:jw...@willwerks.com>>
> To: 4D iNug Technical <4d_tech@lists.4d.com <mailto:4d_tech@lists.4d.com>>
> Cc: Aaron <aaro...@nams-inc.com <mailto:aaro...@nams-inc.com>>
> Subject: RE: 4D SQL Implementation
> Message-ID:
>   
> <dm5pr15mb16413d7cd4bed3ff59490d93a7...@dm5pr15mb1641.namprd15.prod.outlook.com
>  
> <mailto:dm5pr15mb16413d7cd4bed3ff59490d93a7...@dm5pr15mb1641.namprd15.prod.outlook.com>>
>   
> Content-Type: text/plain; charset="utf-8"
> 
> Aaron,
> 
>> alternatively you can use SQL EXECUTE to still be able to use local 
>> variables, and build your query dynamically
> 
> Using strings built into queries is prone to sql injection if the query has 
> any input from the users and is considered a deadly sin in most cases.
> 
> Justin
> 
> 
> Date: Mon, 17 Apr 2017 16:01:26 +
> From: Timothy Penner <tpen...@4d.com <mailto:tpen...@4d.com>>
> To: 4D iNug Technical <4d_tech@lists.4d.com <mailto:4d_tech@lists.4d.com>>
> Subject: RE: 4D SQL Implementation
> Message-ID: <75f9d1a9382542889dcf8f0c06977...@4d.com 
> <mailto:75f9d1a9382542889dcf8f0c06977...@4d.com>>
> Content-Type: text/plain; charset="utf-8"
> 
>> Using strings built into queries is prone to sql injection if the query has 
>> any input from the users and is considered a deadly sin in most cases.
> 
> Here is a good example describing why you should never concatenate data into 
> a SQL statement; you should always use parameterized queries instead.
> http://bobby-tables.com/ <http://bobby-tables.com/>
> http://bobby-tables.com/about <http://bobby-tables.com/about>
> 
> -Tim




**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: 4D SQL Implementation

[Randy Engle via 4D_Tech]

Hi Tim,

Thanks for the reminder about Bobby Tables.

This reminds me of a still existing issue, when accessing 4D via ODBC.

I found that I can call a DROP TABLE from various ways with a SQL editor.

I seem to be missing a way to keep them from successfully calling this command, 
except to not let them have ODBC access in the first place.

We have our system set to not allow INSERTS and UPDATES, but DROP TABLE still 
works if they have ODBC access.

Any hot tips to prevent this?  I'm a SQL newbie, so maybe I'm missing something 
obvious.

Thanks

Randy Engle
XC2 Software LLC

-Original Message-
From: 4D_Tech [mailto:4d_tech-boun...@lists.4d.com] On Behalf Of Timothy Penner 
via 4D_Tech
Sent: Monday, April 17, 2017 9:01 AM
To: 4D iNug Technical <4d_tech@lists.4d.com>
Cc: Timothy Penner <tpen...@4d.com>
Subject: RE: 4D SQL Implementation

> Using strings built into queries is prone to sql injection if the query has 
> any input from the users and is considered a deadly sin in most cases.

Here is a good example describing why you should never concatenate data into a 
SQL statement; you should always use parameterized queries instead.
http://bobby-tables.com/
http://bobby-tables.com/about

-Tim



**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: 4D SQL Implementation

[Timothy Penner via 4D_Tech]

> Using strings built into queries is prone to sql injection if the query has 
> any input from the users and is considered a deadly sin in most cases.

Here is a good example describing why you should never concatenate data into a 
SQL statement; you should always use parameterized queries instead.
http://bobby-tables.com/
http://bobby-tables.com/about

-Tim




**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: 4D SQL Implementation

[Justin Will via 4D_Tech]

Aaron,

> alternatively you can use SQL EXECUTE to still be able to use local 
> variables, and build your query dynamically

Using strings built into queries is prone to sql injection if the query has any 
input from the users and is considered a deadly sin in most cases.

Justin
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: 4D SQL Implementation

[Aaron via 4D_Tech]

> You still can't use local variables in a EXECUTE IMMEDIATE SQL statement in a 
> compiled application?  Is it really that difficult to implement?


alternatively you can use SQL EXECUTE to still be able to use local variables, 
and build your query dynamically

sample:

C_TEXT($sqlStmt)
$sqlStmt:=""
$sqlStmt:=$sqlStmt+"SELECT "
$sqlStmt:=$sqlStmt+"cast( "+$col1+" as varchar) ,"
$sqlStmt:=$sqlStmt+"cast( “+$col2+" as varchar) "
$sqlStmt:=$sqlStmt+"FROM "+$tablename+" "
$sqlStmt:=$sqlStmt+"where "+$where"n+"; "

SQL LOGIN(SQL_INTERNAL;"";"")
SQL EXECUTE($sqlStmt;$col1;$col2)
While (Not(SQL End selection))
  SQL LOAD RECORD(SQL all records)
End while 
SQL LOGOUT





> - Why isn't the use of 4D array variables in SQL queries supported?


You could implode an array (sometimes known as join) to a delimited list As you 
build your sql statement
SQL statements can be executed by SQL EXECUTE or EXECUTE IMMEDIATE SQL
I included a sample Implode/Join 

IN syntax according to 4D

http://doc.4d.com/4Dv16/4D/16/in-predicate.300-3201202.en.html 

SELECT *
FROM ORDERS
WHERE order_id IN (1, 10001, 10003, 10005);




  // Method: Join
  // example: $return := Join (->$myarray;",")  the Array myarray will return a 
string separated by comma

C_TEXT($0)  //joined array
C_POINTER($1)  //passed array
C_TEXT($2)  //value to delimit by
C_LONGINT($iter)  //iterate array
C_TEXT($delim;$return)

  //presets
$delim:=$2
$return:=""

For ($iter;1;Size of array($1->))  //empty array will return nothing
  $return:=$return+$1->{$iter}
  If ($iter#Size of array($1->))
$return:=$return+$delim  //add delim unless its the last record, if only 1 
record, there will be no delim
  End if 
End for
 
$0:=$return





> Message: 1
> Date: Fri, 14 Apr 2017 08:29:56 -0600
> From: Bart Davis 
> To: 4d_tech@lists.4d.com
> Subject: 4D SQL Implementation
> Message-ID: <583fdbff-6490-4373-80ee-7b22cfaea...@phoenixlearning.com>
> Content-Type: text/plain; charset=us-ascii
> 
> 
>> On Apr 14, 2017, at 2:35 AM, 4d_tech-requ...@lists.4d.com wrote:
>> 
>> 
>> Message: 3
>> Date: Thu, 13 Apr 2017 16:31:15 -0400
>> From: Jeffrey Kain 
>> To: 4D iNug Technical <4d_tech@lists.4d.com>
>> Subject: Re: How to create Object field via SQL
>> Message-ID: 
>> Content-Type: text/plain; charset=us-ascii
>> 
>> It's almost as if the SQL engine is a bolted-on afterthought to 4D that 
>> doesn't receive much love at all from the development team.
> 
> I couldn't agree more with this statement "It's almost as if the SQL engine 
> is a bolted-on afterthought to 4D that doesn't receive much love at all from 
> the development team."
> 
> - The fact that you can't use SQL in a trigger when a record is inserted, 
> updated or deleted via SQL is baffling.  Every other SQL database seems to be 
> able to do it.
> 
> -  You still can't use local variables in a EXECUTE IMMEDIATE SQL statement 
> in a compiled application?  Is it really that difficult to implement?
> 
> - Could there be some internal semaphore/blocking code that prevents some 
> part of a SQL query in one process from executing (this includes ORDER BY) 
> when a SQL query in executing in a different process?  In two different web 
> applications that have heavy concurrent use, progress bar windows start 
> appearing on the screen saying ORDER BY or LOADING DATA and as more processes 
> are initiated more windows appear the application grinds to a stop and must 
> be killed and restarted.  These are simple queries with Order By on indexed 
> fields on small data sets.  I replaced the SQL queries with the standard 4D 
> Query language and there are no more performance issues.  On all heavily used 
> queries I have replaced SQL with 4D query language.
> 
> - Why isn't the use of 4D array variables in SQL queries supported?
> 
> Assuming $aRecordID is a populated Longint Array...
> 
> Begin SQL
>  Select * from Table where ID in :$aRecordID
> End SQL
> 
> Bart
> 

> 

thanks,
Aaron
-- 
Aaron Blazer
Myriad Systems
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

Re: 4D SQL Implementation

[Jeffrey Kain via 4D_Tech]

Totally agree with all of that. I was hopeful that since Wakanda uses the same 
engine that we'd see a lot of improvement in the SQL engine of 4D, but not so 
much. Another example: a SELECT on an indexed field using a nested SELECT works 
great until you reach a certain number of rows returned. Once you go past this 
magic number, 4D SQL forgets there's an index and drops you into the land of 
full table scans. So frustrating.

We're probably an exception, but we use the SQL/ODBC engine a lot here. The UPS 
and FedEx computers in the warehouse use ODBC connections to update shipping 
records in 4D, several FileMaker systems also use ODBC to receive up-to-date 
information from 4D and create pretty signs, and we make extensive use of the 
4D to PHP DataObjects library (http://www.php.net/manual/en/ref.pdo-4d.php) in 
a lot of custom projects for monitoring, dashboards, and reporting. The 4D SQL 
engine works great until it doesn't, and when it doesn't there's not much you 
can do other than push the data to MySQL or Postgres. 

And did I mention object fields? :)

Anyway, there's a lot of potential in the 4D SQL engine, and I hope that 4D 
hasn't abandoned it or decided to treat it as a marketing checklist feature.

Jeff

On Apr 14, 2017, at 10:29 AM, Bart Davis via 4D_Tech <4d_tech@lists.4d.com> 
wrote:

> I couldn't agree more with this statement "It's almost as if the SQL engine 
> is a bolted-on afterthought to 4D that doesn't receive much love at all from 
> the development team."
> 
> - The fact that you can't use SQL in a trigger when a record is inserted, 
> updated or deleted via SQL is baffling.  Every other SQL database seems to be 
> able to do it.
> 
> -  You still can't use local variables in a EXECUTE IMMEDIATE SQL statement 
> in a compiled application?  Is it really that difficult to implement?
> 
> - Could there be some internal semaphore/blocking code that prevents some 
> part of a SQL query in one process from executing (this includes ORDER BY) 
> when a SQL query in executing in a different process?  In two different web 
> applications that have heavy concurrent use, progress bar windows start 
> appearing on the screen saying ORDER BY or LOADING DATA and as more processes 
> are initiated more windows appear the application grinds to a stop and must 
> be killed and restarted.  These are simple queries with Order By on indexed 
> fields on small data sets.  I replaced the SQL queries with the standard 4D 
> Query language and there are no more performance issues.  On all heavily used 
> queries I have replaced SQL with 4D query language.
> 
> - Why isn't the use of 4D array variables in SQL queries supported?
> 
> Assuming $aRecordID is a populated Longint Array...
> 
> Begin SQL
>  Select * from Table where ID in :$aRecordID
> End SQL

**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**

RE: 4D SQL Implementation

[Justin Will via 4D_Tech]

Bart,

I have to agree with everything you stated.  At one point in time I had a 
number of SQL calls in a web app that had some heavy use.  The day I rolled out 
the SQL code I had to roll it back to 4D Query code because performance was so 
bad the site became unusable.  At this point in time I avoid using 4D's SQL 
engine like it’s the black plague or something.  I just find it too unreliable, 
to slow and has abysmal SQL support beyond the most basic of syntax.

I sure wish they would make the SQL engine much stronger, but until it gets 
some real effort I will certainly avoid it.  It's a real shame too, because 
reporting and analytical tool options would really open up some fun new 
possibilities.

Thanks
Justin Will
**
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:4d_tech-unsubscr...@lists.4d.com
**