Re: DBD::oracle question about auto reconnecting
John Scoles wrote: Not that I am aware of. Is auto-reconnect a specific MYSQL command of some sort or is a PERL base command or some sort? Can you give me an example of how it is used? cheers John Scoles Patrick Galbraith wrote: Hi there, Is there the equivalent of an auto-reconnect with DBD::Oracle as DBD::mysql has (appended in the connect DSN) ? Thanks! Patrick John, Hi! DBD::mysql uses mysql_auto_reconnect to connect back to MySQL if the connection drops $dbh= DBI-connect('DBI:mysql:test:mysql_auto_reconnect=1', $foo, $fee)...; or of course $dbh-{mysql_auto_reconnect}; In the perl side of the driver: if ($this ($ENV{MOD_PERL} || $ENV{GATEWAY_INTERFACE})) { $this-{mysql_auto_reconnect} = 1; } I had this off once with a release I did, and a lot of people were very unhappy ;) So, whatever might give the functionality to reconnect automatically I suppose it'd be the same as doing unless ($dbh-ping) { $dbh= connect ... } Thanks much, Patrick
Re: DBD::oracle question about auto reconnecting
On Tue, Nov 11, 2008 at 11:27:59AM -0500, Patrick Galbraith wrote: John, Hi! DBD::mysql uses mysql_auto_reconnect to connect back to MySQL if the connection drops $dbh= DBI-connect('DBI:mysql:test:mysql_auto_reconnect=1', $foo, $fee)...; or of course $dbh-{mysql_auto_reconnect}; In the perl side of the driver: if ($this ($ENV{MOD_PERL} || $ENV{GATEWAY_INTERFACE})) { $this-{mysql_auto_reconnect} = 1; } I had this off once with a release I did, and a lot of people were very unhappy ;) I hope they all appreciate the risks they're taking if they use locks or any other server state that would be silently lost by an auto reconnect. So, whatever might give the functionality to reconnect automatically I suppose it'd be the same as doing unless ($dbh-ping) { $dbh= connect ... } The DBI spells that DBI-connect_cached(...) Tim.
security advice needed
Hi, we have a special address that our users can forward spam complaints to, and the number of complaints is exceeding what I can deal with manually. So I am thinking of automating this process and populating a database with things like local user, sending IP, sending relay address, subject, etc for each spam complaint that comes in. So I need to make sure that if I do something like the standard: $sth = $dbh-prepare(INSERT INTO table(foo,bar,baz) VALUES (?,?,?)); and use the contents of the Subject: line as a value, some spammer couldn't put SQL code in the subject and have it interpreted. Something like: Subject: Increase your money; hello; use mysql; drop table users; or something to that effect. Would someone be able to do this, and if so, how would I guard against it? Thanks... Jim McCullars University of Alabama in Huntsville
RE: security advice needed
The fact that you're using placeholders will prevent an SQL injection attack from happening. You're good to go. -Original Message- From: James H. McCullars [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2008 1:27 PM To: dbi-users@perl.org Subject: security advice needed Hi, we have a special address that our users can forward spam complaints to, and the number of complaints is exceeding what I can deal with manually. So I am thinking of automating this process and populating a database with things like local user, sending IP, sending relay address, subject, etc for each spam complaint that comes in. So I need to make sure that if I do something like the standard: $sth = $dbh-prepare(INSERT INTO table(foo,bar,baz) VALUES (?,?,?)); and use the contents of the Subject: line as a value, some spammer couldn't put SQL code in the subject and have it interpreted. Something like: Subject: Increase your money; hello; use mysql; drop table users; or something to that effect. Would someone be able to do this, and if so, how would I guard against it? Thanks... Jim McCullars University of Alabama in Huntsville
mixing placehoders and functions with MySQL
I'm trying something along the lines of: insert into foo(fname, lname, phone, dateadded, status, datemodified) values (?,?,?,curdate(),1,curdate()) and it's failing saying I have an sql error at ...curdate(). DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' curdate())' at line 1 at ./ webdirectory_updater.pl line 128. I cannot mix parameters and functions like that? -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs
Re: security advice needed
On Wed, Nov 12, 2008 at 3:50 PM, Ulisses Montenegro [EMAIL PROTECTED] wrote: Also, if you ever need to display those values in any other potentially interpreted format (such as a Web page -- browsers interpret and render HTML), remember to escape them. Even if you are protecting yourself against SQL injection, you might end up vulnerable to XSS/CSRF attacks by displaying unescaped data in a web document. Ulisses see also http://search.cpan.org/~rsavage/HTML-Entities-Interpolate-1.00/lib/HTML/Entities/Interpolate.pm
Re: mixing placehoders and functions with MySQL
Bruce Johnson schreef: I'm trying something along the lines of: insert into foo(fname, lname, phone, dateadded, status, datemodified) values (?,?,?,curdate(),1,curdate()) and it's failing saying I have an sql error at ...curdate(). DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' curdate())' at line 1 at ./ webdirectory_updater.pl line 128. I cannot mix parameters and functions like that? The error has a space in front of curdate()), so probably your SQL statement is a bit different from what you think. Did you copy/paste it in your posting (good), or typed it in again (bad)? -- Affijn, Ruud Gewoon is een tijger.
RE: security advice needed
Also, if you ever need to display those values in any other potentially interpreted format (such as a Web page -- browsers interpret and render HTML), remember to escape them. Even if you are protecting yourself against SQL injection, you might end up vulnerable to XSS/CSRF attacks by displaying unescaped data in a web document. Ulisses On Wed, 2008-11-12 at 14:03 -0700, Ian Harisay wrote: The fact that you're using placeholders will prevent an SQL injection attack from happening. You're good to go. -Original Message- From: James H. McCullars [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2008 1:27 PM To: dbi-users@perl.org Subject: security advice needed Hi, we have a special address that our users can forward spam complaints to, and the number of complaints is exceeding what I can deal with manually. So I am thinking of automating this process and populating a database with things like local user, sending IP, sending relay address, subject, etc for each spam complaint that comes in. So I need to make sure that if I do something like the standard: $sth = $dbh-prepare(INSERT INTO table(foo,bar,baz) VALUES (?,?,?)); and use the contents of the Subject: line as a value, some spammer couldn't put SQL code in the subject and have it interpreted. Something like: Subject: Increase your money; hello; use mysql; drop table users; or something to that effect. Would someone be able to do this, and if so, how would I guard against it? Thanks... Jim McCullars University of Alabama in Huntsville -- Ulisses Reina Montenegro de Albuquerque AB34 A154 28C1 84A8 676E 202F 264B 117F 88BA B1A5 Tempest Security Intelligence [http://www.tempest.com.br/] In theory, theory and practice are the same. In practice, they are not.
Re: mixing placehoders and functions with MySQL
DBD::mysql::db do failed: On Wed, Nov 12, 2008 at 4:31 PM, Bruce Johnson [EMAIL PROTECTED] wrote: I'm trying something along the lines of: insert into foo(fname, lname, phone, dateadded, status, datemodified) values (?,?,?,curdate(),1,curdate()) and it's failing saying I have an sql error at ...curdate(). DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' curdate())' at line 1 at ./webdirectory_updater.pl line 128. Are you using do() instead of prepare()? Placeholders go into with prepared statements, not immediate executions. I cannot mix parameters and functions like that? Yes you can, in a prepared statement. -- Bruce Johnson University of Arizona College of Pharmacy Information Technology Group Institutions do not have opinions, merely customs -- They who would give up an essential liberty for temporary security, deserve neither liberty or security. Benjamin Franklin Our lives begin to end the day we become silent about things that matter. Martin Luther King The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment IV to the Constitution of the United States I am not going to answer any questions as to my association, my philosophical or religious beliefs or my political beliefs, or how I voted in any election, or any of these private affairs. I think these are very improper questions for any American to be asked, especially under such compulsion as this. Pete Seeger before the House Un-American Activities Comittee
Re: DBD::oracle question about auto reconnecting
I echo Tim's comments. We looked into implementing auto reconnect for our applications (e.g. in the case of the DB going down) and unless you have a *very* simple scenario it was just too much work to save and restore state. For example, you could lose the connection between a -prepare and an -execute. On reconnecting you would need to know to go back and prepare the statement again. What if you were in the middle of a transaction? On reconnect you would need to know when the last commit happened and redo any work since then. Very messy if not impossible to handle in a generic way. Steve On Wed, 2008-11-12 at 15:41 +, Tim Bunce wrote: On Tue, Nov 11, 2008 at 11:27:59AM -0500, Patrick Galbraith wrote: John, Hi! DBD::mysql uses mysql_auto_reconnect to connect back to MySQL if the connection drops $dbh= DBI-connect('DBI:mysql:test:mysql_auto_reconnect=1', $foo, $fee)...; or of course $dbh-{mysql_auto_reconnect}; In the perl side of the driver: if ($this ($ENV{MOD_PERL} || $ENV{GATEWAY_INTERFACE})) { $this-{mysql_auto_reconnect} = 1; } I had this off once with a release I did, and a lot of people were very unhappy ;) I hope they all appreciate the risks they're taking if they use locks or any other server state that would be silently lost by an auto reconnect. So, whatever might give the functionality to reconnect automatically I suppose it'd be the same as doing unless ($dbh-ping) { $dbh= connect ... } The DBI spells that DBI-connect_cached(...) Tim. ** This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email. **
Re: mixing placehoders and functions with MySQL
Jeffrey Seger schreef: Are you using do() instead of prepare()? Placeholders go into with prepared statements, not immediate executions. See the DBI documentation: do $rows = $dbh-do($statement) or die $dbh-errstr; $rows = $dbh-do($statement, \%attr) or die $dbh-errstr; $rows = $dbh-do($statement, \%attr, @bind_values) or die ... Prepare and execute a single statement. [...] -- Affijn, Ruud Gewoon is een tijger.
Re: mixing placehoders and functions with MySQL
On Wed, Nov 12, 2008 at 7:26 PM, Dr.Ruud [EMAIL PROTECTED] wrote: Jeffrey Seger schreef: Are you using do() instead of prepare()? Placeholders go into with prepared statements, not immediate executions. See the DBI documentation: do $rows = $dbh-do($statement) or die $dbh-errstr; $rows = $dbh-do($statement, \%attr) or die $dbh-errstr; $rows = $dbh-do($statement, \%attr, @bind_values) or die ... Prepare and execute a single statement. [...] Right you areI wasn't paying attention to the fact that it was an insert The do() method can be used for non repeated non-SELECT statement (or with drivers that don't support placeholders): Bruce: Can we see the actual code that is producing the error? Without that we'll just have to keep guessing. -- Affijn, Ruud Gewoon is een tijger. -- They who would give up an essential liberty for temporary security, deserve neither liberty or security. Benjamin Franklin Our lives begin to end the day we become silent about things that matter. Martin Luther King The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment IV to the Constitution of the United States I am not going to answer any questions as to my association, my philosophical or religious beliefs or my political beliefs, or how I voted in any election, or any of these private affairs. I think these are very improper questions for any American to be asked, especially under such compulsion as this. Pete Seeger before the House Un-American Activities Comittee