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/CAJidMY+XpqXT+gyysttvQ8JEcgUjCoKfExouEcFpajxv=yh...@mail.gmail.com ** 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.
