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 <[email protected]>
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 <
[email protected]> 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 <[email protected]
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
<[email protected]>
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 <
[email protected]>
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:[email protected]] On
Behalf Of
Stephen
Russell
Sent: Friday, June 28, 2019 9:27 AM
To: [email protected]
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 <
[email protected]> 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]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
