Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
1. Macro-compilation 2. Evaluate(<script>)
From a X# perspective the FOX release already has support for:
1. DBFCDX and readonly access for VFP specific FieldTypes (AutoInc, etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
Thanks,
Good news is that X# in its current version is mapping TEXT/ENDTEXT to an internal function... See personal communication regarding the command with Robert.
TEXT .. ENDTEXT is also parsed and send to a function with the name "__TextSupport". That function is not included in the runtime yet, but if you include that function in your code then it works.
So in short, this will be supported by X#, the internals are just not yet implemented, but will be with the final release of VFP support. If somebody interested, it can be developed by a community member as part of the opensource runtime...
Regards,
Johan.
On 2019/06/27 17:08, MB Software Solutions, LLC wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
Am I missing something about TEXT/ENDTEXT where it just mashes up a string for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
In my largest case, it's usually helping to build SQLs dynamically depending on a number of variables. In other cases, it's for building a SET FILTER command which then gets executed.
On 6/27/2019 3:43 PM, Stephen Russell wrote:
Am I missing something about TEXT/ENDTEXT where it just mashes up a string for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
Thanks.
It gives me a chuckle like this: https://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables
On Thu, Jun 27, 2019 at 2:45 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
In my largest case, it's usually helping to build SQLs dynamically depending on a number of variables. In other cases, it's for building a SET FILTER command which then gets executed.
On 6/27/2019 3:43 PM, Stephen Russell wrote:
Am I missing something about TEXT/ENDTEXT where it just mashes up a
string
for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
Knew you'd post that. I win my bets. lol
Careful consideration is always made so thanks for the humor nonetheless.
On 6/27/2019 4:10 PM, Stephen Russell wrote:
Thanks.
It gives me a chuckle like this: https://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
I now have an Information Safety manager who is applying real safety for our company to protect from the outside as well as ID-10-Ts who open the wrong documents.
He has hacking sub-contractors that test our internal web apps and I get to see what they do in logs, and I ask if it was him or his team. We are now owned by Michael Dell and we now have a lot of new compliance to adhere to.
100% of all of our custom web access is via sprocs. Late this year or next year I'll be creating APIs to utilize as we start to post functionality to the cloud. Some data will be there and some will be here, depends on the system.
On Thu, Jun 27, 2019 at 3:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Knew you'd post that. I win my bets. lol
Careful consideration is always made so thanks for the humor nonetheless.
On 6/27/2019 4:10 PM, Stephen Russell wrote:
Thanks.
It gives me a chuckle like this: https://www.explainxkcd.com/wiki/index.php/Little_Bobby_Tables
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
I use TEXT/ENDTEXT to create dynamic SQL Queries by merging static text and dynamic variables, then pass the resulting string to SQLEXECUTE. Very convenient when creating large query strings.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 2:43 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
Am I missing something about TEXT/ENDTEXT where it just mashes up a string for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
As a dba that scares the shit out of me. Just being honest about dynamic statements and hackers who might know how to mess with you.
This fear is real.
https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
On Thu, Jun 27, 2019 at 3:10 PM Paul H. Tarver paul@tpcqpc.com wrote:
I use TEXT/ENDTEXT to create dynamic SQL Queries by merging static text and dynamic variables, then pass the resulting string to SQLEXECUTE. Very convenient when creating large query strings.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 2:43 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
Am I missing something about TEXT/ENDTEXT where it just mashes up a string for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
I meant "dynamic" in the sense of controlling variables in SELECT WHERE statements. Didn't intent to scare anyone.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 3:17 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
As a dba that scares the shit out of me. Just being honest about dynamic statements and hackers who might know how to mess with you.
This fear is real.
https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
On Thu, Jun 27, 2019 at 3:10 PM Paul H. Tarver paul@tpcqpc.com wrote:
I use TEXT/ENDTEXT to create dynamic SQL Queries by merging static text
and
dynamic variables, then pass the resulting string to SQLEXECUTE. Very convenient when creating large query strings.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 2:43 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
Am I missing something about TEXT/ENDTEXT where it just mashes up a string for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
just put this into your textbox that gets passed back:
1=1 ; Drop table customers go
On Thu, Jun 27, 2019 at 3:54 PM Paul H. Tarver paul@tpcqpc.com wrote:
I meant "dynamic" in the sense of controlling variables in SELECT WHERE statements. Didn't intent to scare anyone.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 3:17 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
As a dba that scares the shit out of me. Just being honest about dynamic statements and hackers who might know how to mess with you.
This fear is real.
https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
On Thu, Jun 27, 2019 at 3:10 PM Paul H. Tarver paul@tpcqpc.com wrote:
I use TEXT/ENDTEXT to create dynamic SQL Queries by merging static text
and
dynamic variables, then pass the resulting string to SQLEXECUTE. Very convenient when creating large query strings.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 2:43 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
Am I missing something about TEXT/ENDTEXT where it just mashes up a
string
for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet
(or
earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback,
so
I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will
feel
is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
Only if I were trying to destroy my clients and my livelihood. Give me a little credit for being a better programmer than that.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 5:02 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
just put this into your textbox that gets passed back:
1=1 ; Drop table customers go
On Thu, Jun 27, 2019 at 3:54 PM Paul H. Tarver paul@tpcqpc.com wrote:
I meant "dynamic" in the sense of controlling variables in SELECT WHERE statements. Didn't intent to scare anyone.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 3:17 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
As a dba that scares the shit out of me. Just being honest about dynamic statements and hackers who might know how to mess with you.
This fear is real.
https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
On Thu, Jun 27, 2019 at 3:10 PM Paul H. Tarver paul@tpcqpc.com wrote:
I use TEXT/ENDTEXT to create dynamic SQL Queries by merging static text
and
dynamic variables, then pass the resulting string to SQLEXECUTE. Very convenient when creating large query strings.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, June 27, 2019 2:43 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
Am I missing something about TEXT/ENDTEXT where it just mashes up a
string
for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet
(or
earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback,
so
I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will
feel
is a step backwards when migrating to .NET or any other platform as
a
move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta...
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Hi Steve,
I don't doubt your method is super secure, but this shows a difference of approaches: you code heavily in the backend DB and as a result everything is very rigid. If things were to need to be changed to a different backend, then your project is most definitely a heavier/longer/more-expensive job than mine. Granted, that scenario may never come to be for many projects, but ever since I saw Bob Lee demonstrate VFP using MySQL at the 2003 WhilFest conference, I've applied that 3-tier (UI/BizObj/DataObj) approach to all of my solutions. I still do some programming in the backend database for triggers and some minor functions and stored procedures, but most of my data code is in the DataObj tier of my app. I have found that it helps me to be very nimble/agile with projects.
You're the large mass army; I'm the quick-strick special teams. Both can do the job and each has their strengths. ;-)
On 6/28/2019 10:27 AM, Stephen Russell wrote:
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta...
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
I was already doing that with the FP distribution tool kit from FP Dos days and working with a custom db that the law firm had that was proprietary to the system the firm had. They made an ODBC driver and I got data in and out of it 93-96.
Mike, you see every problem as a nail and you apply the VFP hammer at with ease as well as speed.
If you did the same thing with your backend of choice and allowed the DB to do the tasks it does so well and you just task them from VFP I'd say you are a multi-tiered developer. When you have to grab data from any system off of the web you would usee the right tools to do that as well, HTML & CSS or python.
I have to get international bank exchange rates on a monthly basis. It is a simple grab but I don't use my database to do that.
On Fri, Jun 28, 2019 at 10:00 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Hi Steve,
I don't doubt your method is super secure, but this shows a difference of approaches: you code heavily in the backend DB and as a result everything is very rigid. If things were to need to be changed to a different backend, then your project is most definitely a heavier/longer/more-expensive job than mine. Granted, that scenario may never come to be for many projects, but ever since I saw Bob Lee demonstrate VFP using MySQL at the 2003 WhilFest conference, I've applied that 3-tier (UI/BizObj/DataObj) approach to all of my solutions. I still do some programming in the backend database for triggers and some minor functions and stored procedures, but most of my data code is in the DataObj tier of my app. I have found that it helps me to be very nimble/agile with projects.
You're the large mass army; I'm the quick-strick special teams. Both can do the job and each has their strengths. ;-)
On 6/28/2019 10:27 AM, Stephen Russell wrote:
I am backing off of licenses for SQL Enterprise down to Standard for 2/3
of
all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta...
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to
a
table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and see
if
you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
"Use the best tool for the job"
In my travels and experiences with SMBs (and mostly Smalls!), I've been able to generate very flexible code and (knock on wood) I've not been "hacked" ever.
ymmv!
On 6/28/2019 11:21 AM, Stephen Russell wrote:
I was already doing that with the FP distribution tool kit from FP Dos days and working with a custom db that the law firm had that was proprietary to the system the firm had. They made an ODBC driver and I got data in and out of it 93-96.
Mike, you see every problem as a nail and you apply the VFP hammer at with ease as well as speed.
If you did the same thing with your backend of choice and allowed the DB to do the tasks it does so well and you just task them from VFP I'd say you are a multi-tiered developer. When you have to grab data from any system off of the web you would usee the right tools to do that as well, HTML & CSS or python.
I have to get international bank exchange rates on a monthly basis. It is a simple grab but I don't use my database to do that.
On Fri, Jun 28, 2019 at 10:00 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Hi Steve,
I don't doubt your method is super secure, but this shows a difference of approaches: you code heavily in the backend DB and as a result everything is very rigid. If things were to need to be changed to a different backend, then your project is most definitely a heavier/longer/more-expensive job than mine. Granted, that scenario may never come to be for many projects, but ever since I saw Bob Lee demonstrate VFP using MySQL at the 2003 WhilFest conference, I've applied that 3-tier (UI/BizObj/DataObj) approach to all of my solutions. I still do some programming in the backend database for triggers and some minor functions and stored procedures, but most of my data code is in the DataObj tier of my app. I have found that it helps me to be very nimble/agile with projects.
You're the large mass army; I'm the quick-strick special teams. Both can do the job and each has their strengths. ;-)
On 6/28/2019 10:27 AM, Stephen Russell wrote:
I am backing off of licenses for SQL Enterprise down to Standard for 2/3
of
all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta...
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to
a
table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and see
if
you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
TEXT...ENDTEXT is a door to SQL Injection Attacks as much as anything else we put inside the ODBC tube.
LOCAL SQLStmt AS String
** Thisform.txtSearch.Value = "1 = 1; DROP TABLE Customers;"
TEXT TO m.SQLStmt NOSHOW TEXTMERGE SELECT * FROM Customers<<IIF(!EMPTY(Thisform.txtSearch.Value), " WHERE Name LIKE ?('%' + Thisform.txtSearch.Value + '%')", "")>>; ENDTEXT
** or
TEXT TO m.SQLStmt NOSHOW TEXTMERGE SELECT * FROM Customers<<IIF(!EMPTY(Thisform.txtSearch.Value), " WHERE Name = ?Thisform.txtSearch.Value", "")>>; ENDTEXT
Unless there is at least one customer with a name that starts or contains "1 = 1; DROP TABLE Customers;", the resulting cursor will be empty and the Customers table perfectly usable afterwards. Otherwise, the cursor will return the "1 = 1; DROP TABLE Customers;" customers. And the Customers table will still be there.
On Fri, Jun 28, 2019 at 3:27 PM Stephen Russell srussell705@gmail.com wrote:
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta...
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
** er.... more like Thisform.txtSearch.Value = "'ACME'; DROP TABLE Customers;"
On Fri, Jun 28, 2019 at 4:03 PM António Tavares Lopes atlopes@gmail.com wrote:
TEXT...ENDTEXT is a door to SQL Injection Attacks as much as anything else we put inside the ODBC tube.
LOCAL SQLStmt AS String
** Thisform.txtSearch.Value = "1 = 1; DROP TABLE Customers;"
TEXT TO m.SQLStmt NOSHOW TEXTMERGE SELECT * FROM Customers<<IIF(!EMPTY(Thisform.txtSearch.Value), " WHERE Name LIKE ?('%' + Thisform.txtSearch.Value + '%')", "")>>; ENDTEXT
** or
TEXT TO m.SQLStmt NOSHOW TEXTMERGE SELECT * FROM Customers<<IIF(!EMPTY(Thisform.txtSearch.Value), " WHERE Name = ?Thisform.txtSearch.Value", "")>>; ENDTEXT
Unless there is at least one customer with a name that starts or contains "1 = 1; DROP TABLE Customers;", the resulting cursor will be empty and the Customers table perfectly usable afterwards. Otherwise, the cursor will return the "1 = 1; DROP TABLE Customers;" customers. And the Customers table will still be there.
On Fri, Jun 28, 2019 at 3:27 PM Stephen Russell srussell705@gmail.com wrote:
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta...
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT queries to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic SQL statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" + ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT
lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")
We accept and validate the selection of the CoCode by the user and then we construct the "dynamic query." I suspect your perception of a Dynamic Query is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a TEXT/ENDTEXT construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta ck/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Paul, I understand your rules. here is a trick that can help you out over the long run.
Make a new database and do all of your sprocs on there that point to the many different environments you currently work with.
Here you will have full authority to take data and munge it anyway you need without ever editing their data.
In your sprocs you can do this:
declare @Shiptemp Table( CoaId varchar(50) null ,BOLNumber varchar(50) null , IsUltra35 bit , MPRDNumber varchar(50) null <snipped> )
insert into @Shiptemp select distinct or1.t_orno CoaId , or1.t_orno BOLNumber ,case when mp.MPRD like '944%' then 1 else 0 end IsUltra35 , mp.MPRD MPRDNumber <snipped> from [ServerName].Databasename.dbo.Table
This above is how I start to manage different sets of data from 4 different systems to make a certification of analysis report that goes with every truckload we create.
The OE system does the order details. I then need to use some of those values to go into our TESTING system and get results by the lot#. Next you get the speicifications from their system and blend them with AD data to identify who is the quality manager.
All of that is done into separate Tables just like you do into cursors. You then join the datasets and send them back for printing in your report. It only requires two parameters and takes 1-5 seconds to generate depending if you need a new one or an OLD one.
This gives my vendor db(s) a safe place and I won't beat the snot out of their temp db accidentally. As the vendor makes changes to their DB in upgrades or patches I do not lose my work. In this new DB you could place your Data Warehouse as well. You can make Cubes and give users Power BI to start making sense of their data without coming to you for a new report every other day.
Looking forward you can also port this data to the cloud if you wanted because the company bought other firms.
Zero cost to your company with massive upside potential.
Safety of their data because now everything is accessed via sproc.
The various vendor systems don't need to know about any of this either. :)
On Fri, Jun 28, 2019 at 10:14 AM Paul H. Tarver paul@tpcqpc.com wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT queries to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic SQL statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and then we construct the "dynamic query." I suspect your perception of a Dynamic Query is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a TEXT/ENDTEXT construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta ck/ https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-attack/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT queries to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic SQL statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" + ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT
lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")
We accept and validate the selection of the CoCode by the user and then we construct the "dynamic query." I suspect your perception of a Dynamic Query is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a TEXT/ENDTEXT construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta ck/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
That's always been my approach to avoiding SQL injection but I thought Steve or somebody else here LONG ago had debunked that approach as still vulnerable?
On 6/28/2019 1:13 PM, Frank Cazabon wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT queries to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic SQL statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" + ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT
lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")
We accept and validate the selection of the CoCode by the user and then we construct the "dynamic query." I suspect your perception of a Dynamic Query is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a TEXT/ENDTEXT construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
I've never heard that. If you can find any references to that please let me know
Frank.
Frank Cazabon
On 28/06/2019 01:20 PM, MB Software Solutions, LLC wrote:
That's always been my approach to avoiding SQL injection but I thought Steve or somebody else here LONG ago had debunked that approach as still vulnerable?
On 6/28/2019 1:13 PM, Frank Cazabon wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT queries to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic SQL statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" + ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT
lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")
We accept and validate the selection of the CoCode by the user and then we construct the "dynamic query." I suspect your perception of a Dynamic Query is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a TEXT/ENDTEXT construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1 = 1 ; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to get a true result. 'abc <> 'cba' does the trick to create a true condition and off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon frank.cazabon@gmail.com wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT
queries
to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic
SQL
statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and then
we
construct the "dynamic query." I suspect your perception of a Dynamic
Query
is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3
of
all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to
a
table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and see
if
you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run") ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" + m.tcSQL + CRLF, "Convert DivChqs to Stars.log", 1) ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to Stars.log", 1) ENDIF ENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1 = 1 ; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to get a true result. 'abc <> 'cba' does the trick to create a true condition and off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon frank.cazabon@gmail.com wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT
queries
to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic
SQL
statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and then
we
construct the "dynamic query." I suspect your perception of a Dynamic
Query
is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3
of
all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to
a
table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and see
if
you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run")ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" + m.tcSQL
- CRLF, "Convert DivChqs to Stars.log", 1)
ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to Stars.log", 1) ENDIF ENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1
= 1
; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to
get a
true result. 'abc <> 'cba' does the trick to create a true condition
and
off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon frank.cazabon@gmail.com wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is
to
build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT
queries
to extract data and then use the results of those queries to create
data
feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic
SQL
statements I'm referring to something little like this (although most
are
far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and then
we
construct the "dynamic query." I suspect your perception of a Dynamic
Query
is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for
2/3
of
all my SQL Server usage in my new deployments. Use to have a total of
96
cores running Ent. and now seeing if we can only use 30. Having
virtual
guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the
beauty
of it for long term source code is lost on me. Say you make a change
to
a
table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and
see
if
you need to tweak the data access to include the change you just made
to
the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which
makes
sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our
customer
portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
I don't understand.
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
On 28 June 2019 16:23:38 GMT-04:00, Stephen Russell srussell705@gmail.com wrote:
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run")ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" +
m.tcSQL
- CRLF, "Convert DivChqs to Stars.log", 1)
ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to
Stars.log", 1)
ENDIFENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here]
or 1
= 1
; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations
to
get a
true result. 'abc <> 'cba' does the trick to create a true
condition
and
off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon
wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I
were an
in-house programmer for a company with full admin rights and/or
console
access to the SQL Servers, I would be tempted to always use
stored
procedures myself. However, that is NOT the world I work in. My
job is
to
build interfaces to move data between different systems. I am
usually
provided with READ-ONLY SQL credentials so I can then issue
SELECT
queries
to extract data and then use the results of those queries to
create
data
feeds into other systems.
Our systems pull data in one direction only and when I describe
dynamic
SQL
statements I'm referring to something little like this (although
most
are
far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user
and then
we
construct the "dynamic query." I suspect your perception of a
Dynamic
Query
is greatly different than mine. The point of my original comment
was to
praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf
Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard
for
2/3
of
all my SQL Server usage in my new deployments. Use to have a
total of
96
cores running Ent. and now seeing if we can only use 30. Having
virtual
guests instead of a single bad ass box makes this a lot easier to
do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see
the
beauty
of it for long term source code is lost on me. Say you make a
change
to
a
table. You can easily find every sproc that referenced that
table with
this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used
and
see
if
you need to tweak the data access to include the change you just
made
to
the column.
We just got handed an oh by the way that hits a major focus on
how we
track
sales. We use to give all sales to the plant that made them,
which
makes
sense. Over time we have created warehouses in areas of the
country to
hold
product for delivery to a customer rich area. Sure the ERP
already did
this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our
customer
portal. We have to identify over 1000 sprocs to validate that
nothing
needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the
db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote: > Give me a little credit for being a better programmer than
that.
C'mon, Paul -- it's mega-million$ $teve we're talking about
here. Mr.
Deep Pockets with SQL Server blinders on usually with only
Stored
Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus
software.
[excessive quoting removed by server]
Stephen,
just in case you missed my question:
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
Frank.
Frank Cazabon
On 28/06/2019 04:23 PM, Stephen Russell wrote:
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run")ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" + m.tcSQL
- CRLF, "Convert DivChqs to Stars.log", 1)
ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to Stars.log", 1) ENDIF ENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1
= 1
; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to
get a
true result. 'abc <> 'cba' does the trick to create a true condition
and
off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon frank.cazabon@gmail.com wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is
to
build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT
queries
to extract data and then use the results of those queries to create
data
feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic
SQL
statements I'm referring to something little like this (although most
are
far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and then
we
construct the "dynamic query." I suspect your perception of a Dynamic
Query
is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for
2/3
of
all my SQL Server usage in my new deployments. Use to have a total of
96
cores running Ent. and now seeing if we can only use 30. Having
virtual
guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the
beauty
of it for long term source code is lost on me. Say you make a change
to
a
table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and
see
if
you need to tweak the data access to include the change you just made
to
the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which
makes
sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our
customer
portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote: > Give me a little credit for being a better programmer than that. C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Actually, in the textbox of your form, you would put it there. ' or 1 = 1; Drop table deleteMe ; --
The closing quote mark, the Or condition with the semicolon. Next statement is simple to delete a table in the database with another semicolon. Then put in 2 minus signs to comment out the rest of the code that you thought was going to operate.
On Mon, Jul 1, 2019 at 1:05 PM Frank Cazabon frank.cazabon@gmail.com wrote:
Stephen,
just in case you missed my question:
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
Frank.
Frank Cazabon
On 28/06/2019 04:23 PM, Stephen Russell wrote:
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run")ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" + m.tcSQL
- CRLF, "Convert DivChqs to Stars.log", 1)
ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to Stars.log",
ENDIFENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1
= 1
; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to
get a
true result. 'abc <> 'cba' does the trick to create a true condition
and
off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon <
frank.cazabon@gmail.com>
wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or
console
access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job
is
to
build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT
queries
to extract data and then use the results of those queries to create
data
feeds into other systems.
Our systems pull data in one direction only and when I describe
dynamic
SQL
statements I'm referring to something little like this (although most
are
far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and
then
we
construct the "dynamic query." I suspect your perception of a Dynamic
Query
is greatly different than mine. The point of my original comment was
to
praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for
2/3
of
all my SQL Server usage in my new deployments. Use to have a total
of
96
cores running Ent. and now seeing if we can only use 30. Having
virtual
guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the
beauty
of it for long term source code is lost on me. Say you make a change
to
a
table. You can easily find every sproc that referenced that table
with
this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and
see
if
you need to tweak the data access to include the change you just made
to
the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which
makes
sense. Over time we have created warehouses in areas of the country
to
hold
product for delivery to a customer rich area. Sure the ERP already
did
this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our
customer
portal. We have to identify over 1000 sprocs to validate that
nothing
needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
> On 6/27/2019 6:39 PM, Paul H. Tarver wrote: >> Give me a little credit for being a better programmer than that. > C'mon, Paul -- it's mega-million$ $teve we're talking about here.
Mr.
> Deep Pockets with SQL Server blinders on usually with only Stored > Procedures being the only viable safe option. > > lol > > > > --- > This email has been checked for viruses by Avast antivirus software. > https://www.avast.com/antivirus > >
[excessive quoting removed by server]
OK, my code is just simulating what would have been entered in the textbox.
So I changed it to this:
m.CompanyID = "' or 1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID" TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk")
It ran with no unexpected result. The deleteme table is still in the database. What are you expecting to happen?
Maybe you can take my code and adjust it to show what the issue is?
Frank.
Frank Cazabon
On 01/07/2019 02:40 PM, Stephen Russell wrote:
Actually, in the textbox of your form, you would put it there. ' or 1 = 1; Drop table deleteMe ; --
The closing quote mark, the Or condition with the semicolon. Next statement is simple to delete a table in the database with another semicolon. Then put in 2 minus signs to comment out the rest of the code that you thought was going to operate.
On Mon, Jul 1, 2019 at 1:05 PM Frank Cazabon frank.cazabon@gmail.com wrote:
Stephen,
just in case you missed my question:
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
Frank.
Frank Cazabon
On 28/06/2019 04:23 PM, Stephen Russell wrote:
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run")ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" + m.tcSQL
- CRLF, "Convert DivChqs to Stars.log", 1)
ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to Stars.log",
ENDIFENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1
= 1
; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to
get a
true result. 'abc <> 'cba' does the trick to create a true condition
and
off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon <
frank.cazabon@gmail.com>
wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote: > I've never doubted the benefits of stored procedures and if I were an > in-house programmer for a company with full admin rights and/or
console
> access to the SQL Servers, I would be tempted to always use stored > procedures myself. However, that is NOT the world I work in. My job
is
to
> build interfaces to move data between different systems. I am usually > provided with READ-ONLY SQL credentials so I can then issue SELECT queries > to extract data and then use the results of those queries to create
data
> feeds into other systems. > > Our systems pull data in one direction only and when I describe
dynamic
SQL > statements I'm referring to something little like this (although most
are
> far more complicated queries with lots of moving parts): > > lcWhereClause = "WHERE emp.CpnyID = '" + > ALLTRIM(thisform.CoCode.value) > > TEXT TO lcSQLCmd TEXTMERGE NOSHOW > SELECT > CAST(emp.CpnyID AS CHAR(20)) AS compid, > CAST(emp.EmpId AS CHAR(20)) AS emplid, > emp.NameFirst as fname, > emp.NameMiddle as mname, > emp.NameLast as lname, > emp.StrtDate as hire_date > FROM dbo.Employee emp > <<lcWhereClause>> > ENDTEXT > > lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList") > > We accept and validate the selection of the CoCode by the user and
then
we > construct the "dynamic query." I suspect your perception of a Dynamic Query > is greatly different than mine. The point of my original comment was
to
> praise the ease with which I can construct SQL statements in a TEXT/ENDTEXT > construct and I think this example shows that > > Thanks! > > Paul H. Tarver > > > -----Original Message----- > From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen > Russell > Sent: Friday, June 28, 2019 9:27 AM > To: profoxtech@leafe.com > Subject: Re: [NF] What would you miss from VFP, when migrating > > I am backing off of licenses for SQL Enterprise down to Standard for
2/3
of > all my SQL Server usage in my new deployments. Use to have a total
of
96
> cores running Ent. and now seeing if we can only use 30. Having
virtual
> guests instead of a single bad ass box makes this a lot easier to do. > > Dynamic SQL can burn you. > >
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
> ck/ > > > Making a stored procedure is common sense. Why you cannot see the
beauty
> of it for long term source code is lost on me. Say you make a change
to
a > table. You can easily find every sproc that referenced that table
with
> this statement and miss all that you have fixed: > declare @text varchar(50) > , @stringtosearch varchar(100) > , @comment varchar(150) > > > set @text = 'Warehouse' > set @comment ='%WarehouseChange fixed%' > > SET @stringtosearch = '%' +@text + '%' > > SELECT Distinct SO.Name > FROM sysobjects SO (NOLOCK) > INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID > AND SO.Type = 'P' > AND SC.Text LIKE @stringtosearch > and SO.id not in > (select distinct SO1.ID > FROM sysobjects SO1 (NOLOCK) > INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID > AND SO1.Type = 'P' > AND SC1.Text LIKE @comment) > > ORDER BY SO.Name > > You can then cross reference every place that the table was used and
see
if > you need to tweak the data access to include the change you just made
to
> the column. > > We just got handed an oh by the way that hits a major focus on how we track > sales. We use to give all sales to the plant that made them, which
makes
> sense. Over time we have created warehouses in areas of the country
to
hold > product for delivery to a customer rich area. Sure the ERP already
did
> this but the early reporting team never saw that value. > > All of these changes are only in our BI/reporting system or our
customer
> portal. We have to identify over 1000 sprocs to validate that
nothing
> needs to be done here and only 150 really need to be altered. > > How would you find that in your prgs? I use the power of the db engine > to do a lot of things like this for me. > > > On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < > mbsoftwaresolutions@mbsoftwaresolutions.com> wrote: > >> On 6/27/2019 6:39 PM, Paul H. Tarver wrote: >>> Give me a little credit for being a better programmer than that. >> C'mon, Paul -- it's mega-million$ $teve we're talking about here.
Mr.
>> Deep Pockets with SQL Server blinders on usually with only Stored >> Procedures being the only viable safe option. >> >> lol >> >> >> >> --- >> This email has been checked for viruses by Avast antivirus software. >> https://www.avast.com/antivirus >> >>
[excessive quoting removed by server]
Maybe this was back in VFP6 days, when _Stephen last worked in VFP regularly. LOL
<gd&r>
On 7/1/2019 3:19 PM, Frank Cazabon wrote:
OK, my code is just simulating what would have been entered in the textbox.
So I changed it to this:
m.CompanyID = "' or 1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk")
It ran with no unexpected result. The deleteme table is still in the database. What are you expecting to happen?
Maybe you can take my code and adjust it to show what the issue is?
Frank.
Frank Cazabon
On 01/07/2019 02:40 PM, Stephen Russell wrote:
Actually, in the textbox of your form, you would put it there. ' or 1 = 1; Drop table deleteMe ; --
The closing quote mark, the Or condition with the semicolon. Next statement is simple to delete a table in the database with another semicolon. Then put in 2 minus signs to comment out the rest of the code that you thought was going to operate.
On Mon, Jul 1, 2019 at 1:05 PM Frank Cazabon frank.cazabon@gmail.com wrote:
Stephen,
just in case you missed my question:
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
Frank.
Frank Cazabon
On 28/06/2019 04:23 PM, Stephen Russell wrote:
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run") ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" + m.tcSQL
- CRLF, "Convert DivChqs to Stars.log", 1)
ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to Stars.log",
ENDIF ENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1
= 1
; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to
get a
true result. 'abc <> 'cba' does the trick to create a true condition
and
off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon <
frank.cazabon@gmail.com>
wrote:
> To make your code safer, ensure you use parameters: > > m.CompanyID = ALLTRIM(thisform.CoCode.value) > m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" > > Frank. > > Frank Cazabon > > On 28/06/2019 11:14 AM, Paul H. Tarver wrote: >> I've never doubted the benefits of stored procedures and if I >> were an >> in-house programmer for a company with full admin rights and/or
console
>> access to the SQL Servers, I would be tempted to always use stored >> procedures myself. However, that is NOT the world I work in. My >> job
is
to
>> build interfaces to move data between different systems. I am >> usually >> provided with READ-ONLY SQL credentials so I can then issue SELECT > queries >> to extract data and then use the results of those queries to >> create
data
>> feeds into other systems. >> >> Our systems pull data in one direction only and when I describe
dynamic
> SQL >> statements I'm referring to something little like this >> (although most
are
>> far more complicated queries with lots of moving parts): >> >> lcWhereClause = "WHERE emp.CpnyID = '" + >> ALLTRIM(thisform.CoCode.value) >> >> TEXT TO lcSQLCmd TEXTMERGE NOSHOW >> SELECT >> CAST(emp.CpnyID AS CHAR(20)) AS compid, >> CAST(emp.EmpId AS CHAR(20)) AS emplid, >> emp.NameFirst as fname, >> emp.NameMiddle as mname, >> emp.NameLast as lname, >> emp.StrtDate as hire_date >> FROM dbo.Employee emp >> <<lcWhereClause>> >> ENDTEXT >> >> lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList") >> >> We accept and validate the selection of the CoCode by the user and
then
> we >> construct the "dynamic query." I suspect your perception of a >> Dynamic > Query >> is greatly different than mine. The point of my original >> comment was
to
>> praise the ease with which I can construct SQL statements in a > TEXT/ENDTEXT >> construct and I think this example shows that >> >> Thanks! >> >> Paul H. Tarver >> >> >> -----Original Message----- >> From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On >> Behalf Of > Stephen >> Russell >> Sent: Friday, June 28, 2019 9:27 AM >> To: profoxtech@leafe.com >> Subject: Re: [NF] What would you miss from VFP, when migrating >> >> I am backing off of licenses for SQL Enterprise down to >> Standard for
2/3
> of >> all my SQL Server usage in my new deployments. Use to have a >> total
of
96
>> cores running Ent. and now seeing if we can only use 30. Having
virtual
>> guests instead of a single bad ass box makes this a lot easier >> to do. >> >> Dynamic SQL can burn you. >> >>
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
>> ck/ >> >> >> Making a stored procedure is common sense. Why you cannot see the
beauty
>> of it for long term source code is lost on me. Say you make a >> change
to
> a >> table. You can easily find every sproc that referenced that table
with
>> this statement and miss all that you have fixed: >> declare @text varchar(50) >> , @stringtosearch varchar(100) >> , @comment varchar(150) >> >> >> set @text = 'Warehouse' >> set @comment ='%WarehouseChange fixed%' >> >> SET @stringtosearch = '%' +@text + '%' >> >> SELECT Distinct SO.Name >> FROM sysobjects SO (NOLOCK) >> INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID >> AND SO.Type = 'P' >> AND SC.Text LIKE @stringtosearch >> and SO.id not in >> (select distinct SO1.ID >> FROM sysobjects SO1 (NOLOCK) >> INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID >> AND SO1.Type = 'P' >> AND SC1.Text LIKE @comment) >> >> ORDER BY SO.Name >> >> You can then cross reference every place that the table was >> used and
see
> if >> you need to tweak the data access to include the change you >> just made
to
>> the column. >> >> We just got handed an oh by the way that hits a major focus on >> how we > track >> sales. We use to give all sales to the plant that made them, >> which
makes
>> sense. Over time we have created warehouses in areas of the >> country
to
> hold >> product for delivery to a customer rich area. Sure the ERP >> already
did
>> this but the early reporting team never saw that value. >> >> All of these changes are only in our BI/reporting system or our
customer
>> portal. We have to identify over 1000 sprocs to validate that
nothing
>> needs to be done here and only 150 really need to be altered. >> >> How would you find that in your prgs? I use the power of >> the db > engine >> to do a lot of things like this for me. >> >> >> On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < >> mbsoftwaresolutions@mbsoftwaresolutions.com> wrote: >> >>> On 6/27/2019 6:39 PM, Paul H. Tarver wrote: >>>> Give me a little credit for being a better programmer than that. >>> C'mon, Paul -- it's mega-million$ $teve we're talking about here.
Mr.
>>> Deep Pockets with SQL Server blinders on usually with only Stored >>> Procedures being the only viable safe option. >>> >>> lol >>> >>> >>> >>> --- >>> This email has been checked for viruses by Avast antivirus >>> software. >>> https://www.avast.com/antivirus >>> >>>
[excessive quoting removed by server]
25 years ago as a guess. I probably didn't use the ? operator.
On Mon, Jul 1, 2019 at 2:39 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Maybe this was back in VFP6 days, when _Stephen last worked in VFP regularly. LOL
<gd&r>
On 7/1/2019 3:19 PM, Frank Cazabon wrote:
OK, my code is just simulating what would have been entered in the textbox.
So I changed it to this:
m.CompanyID = "' or 1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID" TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk")It ran with no unexpected result. The deleteme table is still in the database. What are you expecting to happen?
Maybe you can take my code and adjust it to show what the issue is?
Frank.
Frank Cazabon
On 01/07/2019 02:40 PM, Stephen Russell wrote:
Actually, in the textbox of your form, you would put it there. ' or 1 = 1; Drop table deleteMe ; --
The closing quote mark, the Or condition with the semicolon. Next statement is simple to delete a table in the database with another semicolon. Then put in 2 minus signs to comment out the rest of the code that you thought was going to operate.
On Mon, Jul 1, 2019 at 1:05 PM Frank Cazabon frank.cazabon@gmail.com wrote:
Stephen,
just in case you missed my question:
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
Frank.
Frank Cazabon
On 28/06/2019 04:23 PM, Stephen Russell wrote:
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
I created a database SQL Server called junk and added a table called deleteme with one column called test nchar(10).
I ran the code below and the deleteme table is still there. Did I do what you wanted or have I misinterpreted your request?
TEXT TO m.lcConnectionString NOSHOW TEXTMERGE DRIVER=SQL Server Native Client 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes here>>;Application Name=JunkTest ENDTEXT
LOCAL m.lnHandle
lnDispLogin = SQLGETPROP(0,"DispLogin") SQLSETPROP(0,"DispLogin",3) &&& never m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) SQLSETPROP(0,"DispLogin",lnDispLogin) IF m.lnHandle > 0 m.CompanyID = "1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID"
TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") SQLDISCONNECT(m.lnHandle) MESSAGEBOX(m.lcSQL + " has run")ELSE MESSAGEBOX("Unable to connect") ENDIF
FUNCTION RunSQL LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor
LOCAL m.llSuccess m.llSuccess = .T.
IF TYPE("m.tcMessage") = "L" m.tcMessage = "" ENDIF
IF TYPE("m.tuCursor") = "L" m.tuCursor = "" ENDIF
m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 IF NOT m.llSuccess AERROR(laError) SET STEP ON STRTOFILE("Error: " + laError[2] + " Unable to execute:" + m.tcSQL
- CRLF, "Convert DivChqs to Stars.log", 1)
ELSE IF NOT EMPTY(m.tcMessage) STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to Stars.log",
ENDIFENDIF RETURN m.llSuccess
Frank.
Frank Cazabon
On 28/06/2019 02:11 PM, Stephen Russell wrote: > This looks like a great test for Text EndText! > > create a table deleteMe > > In the form put text like this: [any value for a customer > here] or 1 = 1 > ; drop table deleteMe ; -- > > m.CompanyID = ALLTRIM(thisform.CoCode.value) > m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" > > What do you see in the entire statement you put together? > > If you run it against a SQL box does your table disappear? > > To get around 1 = 1 you could have a TON of different > combinations to get a > true result. 'abc <> 'cba' does the trick to create a true > condition and > off it goes. > > > > > > > > > On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon <
frank.cazabon@gmail.com>
> wrote: > >> To make your code safer, ensure you use parameters: >> >> m.CompanyID = ALLTRIM(thisform.CoCode.value) >> m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" >> >> Frank. >> >> Frank Cazabon >> >> On 28/06/2019 11:14 AM, Paul H. Tarver wrote: >>> I've never doubted the benefits of stored procedures and if I >>> were an >>> in-house programmer for a company with full admin rights and/or
console
>>> access to the SQL Servers, I would be tempted to always use stored >>> procedures myself. However, that is NOT the world I work in. My >>> job
is
to >>> build interfaces to move data between different systems. I am >>> usually >>> provided with READ-ONLY SQL credentials so I can then issue SELECT >> queries >>> to extract data and then use the results of those queries to >>> create data >>> feeds into other systems. >>> >>> Our systems pull data in one direction only and when I describe
dynamic
>> SQL >>> statements I'm referring to something little like this >>> (although most are >>> far more complicated queries with lots of moving parts): >>> >>> lcWhereClause = "WHERE emp.CpnyID = '" + >>> ALLTRIM(thisform.CoCode.value) >>> >>> TEXT TO lcSQLCmd TEXTMERGE NOSHOW >>> SELECT >>> CAST(emp.CpnyID AS CHAR(20)) AS compid, >>> CAST(emp.EmpId AS CHAR(20)) AS emplid, >>> emp.NameFirst as fname, >>> emp.NameMiddle as mname, >>> emp.NameLast as lname, >>> emp.StrtDate as hire_date >>> FROM dbo.Employee emp >>> <<lcWhereClause>> >>> ENDTEXT >>> >>> lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList") >>> >>> We accept and validate the selection of the CoCode by the user and
then
>> we >>> construct the "dynamic query." I suspect your perception of a >>> Dynamic >> Query >>> is greatly different than mine. The point of my original >>> comment was
to
>>> praise the ease with which I can construct SQL statements in a >> TEXT/ENDTEXT >>> construct and I think this example shows that >>> >>> Thanks! >>> >>> Paul H. Tarver >>> >>> >>> -----Original Message----- >>> From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On >>> Behalf Of >> Stephen >>> Russell >>> Sent: Friday, June 28, 2019 9:27 AM >>> To: profoxtech@leafe.com >>> Subject: Re: [NF] What would you miss from VFP, when migrating >>> >>> I am backing off of licenses for SQL Enterprise down to >>> Standard for 2/3 >> of >>> all my SQL Server usage in my new deployments. Use to have a >>> total
of
96 >>> cores running Ent. and now seeing if we can only use 30. Having virtual >>> guests instead of a single bad ass box makes this a lot easier >>> to do. >>> >>> Dynamic SQL can burn you. >>> >>>
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
>>> ck/ >>> >>> >>> Making a stored procedure is common sense. Why you cannot see the beauty >>> of it for long term source code is lost on me. Say you make a >>> change to >> a >>> table. You can easily find every sproc that referenced that table
with
>>> this statement and miss all that you have fixed: >>> declare @text varchar(50) >>> , @stringtosearch varchar(100) >>> , @comment varchar(150) >>> >>> >>> set @text = 'Warehouse' >>> set @comment ='%WarehouseChange fixed%' >>> >>> SET @stringtosearch = '%' +@text + '%' >>> >>> SELECT Distinct SO.Name >>> FROM sysobjects SO (NOLOCK) >>> INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID >>> AND SO.Type = 'P' >>> AND SC.Text LIKE @stringtosearch >>> and SO.id not in >>> (select distinct SO1.ID >>> FROM sysobjects SO1 (NOLOCK) >>> INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID >>> AND SO1.Type = 'P' >>> AND SC1.Text LIKE @comment) >>> >>> ORDER BY SO.Name >>> >>> You can then cross reference every place that the table was >>> used and see >> if >>> you need to tweak the data access to include the change you >>> just made to >>> the column. >>> >>> We just got handed an oh by the way that hits a major focus on >>> how we >> track >>> sales. We use to give all sales to the plant that made them, >>> which makes >>> sense. Over time we have created warehouses in areas of the >>> country
to
>> hold >>> product for delivery to a customer rich area. Sure the ERP >>> already
did
>>> this but the early reporting team never saw that value. >>> >>> All of these changes are only in our BI/reporting system or our customer >>> portal. We have to identify over 1000 sprocs to validate that
nothing
>>> needs to be done here and only 150 really need to be altered. >>> >>> How would you find that in your prgs? I use the power of >>> the db >> engine >>> to do a lot of things like this for me. >>> >>> >>> On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < >>> mbsoftwaresolutions@mbsoftwaresolutions.com> wrote: >>> >>>> On 6/27/2019 6:39 PM, Paul H. Tarver wrote: >>>>> Give me a little credit for being a better programmer than that. >>>> C'mon, Paul -- it's mega-million$ $teve we're talking about here.
Mr.
>>>> Deep Pockets with SQL Server blinders on usually with only Stored >>>> Procedures being the only viable safe option. >>>> >>>> lol >>>> >>>> >>>> >>>> --- >>>> This email has been checked for viruses by Avast antivirus >>>> software. >>>> https://www.avast.com/antivirus >>>> >>>>
[excessive quoting removed by server]
Ah, I was concerned that all these years of me using parameters for safety you had some proof that they were unsafe.
Frank.
Frank Cazabon
On 01/07/2019 03:54 PM, Stephen Russell wrote:
25 years ago as a guess. I probably didn't use the ? operator.
On Mon, Jul 1, 2019 at 2:39 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Maybe this was back in VFP6 days, when _Stephen last worked in VFP regularly. LOL
<gd&r>
On 7/1/2019 3:19 PM, Frank Cazabon wrote:
OK, my code is just simulating what would have been entered in the textbox.
So I changed it to this:
m.CompanyID = "' or 1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID" TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk")It ran with no unexpected result. The deleteme table is still in the database. What are you expecting to happen?
Maybe you can take my code and adjust it to show what the issue is?
Frank.
Frank Cazabon
On 01/07/2019 02:40 PM, Stephen Russell wrote:
Actually, in the textbox of your form, you would put it there. ' or 1 = 1; Drop table deleteMe ; --
The closing quote mark, the Or condition with the semicolon. Next statement is simple to delete a table in the database with another semicolon. Then put in 2 minus signs to comment out the rest of the code that you thought was going to operate.
On Mon, Jul 1, 2019 at 1:05 PM Frank Cazabon frank.cazabon@gmail.com wrote:
Stephen,
just in case you missed my question:
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
Frank.
Frank Cazabon
On 28/06/2019 04:23 PM, Stephen Russell wrote:
I believe that you needed an OR
"1 = 1; drop table deleteMe ; --"
" or 1 = 1; drop table deleteMe ; --"
On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon frank.cazabon@gmail.com wrote:
> I created a database SQL Server called junk and added a table called > deleteme with one column called test nchar(10). > > I ran the code below and the deleteme table is still there. Did I do > what you wanted or have I misinterpreted your request? > > TEXT TO m.lcConnectionString NOSHOW TEXTMERGE > DRIVER=SQL Server Native Client > 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes > here>>;Application Name=JunkTest > ENDTEXT > > LOCAL m.lnHandle > > lnDispLogin = SQLGETPROP(0,"DispLogin") > SQLSETPROP(0,"DispLogin",3) &&& never > m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) > SQLSETPROP(0,"DispLogin",lnDispLogin) > IF m.lnHandle > 0 > m.CompanyID = "1 = 1; drop table deleteMe ; --" > m.lcWhereClause = "WHERE test = ?m.CompanyID" > > TEXT TO m.lcSQL NOSHOW TEXTMERGE > SELECT * > FROM deleteme > <<m.lcWhereClause>> > ENDTEXT > m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") > SQLDISCONNECT(m.lnHandle) > MESSAGEBOX(m.lcSQL + " has run") > ELSE > MESSAGEBOX("Unable to connect") > ENDIF > > > FUNCTION RunSQL > LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor > > LOCAL m.llSuccess > m.llSuccess = .T. > > IF TYPE("m.tcMessage") = "L" > m.tcMessage = "" > ENDIF > > IF TYPE("m.tuCursor") = "L" > m.tuCursor = "" > ENDIF > > m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 > IF NOT m.llSuccess > AERROR(laError) > SET STEP ON > STRTOFILE("Error: " + laError[2] + " Unable to execute:" + > m.tcSQL > + CRLF, "Convert DivChqs to Stars.log", 1) > ELSE > IF NOT EMPTY(m.tcMessage) > STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to > Stars.log",
> ENDIF > ENDIF > RETURN m.llSuccess > > > Frank. > > Frank Cazabon > > On 28/06/2019 02:11 PM, Stephen Russell wrote: >> This looks like a great test for Text EndText! >> >> create a table deleteMe >> >> In the form put text like this: [any value for a customer >> here] or 1 > = 1 >> ; drop table deleteMe ; -- >> >> m.CompanyID = ALLTRIM(thisform.CoCode.value) >> m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" >> >> What do you see in the entire statement you put together? >> >> If you run it against a SQL box does your table disappear? >> >> To get around 1 = 1 you could have a TON of different >> combinations to > get a >> true result. 'abc <> 'cba' does the trick to create a true >> condition > and >> off it goes. >> >> >> >> >> >> >> >> >> On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon <
frank.cazabon@gmail.com>
>> wrote: >> >>> To make your code safer, ensure you use parameters: >>> >>> m.CompanyID = ALLTRIM(thisform.CoCode.value) >>> m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" >>> >>> Frank. >>> >>> Frank Cazabon >>> >>> On 28/06/2019 11:14 AM, Paul H. Tarver wrote: >>>> I've never doubted the benefits of stored procedures and if I >>>> were an >>>> in-house programmer for a company with full admin rights and/or
console
>>>> access to the SQL Servers, I would be tempted to always use stored >>>> procedures myself. However, that is NOT the world I work in. My >>>> job
is
> to >>>> build interfaces to move data between different systems. I am >>>> usually >>>> provided with READ-ONLY SQL credentials so I can then issue SELECT >>> queries >>>> to extract data and then use the results of those queries to >>>> create > data >>>> feeds into other systems. >>>> >>>> Our systems pull data in one direction only and when I describe
dynamic
>>> SQL >>>> statements I'm referring to something little like this >>>> (although most > are >>>> far more complicated queries with lots of moving parts): >>>> >>>> lcWhereClause = "WHERE emp.CpnyID = '" + >>>> ALLTRIM(thisform.CoCode.value) >>>> >>>> TEXT TO lcSQLCmd TEXTMERGE NOSHOW >>>> SELECT >>>> CAST(emp.CpnyID AS CHAR(20)) AS compid, >>>> CAST(emp.EmpId AS CHAR(20)) AS emplid, >>>> emp.NameFirst as fname, >>>> emp.NameMiddle as mname, >>>> emp.NameLast as lname, >>>> emp.StrtDate as hire_date >>>> FROM dbo.Employee emp >>>> <<lcWhereClause>> >>>> ENDTEXT >>>> >>>> lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList") >>>> >>>> We accept and validate the selection of the CoCode by the user and
then
>>> we >>>> construct the "dynamic query." I suspect your perception of a >>>> Dynamic >>> Query >>>> is greatly different than mine. The point of my original >>>> comment was
to
>>>> praise the ease with which I can construct SQL statements in a >>> TEXT/ENDTEXT >>>> construct and I think this example shows that >>>> >>>> Thanks! >>>> >>>> Paul H. Tarver >>>> >>>> >>>> -----Original Message----- >>>> From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On >>>> Behalf Of >>> Stephen >>>> Russell >>>> Sent: Friday, June 28, 2019 9:27 AM >>>> To: profoxtech@leafe.com >>>> Subject: Re: [NF] What would you miss from VFP, when migrating >>>> >>>> I am backing off of licenses for SQL Enterprise down to >>>> Standard for > 2/3 >>> of >>>> all my SQL Server usage in my new deployments. Use to have a >>>> total
of
> 96 >>>> cores running Ent. and now seeing if we can only use 30. Having > virtual >>>> guests instead of a single bad ass box makes this a lot easier >>>> to do. >>>> >>>> Dynamic SQL can burn you. >>>> >>>>
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
>>>> ck/ >>>> >>>> >>>> Making a stored procedure is common sense. Why you cannot see the > beauty >>>> of it for long term source code is lost on me. Say you make a >>>> change > to >>> a >>>> table. You can easily find every sproc that referenced that table
with
>>>> this statement and miss all that you have fixed: >>>> declare @text varchar(50) >>>> , @stringtosearch varchar(100) >>>> , @comment varchar(150) >>>> >>>> >>>> set @text = 'Warehouse' >>>> set @comment ='%WarehouseChange fixed%' >>>> >>>> SET @stringtosearch = '%' +@text + '%' >>>> >>>> SELECT Distinct SO.Name >>>> FROM sysobjects SO (NOLOCK) >>>> INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID >>>> AND SO.Type = 'P' >>>> AND SC.Text LIKE @stringtosearch >>>> and SO.id not in >>>> (select distinct SO1.ID >>>> FROM sysobjects SO1 (NOLOCK) >>>> INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID >>>> AND SO1.Type = 'P' >>>> AND SC1.Text LIKE @comment) >>>> >>>> ORDER BY SO.Name >>>> >>>> You can then cross reference every place that the table was >>>> used and > see >>> if >>>> you need to tweak the data access to include the change you >>>> just made > to >>>> the column. >>>> >>>> We just got handed an oh by the way that hits a major focus on >>>> how we >>> track >>>> sales. We use to give all sales to the plant that made them, >>>> which > makes >>>> sense. Over time we have created warehouses in areas of the >>>> country
to
>>> hold >>>> product for delivery to a customer rich area. Sure the ERP >>>> already
did
>>>> this but the early reporting team never saw that value. >>>> >>>> All of these changes are only in our BI/reporting system or our > customer >>>> portal. We have to identify over 1000 sprocs to validate that
nothing
>>>> needs to be done here and only 150 really need to be altered. >>>> >>>> How would you find that in your prgs? I use the power of >>>> the db >>> engine >>>> to do a lot of things like this for me. >>>> >>>> >>>> On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < >>>> mbsoftwaresolutions@mbsoftwaresolutions.com> wrote: >>>> >>>>> On 6/27/2019 6:39 PM, Paul H. Tarver wrote: >>>>>> Give me a little credit for being a better programmer than that. >>>>> C'mon, Paul -- it's mega-million$ $teve we're talking about here.
Mr.
>>>>> Deep Pockets with SQL Server blinders on usually with only Stored >>>>> Procedures being the only viable safe option. >>>>> >>>>> lol >>>>> >>>>> >>>>> >>>>> --- >>>>> This email has been checked for viruses by Avast antivirus >>>>> software. >>>>> https://www.avast.com/antivirus >>>>> >>>>>
[excessive quoting removed by server]
It takes some testing. I presented the first easiest hack and I may have said use the opening ' because I thought that you were looking for text data and not INT value in that table you were querying against.
Creating a series of tests just to be sure, is helpful. In the end, when everything is safe you get to make a publicity statement that you tested for hacking activity and this first series of tests are in the customer's favor of retaining their data.
On Tue, Jul 2, 2019 at 7:07 AM Frank Cazabon frank.cazabon@gmail.com wrote:
Ah, I was concerned that all these years of me using parameters for safety you had some proof that they were unsafe.
Frank.
Frank Cazabon
On 01/07/2019 03:54 PM, Stephen Russell wrote:
25 years ago as a guess. I probably didn't use the ? operator.
On Mon, Jul 1, 2019 at 2:39 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Maybe this was back in VFP6 days, when _Stephen last worked in VFP regularly. LOL
<gd&r>
On 7/1/2019 3:19 PM, Frank Cazabon wrote:
OK, my code is just simulating what would have been entered in the textbox.
So I changed it to this:
m.CompanyID = "' or 1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID" TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk")It ran with no unexpected result. The deleteme table is still in the database. What are you expecting to happen?
Maybe you can take my code and adjust it to show what the issue is?
Frank.
Frank Cazabon
On 01/07/2019 02:40 PM, Stephen Russell wrote:
Actually, in the textbox of your form, you would put it there. ' or 1 = 1; Drop table deleteMe ; --
The closing quote mark, the Or condition with the semicolon. Next statement is simple to delete a table in the database with another semicolon. Then put in 2 minus signs to comment out the rest of the code that you thought was going to operate.
On Mon, Jul 1, 2019 at 1:05 PM Frank Cazabon <frank.cazabon@gmail.com
wrote:
Stephen,
just in case you missed my question:
Do you mean change this line:
m.CompanyID = "1 = 1; drop table deleteMe ; --"
To this:
m.CompanyID = "or 1 = 1; drop table deleteMe ; --"
Frank.
Frank Cazabon
On 28/06/2019 04:23 PM, Stephen Russell wrote: > I believe that you needed an OR > > "1 = 1; drop table deleteMe ; --" > > " or 1 = 1; drop table deleteMe ; --" > > On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon > frank.cazabon@gmail.com > wrote: > >> I created a database SQL Server called junk and added a table
called
>> deleteme with one column called test nchar(10). >> >> I ran the code below and the deleteme table is still there. Did I
do
>> what you wanted or have I misinterpreted your request? >> >> TEXT TO m.lcConnectionString NOSHOW TEXTMERGE >> DRIVER=SQL Server Native Client >> 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes >> here>>;Application Name=JunkTest >> ENDTEXT >> >> LOCAL m.lnHandle >> >> lnDispLogin = SQLGETPROP(0,"DispLogin") >> SQLSETPROP(0,"DispLogin",3) &&& never >> m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) >> SQLSETPROP(0,"DispLogin",lnDispLogin) >> IF m.lnHandle > 0 >> m.CompanyID = "1 = 1; drop table deleteMe ; --" >> m.lcWhereClause = "WHERE test = ?m.CompanyID" >> >> TEXT TO m.lcSQL NOSHOW TEXTMERGE >> SELECT * >> FROM deleteme >> <<m.lcWhereClause>> >> ENDTEXT >> m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") >> SQLDISCONNECT(m.lnHandle) >> MESSAGEBOX(m.lcSQL + " has run") >> ELSE >> MESSAGEBOX("Unable to connect") >> ENDIF >> >> >> FUNCTION RunSQL >> LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor >> >> LOCAL m.llSuccess >> m.llSuccess = .T. >> >> IF TYPE("m.tcMessage") = "L" >> m.tcMessage = "" >> ENDIF >> >> IF TYPE("m.tuCursor") = "L" >> m.tuCursor = "" >> ENDIF >> >> m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 >> IF NOT m.llSuccess >> AERROR(laError) >> SET STEP ON >> STRTOFILE("Error: " + laError[2] + " Unable to execute:" + >> m.tcSQL >> + CRLF, "Convert DivChqs to Stars.log", 1) >> ELSE >> IF NOT EMPTY(m.tcMessage) >> STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to >> Stars.log",
>> ENDIF >> ENDIF >> RETURN m.llSuccess >> >> >> Frank. >> >> Frank Cazabon >> >> On 28/06/2019 02:11 PM, Stephen Russell wrote: >>> This looks like a great test for Text EndText! >>> >>> create a table deleteMe >>> >>> In the form put text like this: [any value for a customer >>> here] or 1 >> = 1 >>> ; drop table deleteMe ; -- >>> >>> m.CompanyID = ALLTRIM(thisform.CoCode.value) >>> m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" >>> >>> What do you see in the entire statement you put together? >>> >>> If you run it against a SQL box does your table disappear? >>> >>> To get around 1 = 1 you could have a TON of different >>> combinations to >> get a >>> true result. 'abc <> 'cba' does the trick to create a true >>> condition >> and >>> off it goes. >>> >>> >>> >>> >>> >>> >>> >>> >>> On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon < frank.cazabon@gmail.com> >>> wrote: >>> >>>> To make your code safer, ensure you use parameters: >>>> >>>> m.CompanyID = ALLTRIM(thisform.CoCode.value) >>>> m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" >>>> >>>> Frank. >>>> >>>> Frank Cazabon >>>> >>>> On 28/06/2019 11:14 AM, Paul H. Tarver wrote: >>>>> I've never doubted the benefits of stored procedures and if I >>>>> were an >>>>> in-house programmer for a company with full admin rights and/or console >>>>> access to the SQL Servers, I would be tempted to always use
stored
>>>>> procedures myself. However, that is NOT the world I work in. My >>>>> job is >> to >>>>> build interfaces to move data between different systems. I am >>>>> usually >>>>> provided with READ-ONLY SQL credentials so I can then issue
SELECT
>>>> queries >>>>> to extract data and then use the results of those queries to >>>>> create >> data >>>>> feeds into other systems. >>>>> >>>>> Our systems pull data in one direction only and when I describe dynamic >>>> SQL >>>>> statements I'm referring to something little like this >>>>> (although most >> are >>>>> far more complicated queries with lots of moving parts): >>>>> >>>>> lcWhereClause = "WHERE emp.CpnyID = '" + >>>>> ALLTRIM(thisform.CoCode.value) >>>>> >>>>> TEXT TO lcSQLCmd TEXTMERGE NOSHOW >>>>> SELECT >>>>> CAST(emp.CpnyID AS CHAR(20)) AS compid, >>>>> CAST(emp.EmpId AS CHAR(20)) AS emplid, >>>>> emp.NameFirst as fname, >>>>> emp.NameMiddle as mname, >>>>> emp.NameLast as lname, >>>>> emp.StrtDate as hire_date >>>>> FROM dbo.Employee emp >>>>> <<lcWhereClause>> >>>>> ENDTEXT >>>>> >>>>> lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList") >>>>> >>>>> We accept and validate the selection of the CoCode by the user
and
then >>>> we >>>>> construct the "dynamic query." I suspect your perception of a >>>>> Dynamic >>>> Query >>>>> is greatly different than mine. The point of my original >>>>> comment was to >>>>> praise the ease with which I can construct SQL statements in a >>>> TEXT/ENDTEXT >>>>> construct and I think this example shows that >>>>> >>>>> Thanks! >>>>> >>>>> Paul H. Tarver >>>>> >>>>> >>>>> -----Original Message----- >>>>> From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On >>>>> Behalf Of >>>> Stephen >>>>> Russell >>>>> Sent: Friday, June 28, 2019 9:27 AM >>>>> To: profoxtech@leafe.com >>>>> Subject: Re: [NF] What would you miss from VFP, when migrating >>>>> >>>>> I am backing off of licenses for SQL Enterprise down to >>>>> Standard for >> 2/3 >>>> of >>>>> all my SQL Server usage in my new deployments. Use to have a >>>>> total of >> 96 >>>>> cores running Ent. and now seeing if we can only use 30. Having >> virtual >>>>> guests instead of a single bad ass box makes this a lot easier >>>>> to do. >>>>> >>>>> Dynamic SQL can burn you. >>>>> >>>>>
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
>>>>> ck/ >>>>> >>>>> >>>>> Making a stored procedure is common sense. Why you cannot see
the
>> beauty >>>>> of it for long term source code is lost on me. Say you make a >>>>> change >> to >>>> a >>>>> table. You can easily find every sproc that referenced that
table
with >>>>> this statement and miss all that you have fixed: >>>>> declare @text varchar(50) >>>>> , @stringtosearch varchar(100) >>>>> , @comment varchar(150) >>>>> >>>>> >>>>> set @text = 'Warehouse' >>>>> set @comment ='%WarehouseChange fixed%' >>>>> >>>>> SET @stringtosearch = '%' +@text + '%' >>>>> >>>>> SELECT Distinct SO.Name >>>>> FROM sysobjects SO (NOLOCK) >>>>> INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID >>>>> AND SO.Type = 'P' >>>>> AND SC.Text LIKE @stringtosearch >>>>> and SO.id not in >>>>> (select distinct SO1.ID >>>>> FROM sysobjects SO1 (NOLOCK) >>>>> INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID >>>>> AND SO1.Type = 'P' >>>>> AND SC1.Text LIKE @comment) >>>>> >>>>> ORDER BY SO.Name >>>>> >>>>> You can then cross reference every place that the table was >>>>> used and >> see >>>> if >>>>> you need to tweak the data access to include the change you >>>>> just made >> to >>>>> the column. >>>>> >>>>> We just got handed an oh by the way that hits a major focus on >>>>> how we >>>> track >>>>> sales. We use to give all sales to the plant that made them, >>>>> which >> makes >>>>> sense. Over time we have created warehouses in areas of the >>>>> country to >>>> hold >>>>> product for delivery to a customer rich area. Sure the ERP >>>>> already did >>>>> this but the early reporting team never saw that value. >>>>> >>>>> All of these changes are only in our BI/reporting system or our >> customer >>>>> portal. We have to identify over 1000 sprocs to validate that nothing >>>>> needs to be done here and only 150 really need to be altered. >>>>> >>>>> How would you find that in your prgs? I use the power of >>>>> the db >>>> engine >>>>> to do a lot of things like this for me. >>>>> >>>>> >>>>> On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < >>>>> mbsoftwaresolutions@mbsoftwaresolutions.com> wrote: >>>>> >>>>>> On 6/27/2019 6:39 PM, Paul H. Tarver wrote: >>>>>>> Give me a little credit for being a better programmer than
that.
>>>>>> C'mon, Paul -- it's mega-million$ $teve we're talking about
here.
Mr. >>>>>> Deep Pockets with SQL Server blinders on usually with only
Stored
>>>>>> Procedures being the only viable safe option. >>>>>> >>>>>> lol >>>>>> >>>>>> >>>>>> >>>>>> --- >>>>>> This email has been checked for viruses by Avast antivirus >>>>>> software. >>>>>> https://www.avast.com/antivirus >>>>>> >>>>>>
[excessive quoting removed by server]
This had started because Mike had stated:
That's always been my approach to avoiding SQL injection but I thought Steve or somebody else here LONG ago had debunked that approach as still vulnerable?
So I was concerned that the technique that has been put forward as the safest way to avoid SQL Injection (using parameters) had a problem. I thought you were trying to illustrate that problem but it appears to be some mis-communication as you were not referring to using parameters.
All the testing has been done years ago so I don't need to go over that again.
Frank.
Frank Cazabon
On 02/07/2019 09:19 AM, Stephen Russell wrote:
It takes some testing. I presented the first easiest hack and I may have said use the opening ' because I thought that you were looking for text data and not INT value in that table you were querying against.
Creating a series of tests just to be sure, is helpful. In the end, when everything is safe you get to make a publicity statement that you tested for hacking activity and this first series of tests are in the customer's favor of retaining their data.
On Tue, Jul 2, 2019 at 7:07 AM Frank Cazabon frank.cazabon@gmail.com wrote:
Ah, I was concerned that all these years of me using parameters for safety you had some proof that they were unsafe.
Frank.
Frank Cazabon
On 01/07/2019 03:54 PM, Stephen Russell wrote:
25 years ago as a guess. I probably didn't use the ? operator.
On Mon, Jul 1, 2019 at 2:39 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Maybe this was back in VFP6 days, when _Stephen last worked in VFP regularly. LOL
<gd&r>
On 7/1/2019 3:19 PM, Frank Cazabon wrote:
OK, my code is just simulating what would have been entered in the textbox.
So I changed it to this:
m.CompanyID = "' or 1 = 1; drop table deleteMe ; --" m.lcWhereClause = "WHERE test = ?m.CompanyID" TEXT TO m.lcSQL NOSHOW TEXTMERGE SELECT * FROM deleteme <<m.lcWhereClause>> ENDTEXT m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk")It ran with no unexpected result. The deleteme table is still in the database. What are you expecting to happen?
Maybe you can take my code and adjust it to show what the issue is?
Frank.
Frank Cazabon
On 01/07/2019 02:40 PM, Stephen Russell wrote:
Actually, in the textbox of your form, you would put it there. ' or 1 = 1; Drop table deleteMe ; --
The closing quote mark, the Or condition with the semicolon. Next statement is simple to delete a table in the database with another semicolon. Then put in 2 minus signs to comment out the rest of the code that you thought was going to operate.
On Mon, Jul 1, 2019 at 1:05 PM Frank Cazabon <frank.cazabon@gmail.com wrote:
> Stephen, > > just in case you missed my question: > > Do you mean change this line: > > m.CompanyID = "1 = 1; drop table deleteMe ; --" > > To this: > > m.CompanyID = "or 1 = 1; drop table deleteMe ; --" > > Frank. > > Frank Cazabon > > On 28/06/2019 04:23 PM, Stephen Russell wrote: >> I believe that you needed an OR >> >> "1 = 1; drop table deleteMe ; --" >> >> " or 1 = 1; drop table deleteMe ; --" >> >> On Fri, Jun 28, 2019 at 1:34 PM Frank Cazabon >> frank.cazabon@gmail.com >> wrote: >> >>> I created a database SQL Server called junk and added a table
called
>>> deleteme with one column called test nchar(10). >>> >>> I ran the code below and the deleteme table is still there. Did I
do
>>> what you wanted or have I misinterpreted your request? >>> >>> TEXT TO m.lcConnectionString NOSHOW TEXTMERGE >>> DRIVER=SQL Server Native Client >>> 11.0;Trusted_Connection=Yes;DATABASE=junk;SERVER=<<your server goes >>> here>>;Application Name=JunkTest >>> ENDTEXT >>> >>> LOCAL m.lnHandle >>> >>> lnDispLogin = SQLGETPROP(0,"DispLogin") >>> SQLSETPROP(0,"DispLogin",3) &&& never >>> m.lnHandle = SQLSTRINGCONNECT(m.lcConnectionString,.T.) >>> SQLSETPROP(0,"DispLogin",lnDispLogin) >>> IF m.lnHandle > 0 >>> m.CompanyID = "1 = 1; drop table deleteMe ; --" >>> m.lcWhereClause = "WHERE test = ?m.CompanyID" >>> >>> TEXT TO m.lcSQL NOSHOW TEXTMERGE >>> SELECT * >>> FROM deleteme >>> <<m.lcWhereClause>> >>> ENDTEXT >>> m.llSuccess = RunSQL(m.lnHandle, m.lcSQL, "", "c_junk") >>> SQLDISCONNECT(m.lnHandle) >>> MESSAGEBOX(m.lcSQL + " has run") >>> ELSE >>> MESSAGEBOX("Unable to connect") >>> ENDIF >>> >>> >>> FUNCTION RunSQL >>> LPARAMETERS tnHandle, tcSQL, tcMessage, tuCursor >>> >>> LOCAL m.llSuccess >>> m.llSuccess = .T. >>> >>> IF TYPE("m.tcMessage") = "L" >>> m.tcMessage = "" >>> ENDIF >>> >>> IF TYPE("m.tuCursor") = "L" >>> m.tuCursor = "" >>> ENDIF >>> >>> m.llSuccess = SQLEXEC(m.tnHandle, m.tcSQL, m.tuCursor) > 0 >>> IF NOT m.llSuccess >>> AERROR(laError) >>> SET STEP ON >>> STRTOFILE("Error: " + laError[2] + " Unable to execute:" + >>> m.tcSQL >>> + CRLF, "Convert DivChqs to Stars.log", 1) >>> ELSE >>> IF NOT EMPTY(m.tcMessage) >>> STRTOFILE(m.tcMessage + CRLF, "Convert DivChqs to >>> Stars.log", > 1) >>> ENDIF >>> ENDIF >>> RETURN m.llSuccess >>> >>> >>> Frank. >>> >>> Frank Cazabon >>> >>> On 28/06/2019 02:11 PM, Stephen Russell wrote: >>>> This looks like a great test for Text EndText! >>>> >>>> create a table deleteMe >>>> >>>> In the form put text like this: [any value for a customer >>>> here] or 1 >>> = 1 >>>> ; drop table deleteMe ; -- >>>> >>>> m.CompanyID = ALLTRIM(thisform.CoCode.value) >>>> m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" >>>> >>>> What do you see in the entire statement you put together? >>>> >>>> If you run it against a SQL box does your table disappear? >>>> >>>> To get around 1 = 1 you could have a TON of different >>>> combinations to >>> get a >>>> true result. 'abc <> 'cba' does the trick to create a true >>>> condition >>> and >>>> off it goes. >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon < > frank.cazabon@gmail.com> >>>> wrote: >>>> >>>>> To make your code safer, ensure you use parameters: >>>>> >>>>> m.CompanyID = ALLTRIM(thisform.CoCode.value) >>>>> m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID" >>>>> >>>>> Frank. >>>>> >>>>> Frank Cazabon >>>>> >>>>> On 28/06/2019 11:14 AM, Paul H. Tarver wrote: >>>>>> I've never doubted the benefits of stored procedures and if I >>>>>> were an >>>>>> in-house programmer for a company with full admin rights and/or > console >>>>>> access to the SQL Servers, I would be tempted to always use
stored
>>>>>> procedures myself. However, that is NOT the world I work in. My >>>>>> job > is >>> to >>>>>> build interfaces to move data between different systems. I am >>>>>> usually >>>>>> provided with READ-ONLY SQL credentials so I can then issue
SELECT
>>>>> queries >>>>>> to extract data and then use the results of those queries to >>>>>> create >>> data >>>>>> feeds into other systems. >>>>>> >>>>>> Our systems pull data in one direction only and when I describe > dynamic >>>>> SQL >>>>>> statements I'm referring to something little like this >>>>>> (although most >>> are >>>>>> far more complicated queries with lots of moving parts): >>>>>> >>>>>> lcWhereClause = "WHERE emp.CpnyID = '" + >>>>>> ALLTRIM(thisform.CoCode.value) >>>>>> >>>>>> TEXT TO lcSQLCmd TEXTMERGE NOSHOW >>>>>> SELECT >>>>>> CAST(emp.CpnyID AS CHAR(20)) AS compid, >>>>>> CAST(emp.EmpId AS CHAR(20)) AS emplid, >>>>>> emp.NameFirst as fname, >>>>>> emp.NameMiddle as mname, >>>>>> emp.NameLast as lname, >>>>>> emp.StrtDate as hire_date >>>>>> FROM dbo.Employee emp >>>>>> <<lcWhereClause>> >>>>>> ENDTEXT >>>>>> >>>>>> lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList") >>>>>> >>>>>> We accept and validate the selection of the CoCode by the user
and
> then >>>>> we >>>>>> construct the "dynamic query." I suspect your perception of a >>>>>> Dynamic >>>>> Query >>>>>> is greatly different than mine. The point of my original >>>>>> comment was > to >>>>>> praise the ease with which I can construct SQL statements in a >>>>> TEXT/ENDTEXT >>>>>> construct and I think this example shows that >>>>>> >>>>>> Thanks! >>>>>> >>>>>> Paul H. Tarver >>>>>> >>>>>> >>>>>> -----Original Message----- >>>>>> From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On >>>>>> Behalf Of >>>>> Stephen >>>>>> Russell >>>>>> Sent: Friday, June 28, 2019 9:27 AM >>>>>> To: profoxtech@leafe.com >>>>>> Subject: Re: [NF] What would you miss from VFP, when migrating >>>>>> >>>>>> I am backing off of licenses for SQL Enterprise down to >>>>>> Standard for >>> 2/3 >>>>> of >>>>>> all my SQL Server usage in my new deployments. Use to have a >>>>>> total > of >>> 96 >>>>>> cores running Ent. and now seeing if we can only use 30. Having >>> virtual >>>>>> guests instead of a single bad ass box makes this a lot easier >>>>>> to do. >>>>>> >>>>>> Dynamic SQL can burn you. >>>>>> >>>>>>
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
>>>>>> ck/ >>>>>> >>>>>> >>>>>> Making a stored procedure is common sense. Why you cannot see
the
>>> beauty >>>>>> of it for long term source code is lost on me. Say you make a >>>>>> change >>> to >>>>> a >>>>>> table. You can easily find every sproc that referenced that
table
> with >>>>>> this statement and miss all that you have fixed: >>>>>> declare @text varchar(50) >>>>>> , @stringtosearch varchar(100) >>>>>> , @comment varchar(150) >>>>>> >>>>>> >>>>>> set @text = 'Warehouse' >>>>>> set @comment ='%WarehouseChange fixed%' >>>>>> >>>>>> SET @stringtosearch = '%' +@text + '%' >>>>>> >>>>>> SELECT Distinct SO.Name >>>>>> FROM sysobjects SO (NOLOCK) >>>>>> INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID >>>>>> AND SO.Type = 'P' >>>>>> AND SC.Text LIKE @stringtosearch >>>>>> and SO.id not in >>>>>> (select distinct SO1.ID >>>>>> FROM sysobjects SO1 (NOLOCK) >>>>>> INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID >>>>>> AND SO1.Type = 'P' >>>>>> AND SC1.Text LIKE @comment) >>>>>> >>>>>> ORDER BY SO.Name >>>>>> >>>>>> You can then cross reference every place that the table was >>>>>> used and >>> see >>>>> if >>>>>> you need to tweak the data access to include the change you >>>>>> just made >>> to >>>>>> the column. >>>>>> >>>>>> We just got handed an oh by the way that hits a major focus on >>>>>> how we >>>>> track >>>>>> sales. We use to give all sales to the plant that made them, >>>>>> which >>> makes >>>>>> sense. Over time we have created warehouses in areas of the >>>>>> country > to >>>>> hold >>>>>> product for delivery to a customer rich area. Sure the ERP >>>>>> already > did >>>>>> this but the early reporting team never saw that value. >>>>>> >>>>>> All of these changes are only in our BI/reporting system or our >>> customer >>>>>> portal. We have to identify over 1000 sprocs to validate that > nothing >>>>>> needs to be done here and only 150 really need to be altered. >>>>>> >>>>>> How would you find that in your prgs? I use the power of >>>>>> the db >>>>> engine >>>>>> to do a lot of things like this for me. >>>>>> >>>>>> >>>>>> On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < >>>>>> mbsoftwaresolutions@mbsoftwaresolutions.com> wrote: >>>>>> >>>>>>> On 6/27/2019 6:39 PM, Paul H. Tarver wrote: >>>>>>>> Give me a little credit for being a better programmer than
that.
>>>>>>> C'mon, Paul -- it's mega-million$ $teve we're talking about
here.
> Mr. >>>>>>> Deep Pockets with SQL Server blinders on usually with only
Stored
>>>>>>> Procedures being the only viable safe option. >>>>>>> >>>>>>> lol >>>>>>> >>>>>>> >>>>>>> >>>>>>> --- >>>>>>> This email has been checked for viruses by Avast antivirus >>>>>>> software. >>>>>>> https://www.avast.com/antivirus >>>>>>> >>>>>>>
[excessive quoting removed by server]
When VFP process an SQL statement to be SQLEXECuted(), it starts by locating parameters and by evaluating their VFP value.
For instance, the statement (a)
SELECT * FROM Customers WHERE CustomerCode = ?Thisform.txtSearch.Value;
is transformed into (b)
SELECT * FROM Customers WHERE CustomerCode = ?;
and the expression Thisform.txtSearch.Value is evaluated.
If its TYPE() is "C" (as would be something like "' OR 1 = 1; DROP TABLE Customers; --"), when the by now transformed statement is SQLEXECuted() VFP binds the parameter to a string expression (because that's how VFP maps its Character type to the SQL counterpart, SQL_CHAR).
After all parameters of the statement are bound, then the statement in its (b) form is finally sent to the ODBC driver that, in turn, pass it to the SQL provider.
Parameters are not passed as literals inside an SQL statement, regardless of how the statement were built. Their value are put aside so they are not "executed".
On Fri, Jun 28, 2019 at 7:11 PM Stephen Russell srussell705@gmail.com wrote:
This looks like a great test for Text EndText!
create a table deleteMe
In the form put text like this: [any value for a customer here] or 1 = 1 ; drop table deleteMe ; --
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
What do you see in the entire statement you put together?
If you run it against a SQL box does your table disappear?
To get around 1 = 1 you could have a TON of different combinations to get a true result. 'abc <> 'cba' does the trick to create a true condition and off it goes.
On Fri, Jun 28, 2019 at 12:13 PM Frank Cazabon frank.cazabon@gmail.com wrote:
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is
to
build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT
queries
to extract data and then use the results of those queries to create
data
feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic
SQL
statements I'm referring to something little like this (although most
are
far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and then
we
construct the "dynamic query." I suspect your perception of a Dynamic
Query
is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for
2/3
of
all my SQL Server usage in my new deployments. Use to have a total of
96
cores running Ent. and now seeing if we can only use 30. Having
virtual
guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the
beauty
of it for long term source code is lost on me. Say you make a change
to
a
table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and
see
if
you need to tweak the data access to include the change you just made
to
the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which
makes
sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our
customer
portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db
engine
to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Thanks for the heads up, Frank.
In this case, I do multiple validations and tests on the Thisform.CoCode.value which end processing on failures before it ever reaches the WHERE clause. One of the first tests we do is validate the user's selection of the CoCode against a table before we ever let processing begin. Then we do other validations before building the SQL command. My example was admittedly over-simplified.
Thanks again!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Friday, June 28, 2019 12:13 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
To make your code safer, ensure you use parameters:
m.CompanyID = ALLTRIM(thisform.CoCode.value) m.lcWhereClause = "WHERE emp.CpnyID = ?m.CompanyID"
Frank.
Frank Cazabon
On 28/06/2019 11:14 AM, Paul H. Tarver wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT queries to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic
SQL
statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" + ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT
lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")
We accept and validate the selection of the CoCode by the user and then we construct the "dynamic query." I suspect your perception of a Dynamic
Query
is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a
TEXT/ENDTEXT
construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of
Stephen
Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3
of
all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta
ck/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment) ORDER BY SO.NameYou can then cross reference every place that the table was used and see
if
you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we
track
sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to
hold
product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
On Thu, 27 Jun 2019, at 9:10 PM, Paul H. Tarver wrote:
I use TEXT/ENDTEXT to create dynamic SQL Queries by merging static text and dynamic variables, then pass the resulting string to SQLEXECUTE. Very convenient when creating large query strings.
var table = "mytable"; var myfield = "field1"; var myvalue=100; var cmd = $"select {myfield} from {mytable} where value={myvalue}";
Of course when you do this properly using SQL parameters then it will look different :)
On 2019/07/01 10:53, Alan Bourke wrote:
On Thu, 27 Jun 2019, at 9:10 PM, Paul H. Tarver wrote:
I use TEXT/ENDTEXT to create dynamic SQL Queries by merging static text and dynamic variables, then pass the resulting string to SQLEXECUTE. Very convenient when creating large query strings.
var table = "mytable"; var myfield = "field1"; var myvalue=100; var cmd = $"select {myfield} from {mytable} where value={myvalue}";
Of course when you do this properly using SQL parameters then it will look different :)
Only difference really in X# is "i" for interpolated and "e" for escaped strings: var cmd = i"select {myfield} from {mytable} where value={myvalue}";
The philosophy is that you have a function that is called and if you have:
TEXT TO mVar <optional params> .... ENDTEXT
Internally it translate to:
mVar := __TextWhatEver(<optional params>)
So the function internally will handled based on the parameter list what needs to be done to the string.
On 2019/06/27 21:43, Stephen Russell wrote:
Am I missing something about TEXT/ENDTEXT where it just mashes up a string for you for whatever transpires within the loop?
What do you do with that output or the textmerge when you are done with it?
On Thu, Jun 27, 2019 at 10:09 AM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
Steve Ellenoff and I were talking the other day about the beauty of TEXTMERGE (TEXT/ENDTEXT) and iirc he indicated that wasn't in DotNet (or earlier versions anyway). I use TEXT/ENDTEXT *HEAVILY* in code often.
On 6/27/2019 8:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
Yeah, that'd suck. I guess you'd have to loop through record sets like ADO
FOR MyLoop = 1 to MyRecordSet.Recordset.Count
ENDFOR
??? "This sucks"
On 6/27/2019 4:45 PM, John Weller wrote:
I think I would miss SCAN/ENDSCAN most.
John
John Weller 01380 723235 07976 393631
[excessive quoting removed by server]
I would greatly miss the speedy import capabilities of Foxpro and the CREATE CURSOR to build temporary files to temporarily hold imported data. I also make extensive use of Class Libraries. More and more I'm using of creating Empty Objects and adding properties to store and manage lots of variables including making it possible to pass a single reference to the object as a parameter and making all of the properties available to other modules. And I agree 100% with wanting to keep the TEXTMERGE feature.
I think I could do this all day and because it seems I'm still learning new ways to use VFP commands and features, I think I fear losing any of them!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Johan Nel Sent: Thursday, June 27, 2019 7:52 AM To: profoxtech@leafe.com Subject: [NF] What would you miss from VFP, when migrating
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
1. Macro-compilation 2. Evaluate(<script>)
From a X# perspective the FOX release already has support for:
1. DBFCDX and readonly access for VFP specific FieldTypes (AutoInc, etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
VFP cursors are under discussion and will be supported, one way or the other.
There are obviously some .NET features like DataSet/DataTable/DataReader that are all under scrutiny to see how these VFP specifics will/can be accommodated.
As you say, some of these we learn of everyday, same goes for me using X#/.NET and it is up to the community to assist in resolving these once the public VFP support is released. Again, first things first, get the basics in place then we as community in partnership with the DevTeam can sort out the outstanding more "unused" features. It is already clear now that the core of the language is stable, how quick the DevTeam is responding on fixing detected issues...
Johan
On 2019/06/27 18:53, Paul H. Tarver wrote:
I would greatly miss the speedy import capabilities of Foxpro and the CREATE CURSOR to build temporary files to temporarily hold imported data. I also make extensive use of Class Libraries. More and more I'm using of creating Empty Objects and adding properties to store and manage lots of variables including making it possible to pass a single reference to the object as a parameter and making all of the properties available to other modules. And I agree 100% with wanting to keep the TEXTMERGE feature.
I think I could do this all day and because it seems I'm still learning new ways to use VFP commands and features, I think I fear losing any of them!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Johan Nel Sent: Thursday, June 27, 2019 7:52 AM To: profoxtech@leafe.com Subject: [NF] What would you miss from VFP, when migrating
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc, etc)
with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
On Thu, 27 Jun 2019, at 5:53 PM, Paul H. Tarver wrote:
I would greatly miss the speedy import capabilities of Foxpro and the CREATE CURSOR to build temporary files to temporarily hold imported data
Well, assuming CSV:
Install https://joshclose.github.io/CsvHelper/
Then:
void Main() { using (var reader = new StreamReader("path\to\file.csv")) using (var csv = new CsvReader(reader)) { var records = csv.GetRecords<dynamic>(); } }
So you have a collection of records in 'records' with which you can do what you like.
Install https://joshclose.github.io/CsvHelper/
Then:
void Main() { using (var reader = new StreamReader("path\to\file.csv")) using (var csv = new CsvReader(reader)) { var records = csv.GetRecords<dynamic>(); } }
Which is as follows in X# with VFP syntax selected:
function Start() as void using var reader = StreamReader("<path>.file.csv") using var csv = CsvReader(reader) var records = csvGetRecords<dynamic>() end using end using return
The VFP Report Writer is amazing compared to other development tools I've seen, including my beloved Xojo. I've had to create my own report writer that spits out HTML based reports just to get me close to what VFP offered.
Another language command is the SCATTER NAME and GATHER NAME. The ability to create an object with properties that corresponds to each field of a record is incredibly useful. Especially when combined with the TEXTMERGE command already stated earlier.
-Kevin
On 06/27/2019 08:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
+1
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Kevin Cully Sent: Thursday, June 27, 2019 12:25 PM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
The VFP Report Writer is amazing compared to other development tools I've seen, including my beloved Xojo. I've had to create my own report writer that spits out HTML based reports just to get me close to what VFP offered.
Another language command is the SCATTER NAME and GATHER NAME. The ability to create an object with properties that corresponds to each field of a record is incredibly useful. Especially when combined with the TEXTMERGE command already stated earlier.
-Kevin
On 06/27/2019 08:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
I have created a similar approach in .NET based on the NameValueCollection class.
Basically have subclassed it and added Methods:
1. ReaderToCollection - A SQLquery and a DBF to collection 2. ToSql - Construct an UPDATE/INSERT query based on the collection 3. ToControls - Populate a Form with values from the Collection
I am currently with some VFP users busy creating a tool that can read VFP Repos and translate it into VS X# Solutions/Projects. The Report Writer will be addressed once the other repo files are completed. As features are added to the VFP syntax support in X# it gets easier.
Johan
On 2019/06/27 19:25, Kevin Cully wrote:
The VFP Report Writer is amazing compared to other development tools I've seen, including my beloved Xojo. I've had to create my own report writer that spits out HTML based reports just to get me close to what VFP offered.
Another language command is the SCATTER NAME and GATHER NAME. The ability to create an object with properties that corresponds to each field of a record is incredibly useful. Especially when combined with the TEXTMERGE command already stated earlier.
-Kevin
On 06/27/2019 08:51 AM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
If you have to summarize the 1 or 2 features of VFP that you will feel is a step backwards when migrating to .NET or any other platform as a move forwards.
Looking forward to your replies.
Two things that was highlighted on Foxite:
- Macro-compilation
- Evaluate(<script>)
From a X# perspective the FOX release already has support for:
- DBFCDX and readonly access for VFP specific FieldTypes (AutoInc,
etc) with full support planned in the foreseeable future; 2. WITH/ENDWITH, TEXT/ENDTEXT (partially implemented); 3. Macro-compilation; 4. Script engine that can make full use of the .NET framework.
XBase greetings,
Johan Nel FOX Member: Friends of XSharp https://xsharp.info George, South Africa
[excessive quoting removed by server]
On Thu, 27 Jun 2019, at 6:25 PM, Kevin Cully wrote:
Another language command is the SCATTER NAME and GATHER NAME. The ability to create an object with properties that corresponds to each field of a record is incredibly useful
Pick any one of the many ORMs!
I love me some ORM!
Snippet on making an object to be populated with data and saved. You can pile in the Add() objects and then call the SaveChanges() once if you need to. Such as N sales order detail lines.
public static void loadresp(String Plant, String respStr) { using (RingSecurityEntities1 dbb = new RingSecurityEntities1()) { SafetyCultureResponse responseFrom = new SafetyCultureResponse(); Guid gu = Guid.NewGuid(); responseFrom.ID = gu; responseFrom.EventDate = DateTime.Now; responseFrom.ReponsePlant = Plant; responseFrom.ResponsePhrase = respStr; dbb.SafetyCultureResponses.Add(responseFrom); dbb.SaveChanges(); } }
On Mon, Jul 1, 2019 at 3:47 AM Alan Bourke alanpbourke@fastmail.fm wrote:
On Thu, 27 Jun 2019, at 6:25 PM, Kevin Cully wrote:
Another language command is the SCATTER NAME and GATHER NAME. The ability to create an object with properties that corresponds to each field of a record is incredibly useful
Pick any one of the many ORMs!
-- Alan Bourke alanpbourke (at) fastmail (dot) fm
[excessive quoting removed by server]
On Thu, 27 Jun 2019, at 1:52 PM, Johan Nel wrote:
Hi VFPers
I have asked this question on foxite too with very little feedback, so I am asking it here too.
- Macro-compilation
The nature of .NET sort of removes the need for this but you have ExpandoObjects and Reflection should you really want to. The System.Dynamic namespace.
- Evaluate(<script>)
Hi Alan,
On 2019/07/01 10:46, Alan Bourke wrote:
The nature of .NET sort of removes the need for this but you have ExpandoObjects and Reflection should you really want to. The System.Dynamic namespace.
This is true and I have removed a lot of my macros used in Visual Objects by use of Reflection. There are however cases that I cannot get passed using macros.
This is also true for X# having xsScript if you prefer to stay with an XBase style script engine
-
Alan Bourke -
António Tavares Lopes -
Frank Cazabon -
Johan Nel -
John Weller -
Kevin Cully -
MB Software Solutions, LLC -
Paul H. Tarver -
Stephen Russell