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]