Re: [lazarus] MySQL Queries using Lazarus
Op dinsdag 11-12-2007 om 15:54 uur [tijdzone -0800], schreef el
stamatakos:
Hi All,
I have an application that connects to a MySQl database. I have found the
only way to get Lazrus to work without giving me an error Cannot access
Active DataSet is to do a Clear,Add, Open Close, like below
MainForm.SQLQuery1.SQL.Clear;
MainForm.SQLQuery1.SQL.Add('SELECT revID from rev WHERE
revName='+#39+CBProcessRev.Text+#39);
If you want to save some code, you could replace the lines above with:
Mainform.SQLQuery1.SQL.Text := 'SELECT revID fro..';
Joost
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] MySQL Queries using Lazarus
ik wrote: One small note, you have an SQL injection at your code. I recommend you to use parameters as how to add the values, but prior to that, you should check to see if there are invalid chars, and trim them. That way, you can avoid a lot of problems. Ido Perhaps you should explain what is so terrible about a SQL injection ? I have heard of this before, but not the reason. I do this regularly and it seems to work fine! I am hesitant to recommend parameters in this situation currently. It depends on whether you form everything at run time, in which they are probably OK, or try to set it up in Lazarus at design time. I have had problems with parameters set up at design time. (I am still trying to work out exactly what the issue is, or I would have posted something about it, but basically they seem to loose their type specifications. Of course, I might simply be doing something wrong !). If Lefti is just writing his first few queries, what he is doing is probably a good way to get the hang of it. cheers, John Sunderland _ To unsubscribe: mail [EMAIL PROTECTED] with unsubscribe as the Subject archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] MySQL Queries using Lazarus
Op woensdag 12-12-2007 om 22:51 uur [tijdzone +1100], schreef John:
ik wrote:
One small note, you have an SQL injection at your code. I recommend
you to use parameters as how to add the values, but prior to that, you
should check to see if there are invalid chars, and trim them. That
way, you can avoid a lot of problems.
Ido
Perhaps you should explain what is so terrible about a SQL injection
? I have heard of this before, but not the reason. I do this regularly
and it seems to work fine!
Suppose the query is like this: (from some mails earlier)
MainForm.SQLQuery1.SQL.Add('SELECT revID from rev WHERE
revName='+#39+CBProcessRev.Text+#39);
Oh, yes it works. Until...
A user types '; drop table rev; in CBProcessRev.
Well, that will still work. But only once. ;)
Joost
(btw: this won't work with all TSQLConnections, because some don't
accept a semicolon)
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] MySQL Queries using Lazarus
On Dec 12, 2007 2:39 PM, John [EMAIL PROTECTED] wrote:
Joost van der Sluis wrote:
Op woensdag 12-12-2007 om 22:51 uur [tijdzone +1100], schreef John:
ik wrote:
One small note, you have an SQL injection at your code. I recommend
you to use parameters as how to add the values, but prior to that, you
should check to see if there are invalid chars, and trim them. That
way, you can avoid a lot of problems.
Ido
Perhaps you should explain what is so terrible about a SQL injection
? I have heard of this before, but not the reason. I do this regularly
and it seems to work fine!
Suppose the query is like this: (from some mails earlier)
MainForm.SQLQuery1.SQL.Add('SELECT revID from rev WHERE
revName='+#39+CBProcessRev.Text+#39);
Oh, yes it works. Until...
A user types '; drop table rev; in CBProcessRev.
Well, that will still work. But only once. ;)
Joost
(btw: this won't work with all TSQLConnections, because some don't
accept a semicolon)
So the issue is accepting the text straight from the user input, not the
fact that the query text is constructed rather than using a parameter
? When I say I do it all the time, it is far more likely to be
something like 'select * from sometable where ref = ' +
IntToStr(AnIntegerValue), or some otherwise well-validated expression.
Make sure first of all to have only the valid chars, then escape all
potentially exploitable chars (such as ' /* -- ; and so on).
The thing with Injection (regardless of SQL) is that I can use any
type of content I wish to make in order to make sure that I can
effect/gain things that I can't any other way.
I usually create a filter function such as follows:
function filterChars (const Value : string; chars : TCharset) : string;
var
i : integer;
begin
Result := '';
for i := 1 to length (Value) do
if (Value[i] in chars ) then
Result := Result + Value[i];
end;
I just rewrite it from my head... It will remove any chars that are
not part of the chars parameter, making it clean from un needed
chars. then I'll escape any string chars by converting ' into \' or ''
(depands on the database). The thing with parameters (anonymous or
named) is that the database itself makes the escaping for you.
Don't get me wrong, I actually prefer to use parameters in such a
circumstance if I can get them to work, especially if the sql is
executed repeatedly with different criteria.
I recommand you to read my guide for secure programming:
http://wiki.freepascal.org/Secure_programming
Or in a newer version:
http://ik.homelinux.org/index.rhtml/guides/secure_programming
cheers,
John Sunderland
Ido
--
http://ik.homelinux.org/
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
[lazarus] MySQL Queries using Lazarus
Hi All,
I have an application that connects to a MySQl database. I have found the only
way to get Lazrus to work without giving me an error Cannot access Active
DataSet is to do a Clear,Add, Open Close, like below
MainForm.SQLQuery1.SQL.Clear;
MainForm.SQLQuery1.SQL.Add('SELECT revID from rev WHERE
revName='+#39+CBProcessRev.Text+#39);
MainForm.SQLQuery1.Open;
if MainForm.SQLQuery1.SQL.Text'' then
if MainForm.SQLQuery1.RecordCount0 then
revID:=MainForm.SQLQuery1.FieldValues['revID'];
// showmessage(IntToStr(revID));
MainForm.SQLQuery1.Close;
MainForm.SQLQuery1.SQL.Clear;
MainForm.SQLQuery1.SQL.Add('SELECT * from flow WHERE
processID='+IntToStr(ProcessID)+
' AND '+IntToStr(revID)+'='+IntToStr(revID));
MainForm.SQLQuery1.Open;
if MainForm.SQLQuery1.SQL.Text'' then
while Not MainForm.SQLQuery1.EOF do begin
CBProcessFlow.Items.Add(MainForm.SQLQuery1.FieldValues['flowName']);
MainForm.SQLQuery1.Next;
end;
MainForm.SQLQuery1.Close;
My Question is why do I have to close and re open is there a better way of
doing this. Thanks
Lefti
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] MySQL Queries using Lazarus
Hi Lefti,
Opening and closing the query causes the query to be re-parsed and
prepared. IF you change the sql text, there is no way round this. At
the very least, prepare has to be called, and opening a query does not
do a lot more than calling prepare. From what I can see in the code, to
prepare a query, you have to un-prepare the previous one, and that fails
unless the query is closed.
By the way, when you start a new topic, you are setting a new subject,
but still replying to a previous topic. Any threaded mail client still
sees it as a reply to a previous topic, and makes a real mess of a
threaded view! According to my email, this is still part of a
discussion that started with [lazarus] Patch for Forms in Forms or
MDI Could you send a new message, not a reply, with a new subject ?
cheers,
John Sunderland
el stamatakos wrote:
Hi All,
I have an application that connects to a MySQl database. I have found the only way to
get Lazrus to work without giving me an error Cannot access Active DataSet is
to do a Clear,Add, Open Close, like below
MainForm.SQLQuery1.SQL.Clear;
MainForm.SQLQuery1.SQL.Add('SELECT revID from rev WHERE
revName='+#39+CBProcessRev.Text+#39);
MainForm.SQLQuery1.Open;
if MainForm.SQLQuery1.SQL.Text'' then
if MainForm.SQLQuery1.RecordCount0 then
revID:=MainForm.SQLQuery1.FieldValues['revID'];
// showmessage(IntToStr(revID));
MainForm.SQLQuery1.Close;
MainForm.SQLQuery1.SQL.Clear;
MainForm.SQLQuery1.SQL.Add('SELECT * from flow WHERE
processID='+IntToStr(ProcessID)+
' AND '+IntToStr(revID)+'='+IntToStr(revID));
MainForm.SQLQuery1.Open;
if MainForm.SQLQuery1.SQL.Text'' then
while Not MainForm.SQLQuery1.EOF do begin
CBProcessFlow.Items.Add(MainForm.SQLQuery1.FieldValues['flowName']);
MainForm.SQLQuery1.Next;
end;
MainForm.SQLQuery1.Close;
My Question is why do I have to close and re open is there a better way of
doing this. Thanks
Lefti
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
Re: [lazarus] MySQL Queries using Lazarus
One small note, you have an SQL injection at your code. I recommend
you to use parameters as how to add the values, but prior to that, you
should check to see if there are invalid chars, and trim them. That
way, you can avoid a lot of problems.
Ido
On Dec 12, 2007 1:54 AM, el stamatakos [EMAIL PROTECTED] wrote:
Hi All,
I have an application that connects to a MySQl database. I have found the
only way to get Lazrus to work without giving me an error Cannot access
Active DataSet is to do a Clear,Add, Open Close, like below
MainForm.SQLQuery1.SQL.Clear;
MainForm.SQLQuery1.SQL.Add('SELECT revID from rev WHERE
revName='+#39+CBProcessRev.Text+#39);
MainForm.SQLQuery1.Open;
if MainForm.SQLQuery1.SQL.Text'' then
if MainForm.SQLQuery1.RecordCount0 then
revID:=MainForm.SQLQuery1.FieldValues['revID'];
// showmessage(IntToStr(revID));
MainForm.SQLQuery1.Close;
MainForm.SQLQuery1.SQL.Clear;
MainForm.SQLQuery1.SQL.Add('SELECT * from flow WHERE
processID='+IntToStr(ProcessID)+
' AND '+IntToStr(revID)+'='+IntToStr(revID));
MainForm.SQLQuery1.Open;
if MainForm.SQLQuery1.SQL.Text'' then
while Not MainForm.SQLQuery1.EOF do begin
CBProcessFlow.Items.Add(MainForm.SQLQuery1.FieldValues['flowName']);
MainForm.SQLQuery1.Next;
end;
MainForm.SQLQuery1.Close;
My Question is why do I have to close and re open is there a better way of
doing this. Thanks
Lefti
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
--
http://ik.homelinux.org/
_
To unsubscribe: mail [EMAIL PROTECTED] with
unsubscribe as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
