Re: [PHP-DB] SQL injection
Date: Sunday, June 21, 2015 12:39:06 PM -0400 From: Aziz Saleh azizsa...@gmail.com On Sun, Jun 21, 2015 at 9:19 AM, Lester Caine les...@lsces.co.uk wrote: OK - this had no chance of success since publish_date_desc is processed using the _desc ( or _asc ) and any invalid data stripped sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20n ame_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const (CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3 D1 The question is more of interest in just what it was trying to achieve? I presume hack MySQL? So Firebird would barf anyway, but just trying to something that has generated some several hundred error log entries in the last two days ... Lester Caine - G8HFL The sub-query is invalid, if valid it would've been equivalent to: or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy' as 1))a) -- and 1=1 Seems non threatening to me. Regardless of whether this specific attack could have resulted in harmful sql injection or not, user input should be sanitized so that things never get this far. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL injection
On Sun, Jun 21, 2015 at 9:19 AM, Lester Caine les...@lsces.co.uk wrote: OK - this had no chance of success since publish_date_desc is processed using the _desc ( or _asc ) and any invalid data stripped sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20name_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const(CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3D1 The question is more of interest in just what it was trying to achieve? I presume hack MySQL? So Firebird would barf anyway, but just trying to something that has generated some several hundred error log entries in the last two days ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php The sub-query is invalid, if valid it would've been equivalent to: or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy' as 1))a) -- and 1=1 Seems non threatening to me.
Re: [PHP-DB] SQL injection
But what does your application do when it gets an invalid SQL statement? Maybe it is telling the attacker something important about your database so that they can compromise it with the appropriate injection. On 2:36PM, Sun, Jun 21, 2015 Lester Caine les...@lsces.co.uk wrote: On 21/06/15 18:55, Richard wrote: OK - this had no chance of success since publish_date_desc is processed using the _desc ( or _asc ) and any invalid data stripped sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20n ame_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const (CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3 D1 The question is more of interest in just what it was trying to achieve? I presume hack MySQL? So Firebird would barf anyway, but just trying to something that has generated some several hundred error log entries in the last two days ... Lester Caine - G8HFL The sub-query is invalid, if valid it would've been equivalent to: or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy' as 1))a) -- and 1=1 Seems non threatening to me. Regardless of whether this specific attack could have resulted in harmful sql injection or not, user input should be sanitized so that things never get this far. ? That is taken direct off the URL! Sod all I can do to prevent it, but I was simply asking if I was missing something as it did not make any sense? It got no further than the error log but as I said several hundred attempts via a few different filter options all of which suggested something that was expected to work if the site was a vulnerable mysql powered site ... which it's not. Seems that is just a pointless URL rather than some recently identified potential vulnerability? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Sent from my android
Re: [PHP-DB] SQL injection
On 21/06/15 20:14, Mark Murphy wrote: But what does your application do when it gets an invalid SQL statement? Maybe it is telling the attacker something important about your database so that they can compromise it with the appropriate injection. It just defaults to the first news article in this case ... and counts it as another hit on that article. We have never allowed free text SQL to be included in any query, and any variable passed via the URL to provide navigation is only ever passed as a parameter, so even if there was no filtering of the parameter it would just fail. I'd only expect a continued 'attack' if the URL was returning something useful so to carry on just did not make sense ... -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL injection
On 21/06/15 18:55, Richard wrote: OK - this had no chance of success since publish_date_desc is processed using the _desc ( or _asc ) and any invalid data stripped sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20n ame_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const (CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3 D1 The question is more of interest in just what it was trying to achieve? I presume hack MySQL? So Firebird would barf anyway, but just trying to something that has generated some several hundred error log entries in the last two days ... Lester Caine - G8HFL The sub-query is invalid, if valid it would've been equivalent to: or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy' as 1))a) -- and 1=1 Seems non threatening to me. Regardless of whether this specific attack could have resulted in harmful sql injection or not, user input should be sanitized so that things never get this far. ? That is taken direct off the URL! Sod all I can do to prevent it, but I was simply asking if I was missing something as it did not make any sense? It got no further than the error log but as I said several hundred attempts via a few different filter options all of which suggested something that was expected to work if the site was a vulnerable mysql powered site ... which it's not. Seems that is just a pointless URL rather than some recently identified potential vulnerability? -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On 16/05/15 10:00, Karl DeSaulniers wrote: That does clarify things a bit better on both the @ question and prepared statements. Thank you for the link as well. So new question.. what is the best type of database to use for someone who wants to start small and grow big? My findings led me to MySQL InnoDB. I'm somewhat biased since much of my data goes back to a time before MySQL even existed. Using Interbase which is now open source as Firebird. Early versions of MySQL were never stable enough to use in the environments I work, and while Postgres was also appearing on the radar, I've no reason to change. Little things like being able to run backups automatically even if I've never actually had to use one. And some SQL functions available in Firebird have yet to appear in other engines, and having to decide if you want the security InnoDB provides is simply standard in other engines. The first question is are you hosting yourself or using third party hosting? MySQL tends to be available on all third party posting, with some providing Postgres, while Firebird tends to be privately hosted. If you are hosting yourself, then of cause MySQL may actually be MariaDB and you end up with a mix of sources. It's a bit like Internbase and Firebird where the commercial charges can affect one installation where the other is totally free. If you are only looking for a single installation, then MySQL is probably fine. I'm running 50+ databases and with Firebird each is isolated in it's own directory and automatically backs up to the website storage area. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On May 16, 2015, at 8:42 AM, Lester Caine les...@lsces.co.uk wrote: On 16/05/15 10:00, Karl DeSaulniers wrote: That does clarify things a bit better on both the @ question and prepared statements. Thank you for the link as well. So new question.. what is the best type of database to use for someone who wants to start small and grow big? My findings led me to MySQL InnoDB. I'm somewhat biased since much of my data goes back to a time before MySQL even existed. Using Interbase which is now open source as Firebird. Early versions of MySQL were never stable enough to use in the environments I work, and while Postgres was also appearing on the radar, I've no reason to change. Little things like being able to run backups automatically even if I've never actually had to use one. And some SQL functions available in Firebird have yet to appear in other engines, and having to decide if you want the security InnoDB provides is simply standard in other engines. The first question is are you hosting yourself or using third party hosting? MySQL tends to be available on all third party posting, with some providing Postgres, while Firebird tends to be privately hosted. If you are hosting yourself, then of cause MySQL may actually be MariaDB and you end up with a mix of sources. It's a bit like Internbase and Firebird where the commercial charges can affect one installation where the other is totally free. If you are only looking for a single installation, then MySQL is probably fine. I'm running 50+ databases and with Firebird each is isolated in it's own directory and automatically backs up to the website storage area. -- Lester Caine - G8HFL - Interesting. I program in MySQL on a hosting plan by a third party. I have heard/read MySQL is not an enterprise solution, but for the basic business with say less than 100,000 customers, it does the job and well. Larger than that I had hear Postgres and oracle were good to look at. Havent heard any good things about SQL server (.NET), but did't have too much trouble working with one a few years back. I guess I don't know enough about what is available to do with a good database and which to pick to do what I want with. There are so many. Hence my question here. Again, thanks for your response. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On 16/05/15 14:51, Karl DeSaulniers wrote: Interesting. I program in MySQL on a hosting plan by a third party. I have heard/read MySQL is not an enterprise solution, but for the basic business with say less than 100,000 customers, it does the job and well. Larger than that I had hear Postgres and oracle were good to look at. Havent heard any good things about SQL server (.NET), but did't have too much trouble working with one a few years back. I guess I don't know enough about what is available to do with a good database and which to pick to do what I want with. There are so many. Hence my question here. That probably sums up 'hosted' plans. The number of available database engines has declined in recent years, and where a site 'outgrows' MySQL, there are a few custom developments, but bottom line ... there is not a single obvious answer ;) -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On 15/05/15 06:21, Karl DeSaulniers wrote: Oh ok. Now it makes a little more sense. I have worked in ASP before, but I am programming in PHP and MySQL at the moment. I am going to look into Prepared Statements. Thanks for your feedback. Just to clarify things a little here and explain http://php.net/manual/en/pdo.prepared-statements.php a little more ... Many of the legacy injection problems where/are caused by building up the query as a fully self contained string. Various methods like 'magic_quotes' and wrapping $var in things like makesafe($var) were the only way some database engines could handle adding variables to the SQL string and much code still follows that style even today. Other database engines have always had the ability to pass the variables as a separate array of data, and the @x is more normally seen as a simple ? in the SQL string, so PDO and other frameworks map the ':var' elements of the first example to the relevant style used by the database. Actually naming parameters is not the norm, so one has to have the right number of '?' elements to go with the array of data passed, so PDO is adding a layer of code which hides the underlying execute(sql_query, array_of_data); -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On May 16, 2015, at 3:51 AM, Lester Caine les...@lsces.co.uk wrote: On 15/05/15 06:21, Karl DeSaulniers wrote: Oh ok. Now it makes a little more sense. I have worked in ASP before, but I am programming in PHP and MySQL at the moment. I am going to look into Prepared Statements. Thanks for your feedback. Just to clarify things a little here and explain http://php.net/manual/en/pdo.prepared-statements.php a little more ... Many of the legacy injection problems where/are caused by building up the query as a fully self contained string. Various methods like 'magic_quotes' and wrapping $var in things like makesafe($var) were the only way some database engines could handle adding variables to the SQL string and much code still follows that style even today. Other database engines have always had the ability to pass the variables as a separate array of data, and the @x is more normally seen as a simple ? in the SQL string, so PDO and other frameworks map the ':var' elements of the first example to the relevant style used by the database. Actually naming parameters is not the norm, so one has to have the right number of '?' elements to go with the array of data passed, so PDO is adding a layer of code which hides the underlying execute(sql_query, array_of_data); -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk Thank you Lester. That does clarify things a bit better on both the @ question and prepared statements. Thank you for the link as well. So new question.. what is the best type of database to use for someone who wants to start small and grow big? My findings led me to MySQL InnoDB. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On 15.05.2015 07:21, Karl DeSaulniers wrote: On May 14, 2015, at 11:11 PM, Onatawahtaw onatawah...@yahoo.ca wrote: Hi Karl, If you look at the link you provided you'll notice that some of the code is for ASP.net and some is for PHP. I have looked in the link. Most problems by inject an sql-Code is to add something in the where-clause let it end with a semicolon and add an additional sql-command behind the semicolon. In this case you have two SQL-Command. The first maybe a Select-Command and the next can be to drop a whole table with all its content. One thing you can do is to trim the Select-Statement and trough all behind a semicolon in addition the semicolon away. Another securitymethod of mysql that the fieldvarables are capseled by escaping. So mysql get note that this is a variable content for a formfield and should looked like that. Regards, Ruprecht -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
-Kevin Waddell Proverbs 3:5-6 On Fri, 5/15/15, Ruprecht Helms rhe...@rheynmail.de wrote: Subject: Re: [PHP-DB] SQL Injection To: php-db@lists.php.net Date: Friday, May 15, 2015, 10:16 AM On 15.05.2015 07:21, Karl DeSaulniers wrote: On May 14, 2015, at 11:11 PM, Onatawahtaw onatawah...@yahoo.ca wrote: Hi Karl, If you look at the link you provided you'll notice that some of the code is for ASP.net and some is for PHP. I have looked in the link. Most problems by inject an sql-Code is to add something in the where-clause let it end with a semicolon and add an additional sql-command behind the semicolon. In this case you have two SQL-Command. The first maybe a Select-Command and the next can be to drop a whole table with all its content. One thing you can do is to trim the Select-Statement and trough all behind a semicolon in addition the semicolon away. To assume that any SQL injection is going to be by a second statement is very risky. Someone can simply add an or section to the where clause. It will still be one SQL statement and your trim will have done nothing to solve the problem. It is best not to make that assumption and not bother with trimming. Also, it would be tricky finding a proper trim function especially if your form input should contain semi-colons, etc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello Everyone, Have a quick question. Was reading some material and wanted some Players perspective. I know w3schools is not the de-facto on everything, so I wanted to know how reliable is the information on this page. http://www.w3schools.com/sql/sql_injection.asp Namely the @ symbol before SQL Values and because this talks about SQL and not MySQL specifically, does this not apply to MySQL? To my uneducated eyes it seems legit. Any clarification is greatly appreciated. TIA, Best, Karl DeSaulniers Design Drumm http://designdrumm.com That is preferred in PHP as well. The SQL/MySQL isn't specifically doing the replacement, but rather the driver object. Using parametrized queries: http://php.net/manual/en/pdo.prepared-statements.php
Re: [PHP-DB] SQL Injection
On May 14, 2015, at 8:09 PM, Aziz Saleh azizsa...@gmail.com wrote: On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello Everyone, Have a quick question. Was reading some material and wanted some Players perspective. I know w3schools is not the de-facto on everything, so I wanted to know how reliable is the information on this page. http://www.w3schools.com/sql/sql_injection.asp Namely the @ symbol before SQL Values and because this talks about SQL and not MySQL specifically, does this not apply to MySQL? To my uneducated eyes it seems legit. Any clarification is greatly appreciated. TIA, Best, Karl DeSaulniers Design Drumm http://designdrumm.com That is preferred in PHP as well. The SQL/MySQL isn't specifically doing the replacement, but rather the driver object. Using parametrized queries: http://php.net/manual/en/pdo.prepared-statements.php Thank you Aziz, Interesting link, thank you for that. I have not worked with prepared statements on my own, just in WordPress. So the @ symbol is a preferred method even outside the SQL world because? What specifically is the @ symbol doing? From what I read, and from what you just mentioned, it's the PHP-SQL driver that check this @ symbol and treats the data as literal text? Meaning it will not execute the text that comes after the @ symbol as code. Yes? Best, Karl DeSaulniers Design Drumm http://designdrumm.com
Re: [PHP-DB] SQL Injection
On 15/05/14 18:19 , Karl DeSaulniers wrote: On May 14, 2015, at 8:09 PM, Aziz Saleh azizsa...@gmail.com wrote: On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello Everyone, Have a quick question. Was reading some material and wanted some Players perspective. I know w3schools is not the de-facto on everything, so I wanted to know how reliable is the information on this page. http://www.w3schools.com/sql/sql_injection.asp Namely the @ symbol before SQL Values and because this talks about SQL and not MySQL specifically, does this not apply to MySQL? To my uneducated eyes it seems legit. Any clarification is greatly appreciated. TIA, Best, Karl DeSaulniers Design Drumm http://designdrumm.com That is preferred in PHP as well. The SQL/MySQL isn't specifically doing the replacement, but rather the driver object. Using parametrized queries: http://php.net/manual/en/pdo.prepared-statements.php Thank you Aziz, Interesting link, thank you for that. I have not worked with prepared statements on my own, just in WordPress. So the @ symbol is a preferred method even outside the SQL world because? What specifically is the @ symbol doing? From what I read, and from what you just mentioned, it's the PHP-SQL driver that check this @ symbol and treats the data as literal text? Meaning it will not execute the text that comes after the @ symbol as code. If I understand correctly it is not the @ symbol itself which is the thing you should be looking at. What you should be looking at is how your programming language handles prepared statements. What I see is that the @ symbol is how ASP.Net defines the variable name, and also the variable position. I am not sure about this, but it looks like PHP uses : for the same function. I am even less sure about this, but I think with prepared statements you can also define what type of data is being passed. So if you try to pass a string (ie. something that cannot be converted to a number) to a number defined variable, you will get an error thrown. If you use a catch statement that error can be handled by your code, rather than PHP handling it in default manner. It really has been a long time since I have been hands on with any of this, and there is a good chance at least some of what I am saying is wrong. The point of prepared statements is that what variables you are passing through them, they are passed as literal values, rather than simply putting them through as straight text put into your string you are passing to SQL. Even if the string ends up breaking your query in a way that can harm either security of data, or your database itself (also a security issue), it is not passed in a way that SQL handles as such. I discovered an issue on one of the web apps I used where I would get a SQL error message if I entered certain strings into the input field. Even though what I was doing wasn't at all trying to test for it, my inputs made it clear what was going on. With that amount of what is going on figured out. I could send a meaningful bug report that got this issue fixed. Most people using the site would have had no idea what was happening. If I recall, I was putting a or ' in my input, thus closing the string, which then left the rest being interpreted as SQL code. -- Datse Multimedia Productions http://DatseMultimedia.com/ Tel:250-362-5701 Mobile: 250-354-7094 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On May 14, 2015, at 8:37 PM, Jigme Datse Yli-Rasku jigme.da...@gmail.com wrote: On 15/05/14 18:19 , Karl DeSaulniers wrote: On May 14, 2015, at 8:09 PM, Aziz Saleh azizsa...@gmail.com wrote: On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers k...@designdrumm.com wrote: Hello Everyone, Have a quick question. Was reading some material and wanted some Players perspective. I know w3schools is not the de-facto on everything, so I wanted to know how reliable is the information on this page. http://www.w3schools.com/sql/sql_injection.asp Namely the @ symbol before SQL Values and because this talks about SQL and not MySQL specifically, does this not apply to MySQL? To my uneducated eyes it seems legit. Any clarification is greatly appreciated. TIA, Best, Karl DeSaulniers Design Drumm http://designdrumm.com That is preferred in PHP as well. The SQL/MySQL isn't specifically doing the replacement, but rather the driver object. Using parametrized queries: http://php.net/manual/en/pdo.prepared-statements.php Thank you Aziz, Interesting link, thank you for that. I have not worked with prepared statements on my own, just in WordPress. So the @ symbol is a preferred method even outside the SQL world because? What specifically is the @ symbol doing? From what I read, and from what you just mentioned, it's the PHP-SQL driver that check this @ symbol and treats the data as literal text? Meaning it will not execute the text that comes after the @ symbol as code. If I understand correctly it is not the @ symbol itself which is the thing you should be looking at. What you should be looking at is how your programming language handles prepared statements. What I see is that the @ symbol is how ASP.Net defines the variable name, and also the variable position. I am not sure about this, but it looks like PHP uses : for the same function. I am even less sure about this, but I think with prepared statements you can also define what type of data is being passed. So if you try to pass a string (ie. something that cannot be converted to a number) to a number defined variable, you will get an error thrown. If you use a catch statement that error can be handled by your code, rather than PHP handling it in default manner. It really has been a long time since I have been hands on with any of this, and there is a good chance at least some of what I am saying is wrong. The point of prepared statements is that what variables you are passing through them, they are passed as literal values, rather than simply putting them through as straight text put into your string you are passing to SQL. Even if the string ends up breaking your query in a way that can harm either security of data, or your database itself (also a security issue), it is not passed in a way that SQL handles as such. I discovered an issue on one of the web apps I used where I would get a SQL error message if I entered certain strings into the input field. Even though what I was doing wasn't at all trying to test for it, my inputs made it clear what was going on. With that amount of what is going on figured out. I could send a meaningful bug report that got this issue fixed. Most people using the site would have had no idea what was happening. If I recall, I was putting a or ' in my input, thus closing the string, which then left the rest being interpreted as SQL code. Thanks Jigme, Ok, so understand my own situation, the method I have been using, mysqli real escape string is suffice? Or is the @ symbol is the better preferred method? Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
On May 14, 2015, at 11:11 PM, Onatawahtaw onatawah...@yahoo.ca wrote: Hi Karl, If you look at the link you provided you'll notice that some of the code is for ASP.net and some is for PHP. What of the two are you programming in? If you are programming in ASP.net you are asking your question to the wrong mailing list as this list is for PHP. If you are programming in PHP, then the @ symbol does not apply to you. Both prepared statements and mysqli_real_escape_string do provide adequate security (if used correctly). However, my recommendation is to learn how to use PDO with prepared statements. PDO also offers the benefit of being able to connect to multiple types of databases without needing to change your code. If you use mysqli and down the road you decide you want to use Oracle, MS SQL Server, or some other database server, you will ned to rework a lot of your code. Not so with PDO. Hope this helps, -Kevin Waddell Proverbs 3:5-6 Oh ok. Now it makes a little more sense. I have worked in ASP before, but I am programming in PHP and MySQL at the moment. I am going to look into Prepared Statements. Thanks for your feedback. Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection
Hi Karl, If you look at the link you provided you'll notice that some of the code is for ASP.net and some is for PHP. What of the two are you programming in? If you are programming in ASP.net you are asking your question to the wrong mailing list as this list is for PHP. If you are programming in PHP, then the @ symbol does not apply to you. Both prepared statements and mysqli_real_escape_string do provide adequate security (if used correctly). However, my recommendation is to learn how to use PDO with prepared statements. PDO also offers the benefit of being able to connect to multiple types of databases without needing to change your code. If you use mysqli and down the road you decide you want to use Oracle, MS SQL Server, or some other database server, you will ned to rework a lot of your code. Not so with PDO. Hope this helps, -Kevin Waddell Proverbs 3:5-6 On Thu, 5/14/15, Karl DeSaulniers k...@designdrumm.com wrote: Ok, so understand my own situation, the method I have been using, mysqli real escape string is suffice? Or is the @ symbol is the better preferred method? Best, Karl DeSaulniers Design Drumm http://designdrumm.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL syntax
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus IN ( 'Cancelled', 'New', 'Denied',
'Expired' , 'Failed' , 'Pending' , 'Refunded' , 'Reversed' , 'Under Review'
, 'Voided') AND orderdate '.mysqli_real_escape_string($
yesterday);
Another option would be to use either of these functions
-
Find-in-sethttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set.
This is useful if your data type is SET/ENUM type
-
Fieldhttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
On Tue, Jan 15, 2013 at 2:59 PM, Karl DeSaulniers k...@designdrumm.comwrote:
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus = 'Cancelled' OR (orderstatus = ('New'
OR 'Denied' OR 'Expired' OR 'Failed' OR 'Pending' OR 'Refunded' OR
'Reversed' OR 'Under Review' OR 'Voided') AND orderdate
'.mysqli_real_escape_string($**yesterday).');
regds
amit
The difference between fiction and reality? Fiction has to make sense.
Re: [PHP-DB] SQL syntax
On Jan 15, 2013, at 5:25 AM, Amit Tandon wrote:
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus IN ( 'Cancelled', 'New', 'Denied',
'Expired' , 'Failed' , 'Pending' , 'Refunded' , 'Reversed' , 'Under
Review'
, 'Voided') AND orderdate '.mysqli_real_escape_string($
yesterday);
Another option would be to use either of these functions
- Find-in-sethttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set
.
This is useful if your data type is SET/ENUM type
- Fieldhttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field
On Tue, Jan 15, 2013 at 2:59 PM, Karl DeSaulniers k...@designdrumm.com
wrote:
SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus = 'Cancelled' OR (orderstatus =
('New'
OR 'Denied' OR 'Expired' OR 'Failed' OR 'Pending' OR 'Refunded' OR
'Reversed' OR 'Under Review' OR 'Voided') AND orderdate
'.mysqli_real_escape_string($**yesterday).');
regds
amit
The difference between fiction and reality? Fiction has to make
sense.
I am wanting Cancelled to be without a date check, but thanks for the
suggestion.
I will try the IN option.
Thank you.
Best,
Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL omit record if ever had value set
On Mon, Jun 18, 2012 at 6:26 PM, Matijn Woudt tijn...@gmail.com wrote: On Mon, Jun 18, 2012 at 11:56 PM, Dee Ayy dee@gmail.com wrote: I would like a query that lists records where a column has not taken on a specific value when grouped by another column. N V n1 v1 n1 v2 n2 v1 n2 v2 n2 v3 n3 v1 If v3 has ever been set for N, do not list N. So the result would be n1, n3 If v2 has ever been set for N, do not list N. So the result would be only n3 MSSQL TIA I guess this should work in MSSQL too: SELECT N FROM table WHERE N NOT IN (SELECT N FROM table WHERE v = v3) - Matijn Thank you. So simple. I must have had Monday afternoon burnout. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL omit record if ever had value set
On Mon, Jun 18, 2012 at 11:56 PM, Dee Ayy dee@gmail.com wrote: I would like a query that lists records where a column has not taken on a specific value when grouped by another column. N V n1 v1 n1 v2 n2 v1 n2 v2 n2 v3 n3 v1 If v3 has ever been set for N, do not list N. So the result would be n1, n3 If v2 has ever been set for N, do not list N. So the result would be only n3 MSSQL TIA I guess this should work in MSSQL too: SELECT N FROM table WHERE N NOT IN (SELECT N FROM table WHERE v = v3) - Matijn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL for counting comments - is this smart?
Chris your answer is the better solution, I thinked that the option suggest
by Martin was fine.
anywhere, tanks for help us to improve us code.
Gerardo.
2009/3/16 Chris dmag...@gmail.com
Martin Zvarík wrote:
Is it smart to use all of this on one page?
Or should I rather do one SQL and let PHP count it?
$q = $DB-q(SELECT COUNT(*) FROM comments);
$int_total = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved IS NULL);
$int_waiting = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved=0);
$int_deleted = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved=1);
$int_approved = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved=2);
$int_banned = $DB-frow($q);
Each one of these probably going to scan the whole table because the
approved column isn't going to be selective enough to use an index.
You might be better off doing:
select approved, count(*) from comments group by approved;
then in php separating them out:
while ($row = $DB-frow($q)) {
switch ($row['approved']) {
case null:
$waiting = $row['count'];
break;
case 0:
$deleted = $row['count'];
break;
case 1:
$approved = $row['count'];
break;
}
}
$total = $waiting + $approved + $deleted;
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL for counting comments - is this smart?
Martin Zvarík wrote:
Is it smart to use all of this on one page?
Or should I rather do one SQL and let PHP count it?
$q = $DB-q(SELECT COUNT(*) FROM comments);
$int_total = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved IS NULL);
$int_waiting = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved=0);
$int_deleted = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved=1);
$int_approved = $DB-frow($q);
$q = $DB-q(SELECT COUNT(*) FROM comments WHERE approved=2);
$int_banned = $DB-frow($q);
Each one of these probably going to scan the whole table because the
approved column isn't going to be selective enough to use an index.
You might be better off doing:
select approved, count(*) from comments group by approved;
then in php separating them out:
while ($row = $DB-frow($q)) {
switch ($row['approved']) {
case null:
$waiting = $row['count'];
break;
case 0:
$deleted = $row['count'];
break;
case 1:
$approved = $row['count'];
break;
}
}
$total = $waiting + $approved + $deleted;
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql injections/best practises
Thank you Christopher - this gives me some much needed direction. --- On Fri, 11/7/08, Christopher Jones [EMAIL PROTECTED] wrote: From: Christopher Jones [EMAIL PROTECTED] Subject: Re: [PHP-DB] sql injections/best practises To: [EMAIL PROTECTED] Cc: php-db@lists.php.net Date: Friday, November 7, 2008, 5:39 PM mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. I'm starting to redesign a corporate website and am trying to find out more about security and the best practises for database queries and user input form handling. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? I have checked out the security area on the php manual and some users notes - some were useful. But it didnt really have a lot of info and I dont think it is comprehenive or all inclusive. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? thanks PHP 5.2 is the way to go for new projects: PHP 4 isn't being maintained. Binding/preparing statements is the way to go. Here are quotes about them with MySQL Oracle They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks. (From PHP and MySQL Web Development, 4th Edition, Luke Welling and Laura Thomson) If I were to write a book about how to build nonscalable [note the NON] Oracle applications, then 'Don't Use Bind Variables' would be the title of the first and last chapters. [...] If you want to make Oracle run slowly [...] just refuse to use bind variables (From Expert Oracle Database Architecture, Tom Kyte) Depending on the site needs, consider a DB abstraction layer or a framework. For high performance connections in PHP OCI8 for Oracle, use oci_pconnect() and pass the character set. There are a number of Oracle-PHP books available. One free, introductory one is the Underground PHP Oracle Manual, http://tinyurl.com/f8jad (A new edition will be released in the next couple of weeks) Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad
Re: [PHP-DB] sql injections/best practises
thank you so much Fergus for all this great info - this will get me started. --- On Sat, 11/8/08, Fergus Gibson [EMAIL PROTECTED] wrote: From: Fergus Gibson [EMAIL PROTECTED] Subject: Re: [PHP-DB] sql injections/best practises To: php-db@lists.php.net Date: Saturday, November 8, 2008, 12:42 PM On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones [EMAIL PROTECTED] wrote: mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. Most security issues come back to a simple concept. Assume anything in your scripts that is not a constant or literal to be a threat. That means any and all user submitted data is a potential attack. Ideally you should also assume that any and all data read in from the database or files is a potential attack. Assume everything is tainted. Your job then is to clean any and all input through inspection and filtering before you use it. I recommend the book Essential PHP Security by Chris Shiflett (ISBN 0-596-00656-X). It deals with database security and more. I would be happy to go into more detail on this or provide examples if it would be helpful. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? Prepared statements will prevent SQL injection, but that is only one potential vector for attack. Keep in mind too that prepared statements are not necessary to prevent SQL injection and they aren't always the most appropriate way to do it. That said, they are the simplest way to protect your database. I'll outline a way that a database was used to attack an application. The attack wasn't particularly dangerous, but it was embarrassing for the company involved. In this case, the application took form input from a site visitor and saved it in the database. Then the site owner could retrieve the input and view it. Unfortunately, some visitors decided to put script tags in containing a Javascript redirect. Since the application trusted the data coming back from the database (not a best practice), it didn't attempt to filter it in anyway before sending it to the browser. The result was that when the site owner tried to retrieve the form submission data, he would find himself redirect to another website of the attacker's choosing. While no data was compromised in the attack, it did raise doubts about the security of that company's products. This kind of attack could easily be prevented by assuming that the data coming out of the database is tainted and then filtering it with htmlentities(). The result of that would have been that the script didn't run and didn't redirect the browser. This was the solution that the company implemented. I hope this example highlights why it's important to have a full understanding of security and related best practices. Just understanding methods to defeat SQL injection is not enough to ensure that your application is secure, and the aforementioned book will give you a security mindset that you can apply to all threat vectors. You also asked about PHP versions. I do recommend you use PHP 5. As mentioned, PHP 4.4.9 is the last release of PHP 4. There is no promise to address any further security issues in PHP 4 if they are discovered. PHP 5 also has other, non-security advantages over PHP 4. Most notable is a robust object model for we OOP types, but I also like decisions they made to bundle in certain modules missing from PHP 4. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? Ugh. That's my comment. I assume we're discussion Neon here, the new Eclipse-based Zend Studio. The installation is huge and bloated, and I find it doesn't work very well at all for remote files over FTP. I really didn't care for it. If you love Eclipse, though, you will probably like it. I believe there's a free trial of the Studio, so you should try it rather than listening too much to opinions from the peanut gallery. I use UEStudio. It's not perfect, but it's a very robust, general programmers' editor. It's much faster and it makes difficult Eclipse tasks easy. It also has full Javascript scripting built into it, so it's very extensible. You can download a trial: http://www.ultraedit.com/downloads/uestudio_download.html Depending on the site needs, consider a DB abstraction layer or a framework. You can rely on frameworks to provide security to your application, but keep in mind that frameworks can contain vulnerabilities and bugs. They are made by people who can make mistakes. More significantly, if you are making an intensive application, you may find it reaches a point where the framework isn't scalable. I love and use abstraction, but abstraction does come with a performance price. For simple
Re: [PHP-DB] sql injections/best practises
On Mon, Nov 10, 2008 at 8:49 AM, mignon hunter [EMAIL PROTECTED] wrote: One other question. Our current site is written in jsp with Oracle. I'd like to use PHP. Do you have any thoughts on this? Your post, mignon, was pretty clearly directed to Christopher, but I hope neither of you will be unhappy if I make my own comment. I look forward to seeing what Christopher thinks as well, but I do have some thoughts on this. First, I will say that I have never worked with JSP. I have worked with Java, and I rather enjoy it, but I have not travelled down any of the JSP trail. For that reason, I'm not going to attempt to assess JSP specifically, other than to say that it seems rather more time-consuming to learn than PHP. There is a simple reason that I choose PHP exclusively for web development. It has nothing to do with any intrinsic value of PHP. I actually think PHP is the worst designed platform I've ever worked with. There is so much about it I really don't like. But it has one overriding strength: it's everywhere. It's simple to integrate with any server, it's widely available, and hosting for other technologies like JSP, Ruby, and Python tends to be more expensive than PHP. And don't even get me started on ASP and ASP.Net, which are only truly supported on (shudder) MS servers. Now all of this said, I am leery of your idea to switch to PHP for this application you are working on. If you're not planning to move servers, the wider support for PHP isn't an advantage at all. Switching technologies because you think PHP is somehow cooler is a poor justification for what could be a costly exercise for the client, so you ought to have a really practical explanation. Why don't you want to continue working with JSP? Is there really a problem in using that platform? We're not really using Jsp as it was intended ( like using classes ) and I think it has alot of overhead and is overkill. It seems Php would be a better choice for imbedded html. This is probably true, but I am no authority on JSP. But in most cases, embedding PHP in HTML is not a best practice. Most professionals agree that there is a real value in separating presentation (HTML in this case) from logic (the PHP code). This is something all the frameworks attempt to help you do. This same idea was the impetus for the rise of model-view-controller (MVC) design pattern, its later application to web projects, and the development of Cascading Stylesheets (CSS) to separate presentation of web content from structure (HTML). As I mentioned previously, I am a little reluctant to use frameworks. For this reason I have implemented my own simple template script that allows me to put all my PHP logic in one file and all my presentation in a separate template file. My approach is similar to that of Brian Lozer. Brian is the author of bTemplate, but abandoned its development when he hit upon the real weakness in template engines and frameworks. Here's a link to his article on the subject. I'm not suggesting anyone use bTemplate; I'm encouraging people to understand why he decided it wasn't productive to use a convention template engine in the first place. http://massassi.com/php/articles/template_engines/ All the above notwithstanding, there's always an argument for saying a script is so simple the benefits of abstraction or design patterns or MVC are not of much value. But I think there are very few applications where this is true, and worst of all, I tend to find that even if you start off thinking that something is so simple and straightforward you should just bang it out in the most simple and direct way, you'll end up regretting it because the concept wasn't as simple as you thought or because the client keeps adding on to his original goal for the script 'til it becomes a messy monster you wished you designed properly in the first place... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql injections/best practises
Hi Christopher One other question. Our current site is written in jsp with Oracle. I'd like to use PHP. Do you have any thoughts on this? We're not really using Jsp as it was intended ( like using classes ) and I think it has alot of overhead and is overkill. It seems Php would be a better choice for imbedded html. For the most part the site mainly consist of relatively simple db retrieval, for several of our products. Which then lists various documentation and reference material for each, all dynamic. And then we have a few very simple stand alone user input forms occasionally. Oracle is the db on most of the site - a little mysql too. --- On Fri, 11/7/08, Christopher Jones [EMAIL PROTECTED] wrote: From: Christopher Jones [EMAIL PROTECTED] Subject: Re: [PHP-DB] sql injections/best practises To: [EMAIL PROTECTED] Cc: php-db@lists.php.net Date: Friday, November 7, 2008, 5:39 PM mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. I'm starting to redesign a corporate website and am trying to find out more about security and the best practises for database queries and user input form handling. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? I have checked out the security area on the php manual and some users notes - some were useful. But it didnt really have a lot of info and I dont think it is comprehenive or all inclusive. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? thanks PHP 5.2 is the way to go for new projects: PHP 4 isn't being maintained. Binding/preparing statements is the way to go. Here are quotes about them with MySQL Oracle They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks. (From PHP and MySQL Web Development, 4th Edition, Luke Welling and Laura Thomson) If I were to write a book about how to build nonscalable [note the NON] Oracle applications, then 'Don't Use Bind Variables' would be the title of the first and last chapters. [...] If you want to make Oracle run slowly [...] just refuse to use bind variables (From Expert Oracle Database Architecture, Tom Kyte) Depending on the site needs, consider a DB abstraction layer or a framework. For high performance connections in PHP OCI8 for Oracle, use oci_pconnect() and pass the character set. There are a number of Oracle-PHP books available. One free, introductory one is the Underground PHP Oracle Manual, http://tinyurl.com/f8jad (A new edition will be released in the next couple of weeks) Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql injections/best practises
mignon hunter wrote: Hi Christopher One other question. Our current site is written in jsp with Oracle. I'd like to use PHP. Do you have any thoughts on this? My recommendation is to utilize the existing skills you have; this echoes Fergus's comment. However, PHP is very popular and if you have the luxury of being able to learn a new language, choosing PHP is not like choosing an esoteric language that someone will struggle to maintain when you move on. We're not really using Jsp as it was intended ( like using classes ) and I think it has alot of overhead and is overkill. It seems Php would be a better choice for imbedded html. For the most part the site mainly consist of relatively simple db retrieval, for several of our products. Which then lists various documentation and reference material for each, all dynamic. And then we have a few very simple stand alone user input forms occasionally. Oracle is the db on most of the site - a little mysql too. PHP will certainly help you get a working website up quickly. Oracle can easily be accessed in PHP to do the things you describe. Discussion of frameworks and abstraction layers is just a way to make you aware of their place and to ensure the application is architected to suit your current future requirements. Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql injections/best practises
On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones [EMAIL PROTECTED] wrote: mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. Most security issues come back to a simple concept. Assume anything in your scripts that is not a constant or literal to be a threat. That means any and all user submitted data is a potential attack. Ideally you should also assume that any and all data read in from the database or files is a potential attack. Assume everything is tainted. Your job then is to clean any and all input through inspection and filtering before you use it. I recommend the book Essential PHP Security by Chris Shiflett (ISBN 0-596-00656-X). It deals with database security and more. I would be happy to go into more detail on this or provide examples if it would be helpful. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? Prepared statements will prevent SQL injection, but that is only one potential vector for attack. Keep in mind too that prepared statements are not necessary to prevent SQL injection and they aren't always the most appropriate way to do it. That said, they are the simplest way to protect your database. I'll outline a way that a database was used to attack an application. The attack wasn't particularly dangerous, but it was embarrassing for the company involved. In this case, the application took form input from a site visitor and saved it in the database. Then the site owner could retrieve the input and view it. Unfortunately, some visitors decided to put script tags in containing a Javascript redirect. Since the application trusted the data coming back from the database (not a best practice), it didn't attempt to filter it in anyway before sending it to the browser. The result was that when the site owner tried to retrieve the form submission data, he would find himself redirect to another website of the attacker's choosing. While no data was compromised in the attack, it did raise doubts about the security of that company's products. This kind of attack could easily be prevented by assuming that the data coming out of the database is tainted and then filtering it with htmlentities(). The result of that would have been that the script didn't run and didn't redirect the browser. This was the solution that the company implemented. I hope this example highlights why it's important to have a full understanding of security and related best practices. Just understanding methods to defeat SQL injection is not enough to ensure that your application is secure, and the aforementioned book will give you a security mindset that you can apply to all threat vectors. You also asked about PHP versions. I do recommend you use PHP 5. As mentioned, PHP 4.4.9 is the last release of PHP 4. There is no promise to address any further security issues in PHP 4 if they are discovered. PHP 5 also has other, non-security advantages over PHP 4. Most notable is a robust object model for we OOP types, but I also like decisions they made to bundle in certain modules missing from PHP 4. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? Ugh. That's my comment. I assume we're discussion Neon here, the new Eclipse-based Zend Studio. The installation is huge and bloated, and I find it doesn't work very well at all for remote files over FTP. I really didn't care for it. If you love Eclipse, though, you will probably like it. I believe there's a free trial of the Studio, so you should try it rather than listening too much to opinions from the peanut gallery. I use UEStudio. It's not perfect, but it's a very robust, general programmers' editor. It's much faster and it makes difficult Eclipse tasks easy. It also has full Javascript scripting built into it, so it's very extensible. You can download a trial: http://www.ultraedit.com/downloads/uestudio_download.html Depending on the site needs, consider a DB abstraction layer or a framework. You can rely on frameworks to provide security to your application, but keep in mind that frameworks can contain vulnerabilities and bugs. They are made by people who can make mistakes. More significantly, if you are making an intensive application, you may find it reaches a point where the framework isn't scalable. I love and use abstraction, but abstraction does come with a performance price. For simple things, this cost is so slight you won't even notice it; but there is a point where the cost becomes significant. There's no simple way to evaluate that, though, because it depends on so many factors: traffic, server resources, specifics of the application, etc. I tend to stay away from frameworks
Re: [PHP-DB] sql injections/best practises
mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. I'm starting to redesign a corporate website and am trying to find out more about security and the best practises for database queries and user input form handling. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? I have checked out the security area on the php manual and some users notes - some were useful. But it didnt really have a lot of info and I dont think it is comprehenive or all inclusive. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? thanks PHP 5.2 is the way to go for new projects: PHP 4 isn't being maintained. Binding/preparing statements is the way to go. Here are quotes about them with MySQL Oracle They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks. (From PHP and MySQL Web Development, 4th Edition, Luke Welling and Laura Thomson) If I were to write a book about how to build nonscalable [note the NON] Oracle applications, then 'Don't Use Bind Variables' would be the title of the first and last chapters. [...] If you want to make Oracle run slowly [...] just refuse to use bind variables (From Expert Oracle Database Architecture, Tom Kyte) Depending on the site needs, consider a DB abstraction layer or a framework. For high performance connections in PHP OCI8 for Oracle, use oci_pconnect() and pass the character set. There are a number of Oracle-PHP books available. One free, introductory one is the Underground PHP Oracle Manual, http://tinyurl.com/f8jad (A new edition will be released in the next couple of weeks) Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql problem
http://www.php.net/manual/en/function.number-format.php bastien Date: Sun, 16 Dec 2007 17:17:41 +0600 From: [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] sql problem my problem in the following code INSERT INTO `test` ( `debit` ) VALUES ( '2' ) when i search it shows like this: SELECT debit FROM `test` output is :2. but i have to show output :20,000.00 like input 2 output 20,000.00 input 3000 output 3,000.00 input 10 output 1,0.00 _ Read what Santa`s been up to! For all the latest, visit asksantaclaus.spaces.live.com! http://asksantaclaus.spaces.live.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
Bryan wrote: SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... select * from productgroup where groupid = $productid order by (label = 'Cats') desc, title And I do hope you're properly validating and escaping $productid. -Stut -- http://stut.net/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
Try this: SELECT * FROM productgroup WHERE groupid = $productid ORDER BY label = 'Cats' DESC, title The test SQL I did to make sure I understood it was this (against our Users table): select * from users order by first = 'Bob' DESC, first, last It put all the Bobs first, sorting them by first/last, then put everyone else after the Bobs sorted by first/last. If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list. Also refer to the user comments here: http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html good luck! -TG = = = Original message = = = SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
I think there's one small piece of data I left out. I'm working with php/mssql, no mysql. I'll move to mysql when I get everything else built. Mssql 2000 doesn't seem to like the = sign in the order by clause. It looks like both of you so far have come up with the same syntax though so it must work on mysql. ;-) Thanks guys... [EMAIL PROTECTED] wrote: Try this: SELECT * FROM productgroup WHERE groupid = $productid ORDER BY label = 'Cats' DESC, title The test SQL I did to make sure I understood it was this (against our Users table): select * from users order by first = 'Bob' DESC, first, last It put all the Bobs first, sorting them by first/last, then put everyone else after the Bobs sorted by first/last. If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list. Also refer to the user comments here: http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html good luck! -TG = = = Original message = = = SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql statement - complex order by
I think there's one small piece of data I left out. I'm working with php/mssql, not mysql. I'll move to mysql when I get everything else built. Mssql 2000 doesn't seem to like the = sign in the order by clause. It looks like both of you so far have come up with the same syntax though so it must work on mysql. ;-) Thanks guys... [EMAIL PROTECTED] wrote: Try this: SELECT * FROM productgroup WHERE groupid = $productid ORDER BY label = 'Cats' DESC, title The test SQL I did to make sure I understood it was this (against our Users table): select * from users order by first = 'Bob' DESC, first, last It put all the Bobs first, sorting them by first/last, then put everyone else after the Bobs sorted by first/last. If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the bottom of the list. Also refer to the user comments here: http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html good luck! -TG = = = Original message = = = SELECT * FROM productgroup WHERE groupid = $productid AND label = 'Cats' ORDER BY title SELECT * FROM productgroup WHERE groupid = $productid AND label != 'Cats' ORDER BY label,title I'd like to find a way to combine these 2 statements. I want to list out all the products, ordered by title but listing out all the Cats products first. Any way to do that without having separate statements? Thanks... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL unexpected T_CONSTANT_ENCAPSED_STRING Error
Hi Laitha, And with backslashes before them ?? jm - Original Message - From: Lasitha Alawatta [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Thursday, March 22, 2007 2:06 PM Subject: [PHP-DB] SQL unexpected T_CONSTANT_ENCAPSED_STRING Error Hi All, I have a sql script unable to execute. Because I need to insert double-coats ( ) also to one field(SPParams field). Ones I execuit this using mysql_query($sql), it gives Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING error message. $strSql = ; $strSql = $strSql.UPDATE Masters.tblBranches SET FHeadMail= '[EMAIL PROTECTED];', ; $strSql = $strSql.BranchDBConnection='Server=SLBC05; Database=BackOffice; UID=b0;pwd=sSLap.+05);', ; $strSql = $strSql.SPParams='pliplp id=spCustomersAddSLBC t=VarCharCustomerName/pp id=spCustomersAddSLBC t=TinyIntCustomerType/p' ; $strSql = $strSql.WHERE BranchCode=101; How can I be able to solve this issue..? Thank you very much, Lasitha - What kind of emailer are you? Find out today - get a free analysis of your email personality. Take the quiz at the Yahoo! Mail Championship. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
Matthew Ferry wrote:
Hello Everyone
Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.
The query event_time brings back the calendar id for each event that is
pending in the future.
ie 12, 13, 14, 26 (There could be 100 of them out there)
The second query events needs to meet both reqirements.
1 - cal_category='501'
2 - cal_id= a number from the event_time query
I think i need to do a loop inside of a loop
Thanks...
Matt
Here is my code:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start
$tstamp, $db);
This returns a mysql result set...not the actual data...
search php.net for the function mysql_fetch_array or others to actually
*get* the data.
(Some good examples there will help you sort this out!)
$events = mysql_query(SELECT * FROM egw_cal WHERE cal_category='501' and
cal_id='$event_time'\n, $db);
if ($event = mysql_fetch_array($events)) {
echo center\n;
echo HR\n;
do {
echo BFont
Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\n;
} while ($event = mysql_fetch_array($events));
} else {
echo No Public Events Are Currently Scheduled...;
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
Try this as your SQL. It should give you all the results, then you can use PHP to sort it all out. SELECT * FROM egw_cal WHERE cal_category='501' and cal_id in (SELECT cal_id FROM egw_cal_dates where cal_start $tstamp) -TG = = = Original message = = = Hello Everyone Got a simple / stupid question. Worked on this all night. I'm over looking something very basic here. The query event_time brings back the calendar id for each event that is pending in the future. ie 12, 13, 14, 26 (There could be 100 of them out there) The second query events needs to meet both reqirements. 1 - cal_category='501' 2 - cal_id= a number from the event_time query I think i need to do a loop inside of a loop Thanks... Matt Here is my code: ?php $todays_year = date(Y); $todays_month = date(m); $todays_day = date(d); $tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year); $event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start $tstamp, $db); $events = mysql_query(SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n, $db); if ($event = mysql_fetch_array($events)) echo center\n; echo HR\n; do echo BFont Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n; echo BR\n; echo $event[cal_description]; echo BR\n; echo HR\n; while ($event = mysql_fetch_array($events)); else echo No Public Events Are Currently Scheduled...; ? ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
This is a join - Read up on them, they're very useful and don't require
the overhead of a sub-query.
SELECT egw_cal.* FROM egw_cal_dates
LEFT JOIN egw_cal using (cal_id)
where egw_cal_dates.cal_start $tstamp
AND egw_cal.cal_category = '501'
-Micah
On 02/12/2007 08:14 AM, Matthew Ferry wrote:
Hello Everyone
Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.
The query event_time brings back the calendar id for each event that is
pending in the future.
ie 12, 13, 14, 26 (There could be 100 of them out there)
The second query events needs to meet both reqirements.
1 - cal_category='501'
2 - cal_id= a number from the event_time query
I think i need to do a loop inside of a loop
Thanks...
Matt
Here is my code:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start
$tstamp, $db);
$events = mysql_query(SELECT * FROM egw_cal WHERE cal_category='501' and
cal_id='$event_time'\n, $db);
if ($event = mysql_fetch_array($events)) {
echo center\n;
echo HR\n;
do {
echo BFont
Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\n;
} while ($event = mysql_fetch_array($events));
} else {
echo No Public Events Are Currently Scheduled...;
}
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
Thanks Everyone...
After I sent that...I got thinking about doing both queries in one statement.
So thats what I did.
Its working fine...
Here is the updated code:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$events = mysql_query(SELECT DISTINCT * FROM egw_cal, egw_cal_dates WHERE
egw_cal.cal_category='501'
and egw_cal_dates.cal_start '$tstamp' and
egw_cal.cal_id=egw_cal_dates.cal_id, $db);
if ($event = mysql_fetch_array($events)) {
echo center\n;
echo HR\n;
do {
echo BFont
Face='Times'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/Font/B\n;
echo BR\n;
$start = date('F jS\, Y \a\t g:ia', $event[cal_start]);
echo Starting Date/Time:nbsp;nbsp; $start;
echo BR\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\n;
} while ($event = mysql_fetch_array($events));
} else {
echo No Public Events Are Currently Scheduled...;
}
?
- Original Message -
From: Matthew Ferry [EMAIL PROTECTED]
To: php-db@lists.php.net
Sent: Monday, February 12, 2007 11:14 AM
Subject: [PHP-DB] SQL Query - Using variable from another SQL Query
Hello Everyone
Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.
The query event_time brings back the calendar id for each event that is
pending in the future.
ie 12, 13, 14, 26 (There could be 100 of them out there)
The second query events needs to meet both reqirements.
1 - cal_category='501'
2 - cal_id= a number from the event_time query
I think i need to do a loop inside of a loop
Thanks...
Matt
Here is my code:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start
$tstamp, $db);
$events = mysql_query(SELECT * FROM egw_cal WHERE cal_category='501' and
cal_id='$event_time'\n, $db);
if ($event = mysql_fetch_array($events)) {
echo center\n;
echo HR\n;
do {
echo BFont
Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\n;
} while ($event = mysql_fetch_array($events));
} else {
echo No Public Events Are Currently Scheduled...;
}
?
Re: [PHP-DB] SQL Performance Help
Tony Grimes wrote: I'm developing a course calendar for a client and I'm running into performance problems with the admin site. For example, when I try to include registration counts in the course list, the page really slows down for large course lists (50 or so): COURSEATTENDEES CAPACITYSEATS LEFT === == Course 1 5 10 5 Course 2 6 15 9 Course 3 4 10 6 I've been using one query to retrieve the course list and then one for each attendee count. Is there a more efficient way of doing this all in one query? I was thinking something like this (I'm not a SQL expert, so I don't know if this is even possible): SELECT course_name, capacity, count(query here) as attendee_count FROM events AS e LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id WHERE start_time BETWEEN point_a AND point_b Or should I just pull everything as a separate row like this and sort it all out programmatically: SELECT e.course_name, e.capacity, a.user_id FROM events AS e LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id WHERE start_time BETWEEN point_a AND point_b Or should I just try caching the data in PHP? Would an index help? Index your tables, make the database do the work. Much easier and less prone to bugs :) Check you have an index on: events(event_id) event_attendees(event_id) table(start_time) (whichever that table applies to - I assume it's events). Maybe try a multi-column index if this query gets run a lot: create index event_eventid_start_time on events(event_id, start_time); Use 'explain' to see which one is being used and possibly get rid of the other one. I have a guide about how to index databases here: http://www.designmagick.com/article/16/ (Yes it's a postgresql site but the same rules apply to mysql and other databases as well). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL Performance Help
1. yes indexes could help, if mysql uses them. The mysql optimiser may or may not use the index for the query depending on the statement...it sounds like you are doing a full table scan on the data 2. there are two schools of thought here: a. run the whole thing as two statements (one outer loop to loop thru the course list, and an inner one to get the attendees) b. examine the join query to see if you are starting out in the correct way (mysql can be sensitive to the way the tables are joined), examine the indexes and use the EXPLAIN statement to see how the optimiser attempts the query. I would just test both ways and see which one performs better... Bastien From: Tony Grimes [EMAIL PROTECTED] To: PHP-DB php-db@lists.php.net Subject: [PHP-DB] SQL Performance Help Date: Wed, 27 Dec 2006 14:05:13 -0700 I'm developing a course calendar for a client and I'm running into performance problems with the admin site. For example, when I try to include registration counts in the course list, the page really slows down for large course lists (50 or so): COURSEATTENDEES CAPACITYSEATS LEFT === == Course 1 5 10 5 Course 2 6 15 9 Course 3 4 10 6 I've been using one query to retrieve the course list and then one for each attendee count. Is there a more efficient way of doing this all in one query? I was thinking something like this (I'm not a SQL expert, so I don't know if this is even possible): SELECT course_name, capacity, count(query here) as attendee_count FROM events AS e LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id WHERE start_time BETWEEN point_a AND point_b Or should I just pull everything as a separate row like this and sort it all out programmatically: SELECT e.course_name, e.capacity, a.user_id FROM events AS e LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id WHERE start_time BETWEEN point_a AND point_b Or should I just try caching the data in PHP? Would an index help? I realize any answers might be complicated, but if you could just point me in the right direction, I can probably figure the rest out. Thanks, Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Enter the Telus Mobility Xbox a Day contest for your chance to WIN! Telus Mobility is giving away an Microsoft Xbox® 360 every day from November 20 to December 31, 2006! Just download Windows Live (MSN) Messenger to your IM-capable TELUS mobile phone, and you could be a winner! http://www.telusmobility.com/msnxbox/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL query error
Chris Carter wrote: What wrong with this syntax, its not giving any error on runtime but I am facing a blank page while paging. $query= SELECT * FROM gurgaonmalls WHERE mallname = '$mallname' limit $eu, $limit ; Have you tried... echo p $query /p; ...to unsure the variables have the values you expect them to have? Jeffrey -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL query
Make sure that your second query is returning only one row, if it dont help, try this: $query=select email from usuarios where userName in (select username from fussv where folio = 'FUSS-130-2006') MySQL think that you second query returns more than 1 row, that's why mysql dont accept your query, is like trying to compare vs more than one scalar value Regards! ++ | Ing Edwin Cruz [EMAIL PROTECTED] | ++ | Transportes Medel Rogero SA de CV | | | | Desk: +52 (449) 910 30 90 x3054 | ++ | MX Mobile: +52 (449) 111 29 03 | | Aguascalientes, Mexico | | http://www.medel.com.mx | ++ -Mensaje original- De: Miguel Guirao [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m. Para: php-db@lists.php.net Asunto: [PHP-DB] SQL query Hello list, Whats wrong with my SQL query: $query=select email from usuarios where userName = (select username from fussv where folio = 'FUSS-130-2006'); I get an error! I have tested the two individual sentences and they worked OK! --- Miguel Guirao Aguilera Logistica R8 TELCEL Tel. (999) 960.7994 Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL query
Check your version. Subselects were only added in MySQL Version 4.1. Regards, Dwight -Original Message- From: Edwin Cruz [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 10:53 AM To: 'Miguel Guirao'; php-db@lists.php.net Subject: RE: [PHP-DB] SQL query Make sure that your second query is returning only one row, if it dont help, try this: $query=select email from usuarios where userName in (select username from fussv where folio = 'FUSS-130-2006') MySQL think that you second query returns more than 1 row, that's why mysql dont accept your query, is like trying to compare vs more than one scalar value Regards! ++ | Ing Edwin Cruz [EMAIL PROTECTED] | ++ | Transportes Medel Rogero SA de CV | | | | Desk: +52 (449) 910 30 90 x3054 | ++ | MX Mobile: +52 (449) 111 29 03 | | Aguascalientes, Mexico | | http://www.medel.com.mx | ++ -Mensaje original- De: Miguel Guirao [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m. Para: php-db@lists.php.net Asunto: [PHP-DB] SQL query Hello list, Whats wrong with my SQL query: $query=select email from usuarios where userName = (select username from fussv where folio = 'FUSS-130-2006'); I get an error! I have tested the two individual sentences and they worked OK! --- Miguel Guirao Aguilera Logistica R8 TELCEL Tel. (999) 960.7994 Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL query
OK, this makes my day clear!! I have versión 3.23.49-3 of MySQL Thanks Dwight! -Original Message- From: Dwight Altman [mailto:[EMAIL PROTECTED] Sent: Jueves, 28 de Septiembre de 2006 11:32 a.m. To: php-db@lists.php.net Subject: RE: [PHP-DB] SQL query Check your version. Subselects were only added in MySQL Version 4.1. Regards, Dwight -Original Message- From: Edwin Cruz [mailto:[EMAIL PROTECTED] Sent: Thursday, September 28, 2006 10:53 AM To: 'Miguel Guirao'; php-db@lists.php.net Subject: RE: [PHP-DB] SQL query Make sure that your second query is returning only one row, if it dont help, try this: $query=select email from usuarios where userName in (select username from fussv where folio = 'FUSS-130-2006') MySQL think that you second query returns more than 1 row, that's why mysql dont accept your query, is like trying to compare vs more than one scalar value Regards! ++ | Ing Edwin Cruz [EMAIL PROTECTED] | ++ | Transportes Medel Rogero SA de CV | | | | Desk: +52 (449) 910 30 90 x3054 | ++ | MX Mobile: +52 (449) 111 29 03 | | Aguascalientes, Mexico | | http://www.medel.com.mx | ++ -Mensaje original- De: Miguel Guirao [mailto:[EMAIL PROTECTED] Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m. Para: php-db@lists.php.net Asunto: [PHP-DB] SQL query Hello list, Whats wrong with my SQL query: $query=select email from usuarios where userName = (select username from fussv where folio = 'FUSS-130-2006'); I get an error! I have tested the two individual sentences and they worked OK! --- Miguel Guirao Aguilera Logistica R8 TELCEL Tel. (999) 960.7994 Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql output to a multidimensional array
-Original Message-
From: Chris [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 07, 2006 9:52 PM
To: K.A.Bouton
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] sql output to a multidimensional array
K.A.Bouton wrote:
I need the output of my sql to be a multidimensional array
as follows.
chart [ 'chart_data' ] =3D array ( array ( , 2001,
2002, 2003,
2004 ),
array ( AAA, 0,
10, 30,
63 ),
array ( BBB, 100,
20, 65,
55 ),
array ( CCC,56,
21, 0,
90 )
);
I have tried and am unable so far to get this out of my database.
Any suggestions?
SELECT count( publications.title) AS title_number,
publications.year,
publications.affiliation=20 FROM publications GROUP BY
affiliation, year
ORDER BY year;
There is some for loop I am not getting, and am also not
getting the
= zero counts with the count(*).
You won't be able to get an sql query to return in that
format (I know
what you're trying to do, I've used the same chart software).
You won't get zero counts for data that doesn't exist, you'll need to
generate your series before hand:
?php
for ($i = 2000; $i 2006; $i++) {
$data[$i] = 0;
}
?
then later on override that value.
Thanks - but it's a dynamic dataset and I didn't want to hard code dates in.
I managed to do it by first doing a query on the years, then doing a count
query based on the year from the query above, and if no year was avaiable
count was 0.
Seems to work.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql output to a multidimensional array
K.A.Bouton wrote:
I need the output of my sql to be a multidimensional array as follows.
chart [ 'chart_data' ] =3D array ( array ( , 2001, 2002, 2003,
2004 ),
array ( AAA, 0, 10, 30,
63 ),
array ( BBB, 100, 20, 65,
55 ),
array ( CCC,56, 21, 0,
90 )
);
I have tried and am unable so far to get this out of my database.
Any suggestions?
SELECT count( publications.title) AS title_number, publications.year,
publications.affiliation=20 FROM publications GROUP BY affiliation, year
ORDER BY year;
There is some for loop I am not getting, and am also not getting the =
zero counts with the count(*).
You won't be able to get an sql query to return in that format (I know
what you're trying to do, I've used the same chart software).
You won't get zero counts for data that doesn't exist, you'll need to
generate your series before hand:
?php
for ($i = 2000; $i 2006; $i++) {
$data[$i] = 0;
}
?
then later on override that value.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql output to a multidimensional array
K.A.Bouton wrote:
I need the output of my sql to be a multidimensional array as follows.
then Chris wrote:
You won't be able to get an sql query to return in that format (I know
what you're trying to do, I've used the same chart software).
This is my MSSQL Server solution, and yep, it outputs EXACTLY like
requested. I'm not sufficiently fluent with MySQL, so this may have
some dialect translation that needs done.
Also, this IS NOT a scalable solution at all. It assumes you know the
years you want the information for, and requires that you specify the
years in two specific areas of the query. Some SQL engines have a cross
tab function that will make this a little more automatic (MSSQL is one
such engine), but they are typically extensions to the SQL spec. and are
specific to each engine (i.e. the MSSQL implementation would not work
for MySQL).
-- Mitch
--- SQL BEGINS HERE -
-- Table of products
CREATE TABLE #Products (
Product CHAR(3) NOT NULL
)
INSERT #Products(Product) VALUES('AAA')
INSERT #Products(Product) VALUES('BBB')
INSERT #Products(Product) VALUES('CCC')
-- Sales history table.
CREATE TABLE #Sales (
Product CHAR(3) NOT NULL ,
Quantity INT NOT NULL ,
SaleYear INT NOT NULL
)
-- Product AAA, Intentionally left out 2003
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 3, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 8, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 7, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 6, 2004)
-- Product BBB, Intentionally left out 2002
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 3, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 5, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 1, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 8, 2004)
-- Product CCC, Intentionally left out 2001
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 3, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 7, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 1, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 5, 2004)
-- Now for the totally UNscalable query.
SELECTp.Product,
COALESCE(y1.TotalQuan, 0) AS Quan2001,
COALESCE(y2.TotalQuan, 0) AS Quan2002,
COALESCE(y3.TotalQuan, 0) AS Quan2003,
COALESCE(y4.TotalQuan, 0) AS Quan2004
FROM #Products AS p
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2001
GROUP BY Product
) AS y1 ON y1.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2002
GROUP BY Product
) AS y2 ON y2.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2003
GROUP BY Product
) AS y3 ON y3.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2004
GROUP BY Product
) AS y4 ON y4.Product = p.Product
-- Cleanup
DROP TABLE #Sales
DROP TABLE #Products
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql output to a multidimensional array
Mitch Miller wrote: K.A.Bouton wrote: I need the output of my sql to be a multidimensional array as follows. then Chris wrote: You won't be able to get an sql query to return in that format (I know what you're trying to do, I've used the same chart software). This is my MSSQL Server solution, and yep, it outputs EXACTLY like requested. I'm not sufficiently fluent with MySQL, so this may have some dialect translation that needs done. Also, this IS NOT a scalable solution at all. It assumes you know the years you want the information for, and requires that you specify the years in two specific areas of the query. Some SQL engines have a cross tab function that will make this a little more automatic (MSSQL is one such engine), but they are typically extensions to the SQL spec. and are specific to each engine (i.e. the MSSQL implementation would not work for MySQL). I stand corrected :) mysql should support something like that but yeh it's not a great solution (ie it's a horrible query) ;) -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server COM question
Try the MSDN library: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjconnectionpme.asp - Frank Chris wrote: Todd Cary wrote: [Sorry - I misposted this in the General group] I am in the process of converting my clients PHP scripts that are using Firebird so they will work with SQL Server (their request; not mine). Is there a reference where I can get the COM Methods and Properties? The php website maybe? http://php.net/com Also, without loading the large AdoDb library, is there a Prepare() method if I use the $db = new COM(ADODB.Connection) connection? If you want to use adodb methods, you need to load it all up properly, same as anything else. I checked the http://php.net/com site initially and there is a couple of examples, however there is not a list of all COM methods and properties for SQL Server. Does anyone know where such a list might exist? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server COM question
Todd Cary wrote: [Sorry - I misposted this in the General group] I am in the process of converting my clients PHP scripts that are using Firebird so they will work with SQL Server (their request; not mine). Is there a reference where I can get the COM Methods and Properties? The php website maybe? http://php.net/com Also, without loading the large AdoDb library, is there a Prepare() method if I use the $db = new COM(ADODB.Connection) connection? If you want to use adodb methods, you need to load it all up properly, same as anything else. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server COM question
Chris wrote: Todd Cary wrote: [Sorry - I misposted this in the General group] I am in the process of converting my clients PHP scripts that are using Firebird so they will work with SQL Server (their request; not mine). Is there a reference where I can get the COM Methods and Properties? The php website maybe? http://php.net/com Also, without loading the large AdoDb library, is there a Prepare() method if I use the $db = new COM(ADODB.Connection) connection? If you want to use adodb methods, you need to load it all up properly, same as anything else. I checked the http://php.net/com site initially and there is a couple of examples, however there is not a list of all COM methods and properties for SQL Server. Does anyone know where such a list might exist? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server COM question
Todd Cary wrote: Chris wrote: Todd Cary wrote: [Sorry - I misposted this in the General group] I am in the process of converting my clients PHP scripts that are using Firebird so they will work with SQL Server (their request; not mine). Is there a reference where I can get the COM Methods and Properties? The php website maybe? http://php.net/com Also, without loading the large AdoDb library, is there a Prepare() method if I use the $db = new COM(ADODB.Connection) connection? If you want to use adodb methods, you need to load it all up properly, same as anything else. I checked the http://php.net/com site initially and there is a couple of examples, however there is not a list of all COM methods and properties for SQL Server. Does anyone know where such a list might exist? You could always get the freetds stuff working: http://php.net/mssql -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL request on DBase file
Hi, Whats wonrg with the manual? http://ca3.php.net/manual/en/ref.oracle.php http://www.zend.com/products/zend_core/zend_core_for_oracle I looked at these links and I found nothing about .pdf files !!! I have no problem with Oracle, but only for requesting, in SQL, .dbf files... David. Bastien Hi, I have to rewrite an application from ASP. In this application, I receive DBase files which may be saved into Oracle. In ASP, I used a generic driver and I made SQL requests on these files. It is possible with PHP on Linux ? Do you have some examples ? Thank you very much. David. signature.asc Description: Ceci est une partie de message numériquement signée
RE: [PHP-DB] SQL request on DBase file
Guess I misunderstodd the post, thought you were asking about how to access oracle after importing the dbase files http://ca.php.net/manual/en/ref.dbase.php Not sure about your question on PDFs..can you clarify? Bastien From: David BERCOT [EMAIL PROTECTED] To: php-db@lists.php.net Subject: RE: [PHP-DB] SQL request on DBase file Date: Sat, 11 Feb 2006 14:31:50 +0100 Hi, Whats wonrg with the manual? http://ca3.php.net/manual/en/ref.oracle.php http://www.zend.com/products/zend_core/zend_core_for_oracle I looked at these links and I found nothing about .pdf files !!! I have no problem with Oracle, but only for requesting, in SQL, .dbf files... David. Bastien Hi, I have to rewrite an application from ASP. In this application, I receive DBase files which may be saved into Oracle. In ASP, I used a generic driver and I made SQL requests on these files. It is possible with PHP on Linux ? Do you have some examples ? Thank you very much. David. signature.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL request on DBase file
Guess I misunderstodd the post, thought you were asking about how to access oracle after importing the dbase files http://ca.php.net/manual/en/ref.dbase.php Not sure about your question on PDFs..can you clarify? Of course. No problemo ;-) I want to make SQL request on PDFs files like (syntax is fantaisist) : Conn = OpenDatabase(PDF Driver,/var/www/repDBF); SQLStmt = Select * From DBFFile1 where Name = 'DUBOIS'; RS = Conn.Execute(SQLStmt); These is what I do in ASP on Windows... David. Bastien From: David BERCOT [EMAIL PROTECTED] To: php-db@lists.php.net Subject: RE: [PHP-DB] SQL request on DBase file Date: Sat, 11 Feb 2006 14:31:50 +0100 Hi, Whats wonrg with the manual? http://ca3.php.net/manual/en/ref.oracle.php http://www.zend.com/products/zend_core/zend_core_for_oracle I looked at these links and I found nothing about .pdf files !!! I have no problem with Oracle, but only for requesting, in SQL, .dbf files... David. Bastien Hi, I have to rewrite an application from ASP. In this application, I receive DBase files which may be saved into Oracle. In ASP, I used a generic driver and I made SQL requests on these files. It is possible with PHP on Linux ? Do you have some examples ? Thank you very much. David. signature.asc signature.asc Description: Ceci est une partie de message numériquement signée
RE: [PHP-DB] SQL request on DBase file
Whats wonrg with the manual? http://ca3.php.net/manual/en/ref.oracle.php http://www.zend.com/products/zend_core/zend_core_for_oracle Bastien From: David BERCOT [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] SQL request on DBase file Date: Sat, 11 Feb 2006 00:28:16 +0100 Hi, I have to rewrite an application from ASP. In this application, I receive DBase files which may be saved into Oracle. In ASP, I used a generic driver and I made SQL requests on these files. It is possible with PHP on Linux ? Do you have some examples ? Thank you very much. David. signature.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server - do I need PHP to run COMMIT?
Using COMMIT is only required if you issued a BEGIN TRANS before your
INSERTs, UPDATEs and / or DELETEs. All transactions are automatically
committed if you don't use BEGIN TRANS. Are you backing up the
transaction logs regularly?
-- bob
On Fri, 16 Dec 2005, Alex Gemmell wrote:
Hello people,
My PHP application uses a SQL Server 2000 database. I have previously
only ever used MySQL and so my knowledge of SQL Server comes just from
experimentation and trial and error experience.
My PHP application appears to be working fine but I have just discovered
that although the database itself is rather small on the disk (about
25MB) the transaction log file is huge (400MB). I have had a quick look
at Microsoft's website about large transaction files and they suggest
many reasons, one of which is the application not COMMITing
transactions. This is certainly true because I simply make INSERT and
UPDATE queries but don't include a COMMIT statement.
So my question is this: should I be COMMITing?
How do I do that? Do I simply run something like this after every
INSERT/UPDATE/DELETE:
mssql_query('COMMIT', $link_identifier);
Please help - I feel like I'm missing a trick here.
FYI: I'm also now doubting my use of mssql_pconnect() - should I
being using mssql_connect() with mssql_close() instead?
Thanks,
Alex
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server - do I need PHP to run COMMIT?
Yes, the transaction logs are being backed up and shrunk regularly. I
am told while they are 400MB most of that is empty space and it's
really about 50MB in size. Apparently that is still a bit too big and
indicates a possible problem.
I'm glad all transactions are auto-commited. I presumed that was the
case because we have 5 users all using this system at the same time and
we have had no apparent problems with data being saved but not
actually being stored in the database. Everything actually seems to be
working fine apart from the remarkably large transaction file.
Any ideas why the transaction log file would be so big?
Robert Twitty wrote:
Using COMMIT is only required if you issued a BEGIN TRANS before your
INSERTs, UPDATEs and / or DELETEs. All transactions are automatically
committed if you don't use BEGIN TRANS. Are you backing up the
transaction logs regularly?
-- bob
On Fri, 16 Dec 2005, Alex Gemmell wrote:
Hello people,
My PHP application uses a SQL Server 2000 database. I have previously
only ever used MySQL and so my knowledge of SQL Server comes just from
experimentation and trial and error experience.
My PHP application appears to be working fine but I have just discovered
that although the database itself is rather small on the disk (about
25MB) the transaction log file is huge (400MB). I have had a quick look
at Microsoft's website about large transaction files and they suggest
many reasons, one of which is the application not COMMITing
transactions. This is certainly true because I simply make INSERT and
UPDATE queries but don't include a COMMIT statement.
So my question is this: should I be COMMITing?
How do I do that? Do I simply run something like this after every
INSERT/UPDATE/DELETE:
mssql_query('COMMIT', $link_identifier);
Please help - I feel like I'm missing a trick here.
FYI: I'm also now doubting my use of mssql_pconnect() - should I
being using mssql_connect() with mssql_close() instead?
Thanks,
Alex
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server - do I need PHP to run COMMIT?
Do you have the truncate log on checkpoint option enabled? Also, if you
want to reduce the size of the log file if shrinking doesn't work, try
running the following against the database. Of course, you should backup
the DB first.
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [CLEANUP] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @LogicalFileName = 'FHMMSYS_Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize= 100 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes DATEDIFF (mi, @StartTime, GETDATE()) -- time
has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) @NewSize -- The value passed in
for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter @OriginalSize / 16) AND (@Counter 5))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char
field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection attack
I'm pretty amateur at this too, but have done a little reading on the subject.
Here's some nuggets to ponder while the real experts write their responses: :)
1. Magic quotes + mysql_escape_string = double escaped stuff. I think the
general opinion is the magic quotes is evil, but I'm sure some people like it.
I prefer to use mysql_escape_string() since it escapes things more specific to
MySQL than magic quotes does. Using mysql_escape_string should be good enough
by itself.
2. Check data type. If an item is supposed to be an integer, use intval()
before inserting into the database.
3. What your SQL statements for variables that can turn your statement into a
WHERE 1 = 1 situation that will always return TRUE.
Here's something I've been playing with.. a generic function to sanitize data
before inserting into the database. You pass it the data and the type of data
and it'll clean it up. Nice thing about this is I designed it so if you say
type = phone and you process it the same as type = numeric.. then later you
decide you want to process phone and numeric types separately, you only
have to check this function, not all your lines of code.
If someone has better ways of doing this, I'm all for hearing it. Please opine
or criticize what I've posted above too. I want to learn as well.
-TG
Code:
?php
/**
*~DBSanitizeData() prepares data for inserting/updating into or selecting from
* MySQL by making sure that string data is properly escaped so as not to allow
* 'SQL injection' type security issues from happening. No direct $_POST or
$_GET
* data should ever be used in a SQL string.
*
* Returns sanitized copy of data sent to it.
*
* Current sanitization only performs a mysql_escape_string() function but could
do
* more later.
*
* Example: $result = mysql_query('INSERT INTO TableName (SomeColumn) VALUES ('
. DBSanitizeData($_POST['somevar']) . ')');
*
* pre
* Modification Log:
* --
* Created: ~~Trevor Gryffyn - 03/28/2005
*
* /pre
*
* @author Trevor Gryffyn [EMAIL PROTECTED]
* @category Database Functions
*
*/
function DBSanitizeData($dbdata, $datatype = alpha) {
switch ($datatype) {
case binary:
case truefalse:
$trues = array(YES, Y, 1, ON, TRUE, T);
$falses = array(NO, N, 0, OFF, FALSE, F);
if (in_array(trim(strtoupper($dbdata)), $trues)) {
$dbdata = Y;
} else {
$dbdata = N;
}
break;
case phone:
case numeric:
case ssn:
$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata);
break;
case float:
case money:
case percent:
// TODO: Should this be handled with floatval() or something else?
// Yes.. it probably should. Maybe this is better.
if (strstr($dbdata, .) AND trim($dbdata) ) {
#$dbdata = (preg_replace ('/[^\d]+/s', '', $dbdata) / 100) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata) / 100);
} else {
#$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata));
}
break;
case name:
case address:
$dbdata = ucwords($dbdata);
break;
case state:
$dbdata = strtoupper($dbdata);
break;
case date:
$dbdata = date(Y-m-d, strtotime($dbdata));
if ($dbdata == 1969-12-31) $dbdata = ;
break;
case alpha:
default:
// Nothing special, just jump down to the trim/escape
break;
}
return trim(mysql_escape_string($dbdata));
}
?
= = = Original message = = =
Greetings all:
Using PHP 4.3.xx and MySQL 4.1 (and 3.xxx sometimes).
I've got a ton of forms that use the $_POST variable to send information into
the database, and I'm worried about injection attacks.
My server has magic_quotes enabled, which I thought would handle most things,
but am wondering now if I need to use mysql_escape_string on everything, which
would mean, of course, a lot of find-and-replace and rewriting.
Also, REGISTER_GLOBALS is turned off, and errors are not shown to the user when
the site is live.
Any suggestions on how to tighten up the form security, or does magic_quotes
help enough?
For what it's worth, I've tried to enter things like pw='' and other
simulated attackes using the $_GET method, but haven't been able to crack the
site. But I'm a noob at that kind of thing, so I try not to get too carried
away with myself.
Thanks,
V
___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection attack
NOTE: http://www.php.net/mysql_escape_string Version: 4.3.0 Description: This function became deprecated, do not use this function. Instead, use mysql_real_escape_string(). Jordan On Aug 25, 2005, at 2:15 PM, [EMAIL PROTECTED] tg- [EMAIL PROTECTED] wrote: Using mysql_escape_string should be good enough by itself. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Injection attack
Haha.. what the hell? Ok, I know this is an older copy of the script I wrote
because I know I took out the All this does is escape the data comment and I
KNOW I saw the thing about mysql_escape_string() being deprecated... don't
know why it's still in there. Hah
Thanks for pointing that out. Now off to find my newer version and make sure I
chaned it there too.
-TG
= = = Original message = = =
no !!!
mysql_real_escape_string()
anyhow.. good luck with your security endeavors!
On 8/25/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I'm pretty amateur at this too, but have done a little reading on the
subject. Here's some nuggets to ponder while the real experts write their
responses: :)
1. Magic quotes + mysql_escape_string = double escaped stuff. I think the
general opinion is the magic quotes is evil, but I'm sure some people like
it. I prefer to use mysql_escape_string() since it escapes things more
specific to MySQL than magic quotes does. Using mysql_escape_string should
be good enough by itself.
2. Check data type. If an item is supposed to be an integer, use intval()
before inserting into the database.
3. What your SQL statements for variables that can turn your statement into a
WHERE 1 = 1 situation that will always return TRUE.
Here's something I've been playing with.. a generic function to sanitize data
before inserting into the database. You pass it the data and the type of
data and it'll clean it up. Nice thing about this is I designed it so if you
say type = phone and you process it the same as type = numeric.. then
later you decide you want to process phone and numeric types separately,
you only have to check this function, not all your lines of code.
If someone has better ways of doing this, I'm all for hearing it. Please
opine or criticize what I've posted above too. I want to learn as well.
-TG
Code:
?php
/**
*~DBSanitizeData() prepares data for inserting/updating into or selecting from
* MySQL by making sure that string data is properly escaped so as not to allow
* 'SQL injection' type security issues from happening. No direct $_POST or
$_GET
* data should ever be used in a SQL string.
*
* Returns sanitized copy of data sent to it.
*
* Current sanitization only performs a mysql_escape_string() function but
could do
* more later.
*
* Example: $result = mysql_query('INSERT INTO TableName (SomeColumn) VALUES
(' . DBSanitizeData($_POST['somevar']) . ')');
*
* pre
* Modification Log:
* --
* Created: ~~Trevor Gryffyn - 03/28/2005
*
* /pre
*
* @author Trevor Gryffyn [EMAIL PROTECTED]
* @category Database Functions
*
*/
function DBSanitizeData($dbdata, $datatype = alpha)
switch ($datatype)
case binary:
case truefalse:
$trues = array(YES, Y, 1, ON, TRUE, T);
$falses = array(NO, N, 0, OFF, FALSE, F);
if (in_array(trim(strtoupper($dbdata)), $trues))
$dbdata = Y;
else
$dbdata = N;
break;
case phone:
case numeric:
case ssn:
$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata);
break;
case float:
case money:
case percent:
// TODO: Should this be handled with floatval() or something else?
// Yes.. it probably should. Maybe this is better.
if (strstr($dbdata, .) AND trim($dbdata) )
#$dbdata = (preg_replace ('/[^\d]+/s', '', $dbdata) / 100) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata) / 100);
else
#$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata) . .00;
$dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata));
break;
case name:
case address:
$dbdata = ucwords($dbdata);
break;
case state:
$dbdata = strtoupper($dbdata);
break;
case date:
$dbdata = date(Y-m-d, strtotime($dbdata));
if ($dbdata == 1969-12-31) $dbdata = ;
break;
case alpha:
default:
// Nothing special, just jump down to the trim/escape
break;
return trim(mysql_escape_string($dbdata));
?
= = = Original message = = =
Greetings all:
Using PHP 4.3.xx and MySQL 4.1 (and 3.xxx sometimes).
I've got a ton of forms that use the $_POST variable to send information into
the database, and I'm worried about injection attacks.
My server has magic_quotes enabled, which I thought would handle most things,
but am wondering now if I need to use mysql_escape_string on everything,
which would mean, of course, a lot of find-and-replace and rewriting.
Also, REGISTER_GLOBALS is turned off, and errors are not shown to the user
when the site is live.
Any suggestions on how to tighten up the form security, or does magic_quotes
help enough?
For what it's worth, I've tried to enter things like pw='' and
Re: [PHP-DB] SQL Injection attack
Estimado veditio,
you wrote:
I've got a ton of forms that use the $_POST variable to send
information into the database [...]
Any suggestions on how to tighten up the form security, or does
magic_quotes help enough?
I'm not a security expert but after some attacks I have implemented
this simple thing. Until today it works for me.
You can put it before be connected to your database. I have one
only script to connect my database placed outside the /public_html.
It is and requested by means one include() in every oho script.
In this way, this security works in the whole site.
?
$req = $_SERVER['REQUEST_URI'];
$cadena = explode(?, $req);
$mi_url = $cadena[0];
$resto = $cadena[1];
// here you can put your suspicions chains at will. Just be careful with
// the names of your variables passing by you URLs
$inyecc='/script|http|||%3c|%3e|SELECT|UNION|UPDATE|AND|exe|exec|INSERT|tmp/i';
...etc
// detecting
if (preg_match($inyecc, $resto)) {
// make something, in example sending an e-mail alert
$ip = $HTTP_SERVER_VARS[HTTP_CLIENT_IP];
$forwarded = $HTTP_SERVER_VARS[HTTP_X_FORWARDED_FOR];
$remoteaddress = $HTTP_SERVER_VARS[REMOTE_ADDR];
$message = attack injection in $mi_url \n\nchain: $resto \n\n
from: (ip-forw-RA):- $ip - $forwarded - $remoteaddress\n\n
- end ;
mail([EMAIL PROTECTED], Attack injection, $message,
From: [EMAIL PROTECTED]'SERVER_NAME']}, [EMAIL
PROTECTED]'SERVER_NAME']});
// kill execution
echo 'illegal url';
die();
}
// DB connection
$connection=mysql_connect(...etc.
?
if you can encode this script with Zend Encoder or a similar thing.
It will be an additional measure to avoid the reading of this file.
hope it can be useful,
Vicente,
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL or array ?
explain the phrase big array. What is big to you might not be big to the server nor to me but then again maybe it's bigger. Are you talking dozens, hundreds, thousands, millions?? - Original Message - From: Paul Reilly [EMAIL PROTECTED] To: php-db@lists.php.net Sent: Saturday, April 23, 2005 5:05 PM Subject: [PHP-DB] SQL or array ? I have a quick question about which would the best way to implement something in terms of performance. Using a database, or just creating a big array in memory? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL or array ?
explain the phrase big array. I guess everything is relative! We're talking about 300-500 items here. Paul -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL or array ?
Only? That aint too big, but now i'm confused what's ur hardware and O.Sspecs? 300 to 500 would be a peice of cake to load. However, it depends on ur system. I would rather use the array than hitting the db for the query. Because the bottom line is that you will receive your data in an array no matter what even if its in the db so doesnt realy matter unless you wanna paginate it. In that case I'd use the db rather to array alone. Wow Hold on. That's a catch 22. If ur loading ur array in the beginning of the execution then just do it on the DB once n just load it the next time. That means the next time you dont have to take a trip to ur file system n then another trip to display the array. And plus if u needed to paginate which of corse ull need to for such a number db helps, however paginating arrays is easy as well. HTH Benchmark it! And find out for yourself. On 4/24/05, Paul Reilly [EMAIL PROTECTED] wrote: explain the phrase big array. I guess everything is relative! We're talking about 300-500 items here. Paul -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- M.Saleh.E.G 97150-4779817
Re: [PHP-DB] SQL prob
Thanks, but DISTINCT doesn't work... But I managed to get it to work anyway by including namn_1 NOT LIKE 'H%' in the second WHERE-clause... Micah Stevens wrote: use DISTINCT? On Sunday 30 January 2005 12:51 pm, Bobo Wieland wrote: Anyone that can help me with this one? I want this SQL-statement to retrive only distinct values from the original table column named (not the AS stuff) ( SELECT * , namn_2 AS sec_namn, namn_1 AS one FROM sortiment WHERE namn_1 LIKE 'H%' ) UNION ( SELECT * , UPPER( namn_2 ) AS sec_namn, namn_2 AS one FROM sortiment WHERE namn_2 LIKE 'H%' AND SUBSTRING( namn_2, 1, 1 ) LIKE BINARY 'H' ) ORDER BY one LIMIT 0 , 10 this will return, for example, the following row twice where (in the original table) namn_1 = Humulus lupulus namn_2 = Humle the two rows are identical except for the sec_namn and one created by the query. They are set to: sec_namn one HUMLEHumle HumleHumulus lupulus _bobo wieland _ [EMAIL PROTECTED] _ winamp Not playing anything right now... -- _bobo wieland _ [EMAIL PROTECTED] _ winamp Not playing anything right now... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL prob
use DISTINCT? On Sunday 30 January 2005 12:51 pm, Bobo Wieland wrote: Anyone that can help me with this one? I want this SQL-statement to retrive only distinct values from the original table column named (not the AS stuff) ( SELECT * , namn_2 AS sec_namn, namn_1 AS one FROM sortiment WHERE namn_1 LIKE 'H%' ) UNION ( SELECT * , UPPER( namn_2 ) AS sec_namn, namn_2 AS one FROM sortiment WHERE namn_2 LIKE 'H%' AND SUBSTRING( namn_2, 1, 1 ) LIKE BINARY 'H' ) ORDER BY one LIMIT 0 , 10 this will return, for example, the following row twice where (in the original table) namn_1 = Humulus lupulus namn_2 = Humle the two rows are identical except for the sec_namn and one created by the query. They are set to: sec_namn one HUMLEHumle HumleHumulus lupulus _bobo wieland _ [EMAIL PROTECTED] _ winamp Not playing anything right now... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement
PHPDiscuss - PHP Newsgroups and mailing lists wrote: Hello everybody, I'm building a small application and I have trouble passing a POST variable form one page to another inside the SQL statement. The query displayed below works great without the .$_POST['CompanyName']. $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName=.$_POST['CompanyName']. ORDER BY CompanyName ASC; you need to quote the string (company name) in the actual sql, compare the following 2 statements (lets assume companyname is 'IBM'): WRONG (this is what you are doing now): SELECT CompanyID, CompanyName,CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName=IBM ORDER BY CompanyName ASC RIGHT: SELECT CompanyID, CompanyName,CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName='IBM' ORDER BY CompanyName ASC there may be times when the companyname contains a single quote - that will break your query unless you escape the single quote in the name before placing the string into the query string... mysql.com can tell you more. But it messes up if I include it because the first is considered as the end of the previous one and so on, so the code gets messed up. I'll really appreciate any/all help! Have you all an excellent year! Jorge -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL statement syntaxis
missing the singles quotes around the company name text element $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName='.$_POST['CompanyName'].' ORDER BY CompanyName ASC; bastien From: [EMAIL PROTECTED] (PHPDiscuss - PHP Newsgroups and mailing lists) To: php-db@lists.php.net Subject: [PHP-DB] SQL statement syntaxis Date: 6 Jan 2005 19:12:16 - Hello everybody, I'm building a small application and I have trouble passing a POST variable form one page to another inside the SQL statement. The query (displayed below) works great without the .$_POST['CompanyName']. $query_company_listing = SELECT CompanyID, CompanyName, CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM company WHERE company.CompanyName=.$_POST['CompanyName']. ORDER BY CompanyName ASC; But it messes up if I include it because the first is considered as the end of the previous one and so on. So the code gets messed up. Any help will be greatly appreciated! Have everybody a wonderful 2005! Jorge -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement
Jason, can you please turn off the return receipts on emails you send to the list. it's bloody annoying to have 'The Sender wishes to be notified' popup messages everytime I read one of your emails (and, alas, I don't have the skill to hack the return receipt crap right out of Tbird). BTW your not the only one that has it turned on - so this goes to the rest of you as well :-) cheers! Jason Walker wrote: First off - $_POST['CompanyName'] is valid, right? ... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL statement
Hi, To stop the return receipt dialog appearing in Tbird... Tools... Options... Advanced... Return Receipt and select Never send a return receipt. Or you can choose some of the other selections. graeme Jochem Maas wrote: Jason, can you please turn off the return receipts on emails you send to the list. it's bloody annoying to have 'The Sender wishes to be notified' popup messages everytime I read one of your emails (and, alas, I don't have the skill to hack the return receipt crap right out of Tbird). BTW your not the only one that has it turned on - so this goes to the rest of you as well :-) cheers! Jason Walker wrote: First off - $_POST['CompanyName'] is valid, right? ... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL statement
First off - $_POST['CompanyName'] is valid, right?
Can you do something like this?:
if (isset($_POST['CompanyName'])){
$sqlCompanyName = $_POST['CompanyName'];
} else {
return them back to the form, or something?
}
$query_company_listing = SELECT CompanyID, CompanyName,
CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM
company WHERE company.CompanyName='$sqlCompanyName' ORDER BY
CompanyName ASC;
Also, what datatype is CompanyName? If it is varchar - or really anything
else - I have had better look single quote encapsulation on the VALUE
portion of the query (company.CompanyName='VALUE' vs.
company.CompanyName=VALUE)
Not knowing the datatypes may make this an irrelevant point though.
-Original Message-
From: PHPDiscuss - PHP Newsgroups and mailing lists
[mailto:[EMAIL PROTECTED]
Sent: Thursday, January 06, 2005 12:09 PM
To: php-db@lists.php.net
Subject: [PHP-DB] SQL statement
Hello everybody,
I'm building a small application and I have trouble passing a POST
variable form one page to another inside the SQL statement.
The query displayed below works great without the
.$_POST['CompanyName'].
$query_company_listing = SELECT CompanyID, CompanyName,
CompanyOrDepartment, BillingAddress, City, PostalCode, PhoneNumber FROM
company WHERE company.CompanyName=.$_POST['CompanyName']. ORDER BY
CompanyName ASC;
But it messes up if I include it because the first is considered as the
end of the previous one and so on, so the code gets messed up.
I'll really appreciate any/all help!
Have you all an excellent year!
Jorge
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.8 - Release Date: 1/3/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.298 / Virus Database: 265.6.8 - Release Date: 1/3/2005
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Insert problem (SOLVED), Thanks!
Thank you all for the help. I can't believe I forgot something so simple
yet crucial.
Thanks again for all the help.
Vinny
-Original Message-
From: John W. Holmes [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 11:04 AM
To: Vincent Jordan; [EMAIL PROTECTED]
Subject: [SPAM] Re: [PHP-DB] SQL Insert problem
From: Vincent Jordan [EMAIL PROTECTED]
$sql = INSERT INTO rmarequest (firstname, lastname, address,
address2,
city, state, zip, phone, email, serial, product, reason,
rmanumber)VALUES
('$firstname', '$lastname', '$address', '$city', '$state',
'$zip', '$phone',
'$email', '$serial', '$product', '$reason', '$rmanumber') or die
(mysql_error());
Uhhh. where's mysql_query()???
$sql = INSERT ...;
$result = mysql_query($sql) or die(mysql_error());
---John Holmes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL Insert problem
You're missing address2 in your list of values. This means that you have an
unmatching number of column names and values in your query and that'll make
the query bomb.
Rich
-Original Message-
From: Vincent Jordan [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 10:25 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] SQL Insert problem
Im having a problem inserting data. Ive looked over this
again and again and
can not find what ive missed. Everything else works besides
the db insert.
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
?php
ini_set ('display_errors', 1);
error_reporting (E_ALL ~E_NOTICE);
// Define
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$serial = $_POST['serial'];
$product = $_POST['product'];
$reason = $_POST['reason'];
$gold = $_POST['gold_button_y'];
$goldaccount = $_POST['goldaccount'];
$rmanumber = $lastname{0}.date(ndyGi);
$connect = mysql_connect(SERVER , USER, PASSWORD) or die
(mysql_error());
$select = mysql_select_db (spdata) or die (mysql_error());
$sql = INSERT INTO rmarequest (firstname, lastname, address,
address2,
city, state, zip, phone, email, serial, product, reason,
rmanumber)VALUES
('$firstname', '$lastname', '$address', '$city', '$state',
'$zip', '$phone',
'$email', '$serial', '$product', '$reason', '$rmanumber') or die
(mysql_error());
if (isset($_POST['submit'])) {
$sql;
}
// Send and put in email message
$htmlheader = Content-Type: text/html; charset=us-ascii\n;
$htmlheader .= Content-Transfer-Encoding: 7bit;
$header = $from; // set the from field in the header
$header .= \n; // add a line feed
$header .= MIME-version: 1.0\n; //add the mime-version header
$header .= $htmlheader.\n;
$from = From: RMA Request [EMAIL PROTECTED];
$message = $firstname $lastname
$address
$address2
$city
$state
$zip
$phone
$email
$product
$serial
$gold
$goldaccount
$reason
$rmanumber;
// Send email to support
mail([EMAIL PROTECTED], RMA Request, $message, $header);
?
html xmlns=http://www.w3.org/1999/xhtml;
head
titleUntitled Document/title
/head
body
pstrongRMA Request Sent/strong/p
pYour RMA Number is strong ? echo $rmanumber ? /strong /p
pPlease include a note within your package with your
shipping address,
phone number and discription of the problem.br /When
shipping Smart Parts
reccomends insuring your package for the full replacment cost.
We also advise purchasing tracking services if using the postal
service./p
pstrongShip your return to: /strong/p
pSmart Parts, Incbr /
ATTN ? echo $rmanumber ?br /
Loyanhanna Business Complexbr /
100 Station St.br /
Loyalhanna Pa. 15661/p
pbr /
Please allow up to one week for package delivery.
For status information please call 800-992-2147 and ask
for the returns
department./p
a href=# onClick=window.print();Click Here to print
this page/abr
/
a href=form.htmClick here to return to RMA Request form/a
/body
/html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL Insert problem
I have inserted '$address2', correctly in the row however it is still not
putting the data in the table.
I am not getting an error but I do believe I have correct syntax set to
display problems.
-Original Message-
From: Hutchins, Richard [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 10:26 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] SQL Insert problem
You're missing address2 in your list of values. This means that you have
an
unmatching number of column names and values in your query and that'll
make
the query bomb.
Rich
-Original Message-
From: Vincent Jordan [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 10:25 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] SQL Insert problem
Im having a problem inserting data. Ive looked over this
again and again and
can not find what ive missed. Everything else works besides
the db insert.
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
?php
ini_set ('display_errors', 1);
error_reporting (E_ALL ~E_NOTICE);
// Define
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$serial = $_POST['serial'];
$product = $_POST['product'];
$reason = $_POST['reason'];
$gold = $_POST['gold_button_y'];
$goldaccount = $_POST['goldaccount'];
$rmanumber = $lastname{0}.date(ndyGi);
$connect = mysql_connect(SERVER , USER, PASSWORD) or die
(mysql_error());
$select = mysql_select_db (spdata) or die (mysql_error());
$sql = INSERT INTO rmarequest (firstname, lastname, address,
address2,
city, state, zip, phone, email, serial, product, reason,
rmanumber)VALUES
('$firstname', '$lastname', '$address', '$city', '$state',
'$zip', '$phone',
'$email', '$serial', '$product', '$reason', '$rmanumber') or die
(mysql_error());
if (isset($_POST['submit'])) {
$sql;
}
// Send and put in email message
$htmlheader = Content-Type: text/html; charset=us-ascii\n;
$htmlheader .= Content-Transfer-Encoding: 7bit;
$header = $from; // set the from field in the header
$header .= \n; // add a line feed
$header .= MIME-version: 1.0\n; //add the mime-version header
$header .= $htmlheader.\n;
$from = From: RMA Request [EMAIL PROTECTED];
$message = $firstname $lastname
$address
$address2
$city
$state
$zip
$phone
$email
$product
$serial
$gold
$goldaccount
$reason
$rmanumber;
// Send email to support
mail([EMAIL PROTECTED], RMA Request, $message, $header);
?
html xmlns=http://www.w3.org/1999/xhtml;
head
titleUntitled Document/title
/head
body
pstrongRMA Request Sent/strong/p
pYour RMA Number is strong ? echo $rmanumber ? /strong /p
pPlease include a note within your package with your
shipping address,
phone number and discription of the problem.br /When
shipping Smart Parts
reccomends insuring your package for the full replacment cost.
We also advise purchasing tracking services if using the postal
service./p
pstrongShip your return to: /strong/p
pSmart Parts, Incbr /
ATTN ? echo $rmanumber ?br /
Loyanhanna Business Complexbr /
100 Station St.br /
Loyalhanna Pa. 15661/p
pbr /
Please allow up to one week for package delivery.
For status information please call 800-992-2147 and ask
for the returns
department./p
a href=# onClick=window.print();Click Here to print
this page/abr
/
a href=form.htmClick here to return to RMA Request form/a
/body
/html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Insert problem
John W. Holmes [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
From: Vincent Jordan [EMAIL PROTECTED]
Im having a problem inserting data. Ive looked over this again and again
and
can not find what ive missed. Everything else works besides the db
insert.
It would have helped if you told us the error you got was column count
does
not match value count or something similar...
$sql = INSERT INTO rmarequest (firstname, lastname, address, address2,
city, state, zip, phone, email, serial, product, reason,
rmanumber)VALUES
('$firstname', '$lastname', '$address', '$city', '$state', '$zip',
'$phone',
'$email', '$serial', '$product', '$reason', '$rmanumber') or die
(mysql_error());
You list 13 columns to insert into but only give 12 values.
Vincent, you can use the alternative INSERT syntax to avoid this problem in
the future:
INSERT INTO table SET
column1 = '$value1',
column2 = '$value2',
column3 = '$value3',
...
Regards, Torsten Roehr
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Insert problem
From: Vincent Jordan [EMAIL PROTECTED]
$sql = INSERT INTO rmarequest (firstname, lastname, address,
address2,
city, state, zip, phone, email, serial, product, reason,
rmanumber)VALUES
('$firstname', '$lastname', '$address', '$city', '$state',
'$zip', '$phone',
'$email', '$serial', '$product', '$reason', '$rmanumber') or die
(mysql_error());
Uhhh. where's mysql_query()???
$sql = INSERT ...;
$result = mysql_query($sql) or die(mysql_error());
---John Holmes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL Insert problem
Try echoing out your query to the browser window before you send it to the
database. A simple echo $sql should suffice. Then you can see what's
actually being sent in the query string to the database. Thar may show you
exactly what's going wrong. If your query string looks right, then the error
lies elsewhere.
Was your error reporting logic reporting that you had a mismatched number of
columns and arguments before? If not, then your error reporting logic is
probably not doing what you expect it to.
Rich
-Original Message-
From: Vincent Jordan [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 11:06 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] SQL Insert problem
I have inserted '$address2', correctly in the row however it
is still not
putting the data in the table.
I am not getting an error but I do believe I have correct
syntax set to
display problems.
-Original Message-
From: Hutchins, Richard [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 10:26 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] SQL Insert problem
You're missing address2 in your list of values. This means
that you have
an
unmatching number of column names and values in your query
and that'll
make
the query bomb.
Rich
-Original Message-
From: Vincent Jordan [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 10:25 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] SQL Insert problem
Im having a problem inserting data. Ive looked over this
again and again and
can not find what ive missed. Everything else works besides
the db insert.
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
?php
ini_set ('display_errors', 1);
error_reporting (E_ALL ~E_NOTICE);
// Define
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$serial = $_POST['serial'];
$product = $_POST['product'];
$reason = $_POST['reason'];
$gold = $_POST['gold_button_y'];
$goldaccount = $_POST['goldaccount'];
$rmanumber = $lastname{0}.date(ndyGi);
$connect = mysql_connect(SERVER , USER, PASSWORD) or die
(mysql_error());
$select = mysql_select_db (spdata) or die (mysql_error());
$sql = INSERT INTO rmarequest (firstname, lastname, address,
address2,
city, state, zip, phone, email, serial, product, reason,
rmanumber)VALUES
('$firstname', '$lastname', '$address', '$city', '$state',
'$zip', '$phone',
'$email', '$serial', '$product', '$reason', '$rmanumber') or die
(mysql_error());
if (isset($_POST['submit'])) {
$sql;
}
// Send and put in email message
$htmlheader = Content-Type: text/html; charset=us-ascii\n;
$htmlheader .= Content-Transfer-Encoding: 7bit;
$header = $from; // set the from field in the header
$header .= \n; // add a line feed
$header .= MIME-version: 1.0\n; //add the mime-version header
$header .= $htmlheader.\n;
$from = From: RMA Request [EMAIL PROTECTED];
$message = $firstname $lastname
$address
$address2
$city
$state
$zip
$phone
$email
$product
$serial
$gold
$goldaccount
$reason
$rmanumber;
// Send email to support
mail([EMAIL PROTECTED], RMA Request, $message, $header);
?
html xmlns=http://www.w3.org/1999/xhtml;
head
titleUntitled Document/title
/head
body
pstrongRMA Request Sent/strong/p
pYour RMA Number is strong ? echo $rmanumber ?
/strong /p
pPlease include a note within your package with your
shipping address,
phone number and discription of the problem.br /When
shipping Smart Parts
reccomends insuring your package for the full replacment cost.
We also advise purchasing tracking services if using
the postal
service./p
pstrongShip your return to: /strong/p
pSmart Parts, Incbr /
ATTN ? echo $rmanumber ?br /
Loyanhanna Business Complexbr /
100 Station St.br /
Loyalhanna Pa. 15661/p
pbr /
Please allow up to one week for package delivery.
For status information please call 800-992-2147 and ask
for the returns
department./p
a href=# onClick=window.print();Click Here to print
this page/abr
/
a href=form.htmClick here to return to RMA Request form/a
/body
/html
--
PHP Database Mailing List (http://www.php.net
RE: [PHP-DB] SQL Insert problem
1. echo your $sql to make certain it's as sound as you think.
2. i don't see execution of the query: mysql_query( $sql) - you'll have to
put the die() error after this function.
Hth - mthompson
At 12:05 PM 8/5/2004, Vincent Jordan wrote:
I have inserted '$address2', correctly in the row however it is still not
putting the data in the table.
I am not getting an error but I do believe I have correct syntax set to
display problems.
-Original Message-
From: Hutchins, Richard [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 10:26 AM
To: [EMAIL PROTECTED]
Subject: RE: [PHP-DB] SQL Insert problem
You're missing address2 in your list of values. This means that you have
an
unmatching number of column names and values in your query and that'll
make
the query bomb.
Rich
-Original Message-
From: Vincent Jordan [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 05, 2004 10:25 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] SQL Insert problem
Im having a problem inserting data. Ive looked over this
again and again and
can not find what ive missed. Everything else works besides
the db insert.
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
?php
ini_set ('display_errors', 1);
error_reporting (E_ALL ~E_NOTICE);
// Define
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$address = $_POST['address'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$serial = $_POST['serial'];
$product = $_POST['product'];
$reason = $_POST['reason'];
$gold = $_POST['gold_button_y'];
$goldaccount = $_POST['goldaccount'];
$rmanumber = $lastname{0}.date(ndyGi);
$connect = mysql_connect(SERVER , USER, PASSWORD) or die
(mysql_error());
$select = mysql_select_db (spdata) or die (mysql_error());
$sql = INSERT INTO rmarequest (firstname, lastname, address,
address2,
city, state, zip, phone, email, serial, product, reason,
rmanumber)VALUES
('$firstname', '$lastname', '$address', '$city', '$state',
'$zip', '$phone',
'$email', '$serial', '$product', '$reason', '$rmanumber') or die
(mysql_error());
if (isset($_POST['submit'])) {
$sql;
}
// Send and put in email message
$htmlheader = Content-Type: text/html; charset=us-ascii\n;
$htmlheader .= Content-Transfer-Encoding: 7bit;
$header = $from; // set the from field in the header
$header .= \n; // add a line feed
$header .= MIME-version: 1.0\n; //add the mime-version header
$header .= $htmlheader.\n;
$from = From: RMA Request [EMAIL PROTECTED];
$message = $firstname $lastname
$address
$address2
$city
$state
$zip
$phone
$email
$product
$serial
$gold
$goldaccount
$reason
$rmanumber;
// Send email to support
mail([EMAIL PROTECTED], RMA Request, $message, $header);
?
html xmlns=http://www.w3.org/1999/xhtml;
head
titleUntitled Document/title
/head
body
pstrongRMA Request Sent/strong/p
pYour RMA Number is strong ? echo $rmanumber ? /strong /p
pPlease include a note within your package with your
shipping address,
phone number and discription of the problem.br /When
shipping Smart Parts
reccomends insuring your package for the full replacment cost.
We also advise purchasing tracking services if using the postal
service./p
pstrongShip your return to: /strong/p
pSmart Parts, Incbr /
ATTN ? echo $rmanumber ?br /
Loyanhanna Business Complexbr /
100 Station St.br /
Loyalhanna Pa. 15661/p
pbr /
Please allow up to one week for package delivery.
For status information please call 800-992-2147 and ask
for the returns
department./p
a href=# onClick=window.print();Click Here to print
this page/abr
/
a href=form.htmClick here to return to RMA Request form/a
/body
/html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL help
I have tried that and I don't get an error, but I don't get any records returned either. And I have lowered the search string like you mentioned. Here's what I tried ( Access syntax ): SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE LCase(fldBody) LIKE '%$strSearchFor%'; Nicole Swan wrote: Have you tried lowering the fldBody as well? Like: SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE LOWER(fldBody) LIKE '%$strSearchFor%'; And $strSearchFor has already been lowered, of course. --Nicole --- Nicole Swan Web Programming Specialist Carroll College CCIT (406)447-4310 -Original Message- From: Gabe [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 23, 2004 8:59 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] SQL help I'm using PHP with ADOdb ( and an MS Access 2000 db ) to write a simple SQL statement but was running into some case sensitivity issues. Here's my SQL currently: SELECT autoQuesID,fldQuesTitle,fldBody FROM tblFAQ_Question WHERE fldBody LIKE '%$strSearchFor%'; All I'm trying to do is have the users search string searched for in the fldBody field. However, I'm having problems trying to get it so that the search is case-insensitive. For instance: If I search on Airline, I get 1 record. If I search on airline, I get 0 records. I make the value of $strSearchFor lower case ( using strtolower() ), but I don't know how to get it so that the contents of the fldBody field is lower case also. I can't seem to find any functions or operators that remove the case-sensitivity. Any help would be much appreciated! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Statement
Jimi,
PHP does not return an error because it knows nothing about valid sql.
It's just knows if it's a valid PHP statement. (which it is because
you've got the 's in the right place and a ; at the end. ) :)
It's up to MySQL to return an error.
As to your statement.
1: It's easier and valid in PHP to write it:
$sqlwrk = SELECT pk_phone_reports,
SUM(calls) AS total_calls,
date,
calls ,
fk_ph_num
FROM phone_reports
WHERE (pk_phone_number = {fk_phone}) AND
(date BETWEEN '{$my_startdate}' AND
'$my_enddate')
GROUP BY pk_phone_reports,
fk_ph_num,
date,
calls;
In reformatting the string I found a couple of PHP things that are
probably what's tripping you up. PHP used the . as a string
concatonator. You were missing several of them. Notice that I remove
them all. Because we enclosed the entire statement in a sing pair, we
can use {$variable} for substitution. Makes life a lot easier when
building sql statements.
Also, I removed all the back-tiks. Not because they were wrong but they
annoy my and in 99% of the cases are not necessary.
Oh and welcome to PHP/MySQL. I hope you'll find the language easy and
the people friendly.
Finally, a could of things that will make life easier for you if you are
going to be doing much database work in PHP/MySQL.
http://php.weblogs.com/
This is the best database abstraction layer I've found. Even if you
only use MySQL, it's worth the investment in time to learn it. (mainly
for the debug feature.)
www.sqlyog.com
Best FE for MySQL on Windows I've ever found. It's $49.00 (I think...I
forget) but it's worth it. It's got it's bugs but overall it's a killer
tool.
http://www.fabforce.net/dbdesigner4/
A killer, open source tool for designing databases. If you are used to
the commercial tools costing $4k+ then you'll feel right at home with
this. (It's my understanding that MySQL has purchased this project, but
I may have my story wrong.)
Finally, you've already found the greatest tool for debugging MySQL/php,
the lists.
See ya round.
=C=
:
: Cal Evans
: Evans Internet Construction Company
: 615-360-3385
: http://www.eicc.com
: Building web sites that build your business
:
Thompson, Jimi wrote:
For some background, I've been tasked with building a marketing tracking application by the PHB's who think that being able to write SQL means you can code. I know how to get data into a database and I can do thing with it once it's in there, but this is one of my first attempts at extracting anything remotely end-user-ish. Since the only server I can get is an old cobalt RAQ 2, the only database I can run is MySQL. Im not terribly familiar with MySQL (spent more time working with commercial databases) and Im a complete newbie at PHP, so please dont flame me yet
Im not even sure what information Ill need to provide you so here goes:
Platform Red Hat 9.0 Linux on a BogoMIPS CPU
PHP Version - 4.3.3
Apache Version - 1.3.28
MySQL Version - 4.0.14
Heres my SQL statement which works fine from a DBA perspective (meaning that I can execute it from the command line against the database and obtain the desired results), but Im obviously missing something in the syntax in converting this to an acceptable PHP SQL statement.
I know that I can connect to the database and can extract other records, but I keep getting unable
to parse error message and dont know enough to know which thing Im doing is wrong.
SELECT
phone_reports.pk_phone_reports,
SUM(phone_reports.calls) AS total_calls,
phone_reports.fk_ph_num,
phone_reports.`date`,
phone_reports.calls
FROM
phone_reports
WHERE
(phone_reports.fk_ph_num = 1) AND
(phone_reports.`date` BETWEEN '2004/05/17' AND '2004/07/05')
GROUP BY
phone_reports.pk_phone_reports,
phone_reports.fk_ph_num,
phone_reports.`date`,
phone_reports.calls
Heres the PHP SQL statement built from the SQL statement above
?php
if ($fk_phone != NULL) {
$sqlwrk = SELECT `pk_phone_reports`, `date`, `calls` , `fk_ph_num` FROM
`phone_reports`;
$sqlwrk .= WHERE `pk_phone_number` = . $fk_phone;
$rswrk = mysql_query($sqlwrk);
if ($rswrk $rowwrk = mysql_fetch_array($rswrk)) {
echo $rowwrk[number];
}
@mysql_free_result($rswrk);
}
?
This seems to work ok, but doesnt return any results (which I expected) but it does parse! So then I try do this
?php
if ($fk_phone != NULL) {
$sqlwrk = SELECT `pk_phone_reports`, SUM(`calls`) AS `total_calls`,
`date`, `calls` , `fk_ph_num` FROM `phone_reports`;
$sqlwrk .= WHERE `pk_phone_number` = . $fk_phone;
$rswrk = mysql_query($sqlwrk);
if ($rswrk $rowwrk = mysql_fetch_array($rswrk)) {
echo $rowwrk[number];
}
@mysql_free_result($rswrk);
}
?
Note that this shouldnt
Re: [PHP-DB] SQL Statement
Thompson, Jimi wrote:
So then I try do this
?php
if ($fk_phone != NULL) {
$sqlwrk = SELECT `pk_phone_reports`, SUM(`calls`) AS `total_calls`,
`date`, `calls` , `fk_ph_num` FROM `phone_reports`;
$sqlwrk .= WHERE `pk_phone_number` = . $fk_phone;
$rswrk = mysql_query($sqlwrk);
if ($rswrk $rowwrk = mysql_fetch_array($rswrk)) {
echo $rowwrk[number];
}
@mysql_free_result($rswrk);
}
?
Note that this shouldnt work since it isnt a valid SQL statement.
I'm not sure why PHP doesn't return some kind of an error message.
PHP does return an error message, you're just not displaying it.
$rswrk = mysql_query($sqlwrk) or die(mysql_error());
$sqlwrk .= WHERE (`pk_phone_number` = . $fk_phone) AND
(`date` BETWEEN '$my_startdate' AND '$my_enddate');
Which brings me to my lovely parse error Parse error:
You're not concatinating your string correctly.
$sqlwrk .= WHERE (`pk_phone_number` = . $fk_phone . ) AND
(`date` BETWEEN ' . $my_startdate . ' AND ' . $my_enddate . ');
or
$sqlwrk .= WHERE (`pk_phone_number` = $fk_phone) AND
(`date` BETWEEN '$my_startdate' AND '$my_enddate');
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL question!
I have these tables. Users ( id,name,etc ) Coments : ( id , comment ) How do I do this kind of query: I thought in one thing like this but I cant figure it out. Example: Select * from users order by id desc in (select count (id) from comments) Expected result: List of users: * User1 See comments ( 32 comment in database ) * User2 See coments (13 comments in database ) You need to add a user_id column to comments, which records what user entered each comment. Then you can do SELECT u.name, COUNT(c.id) FROM users u, comments c WHERE u.id=c.user_id GROUP BY (c.user_id) The syntax may not be exactly right but that's the basic idea. Adding a user ID foreign key to the comments table is the important part. Hope that helps, Larry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL for Showing the number of queries served on each day.
Vern wrote: I found this code below that allows me to retreive the queries served on my server for each day but can't figure out how to actually display the information using echo. Can some one give me an example using the following SQL? SELECT DATE_FORMAT(ex_date, '%Y %m %d %W'), COUNT(id) FROM email WHERE ex_dateNow()-INTERVAL 50 DAY GROUP BY DATE_FORMAT(ex_date, '%Y %m %d %W') ORDER BY DATE_FORMAT(ex_date, '%Y %m %d %W') DESC You probably just need to use an alias. SELECT DATE_FORMAT(ex_date, '%Y %m %d %W') as mydate, COUNT(id) as mycount Then, you'll have columns called mydate and mycount in your result set. Without knowing what database you're using, it's hard to give an example with exact code. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL for Showing the number of queries served on each day.
That did it thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server Query Failed
david wrote: Hello there! I have just about driven myself crazy with an odd intermittent problem. [snip] I'd first start by turning on all logging I could in the SQL server so that I could see what's happening straight from the horse's mouth... Bruno Ferreira --- [This E-mail scanned for viruses by Declude Virus] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Server Query Failed
What type of field is PhaseFK? -- bob On Wed, 17 Mar 2004, david wrote: Hello there! I have just about driven myself crazy with an odd intermittent problem. I have an intranet site, a good size one at that, on a Windows 2000 Server, running Apache, connecting to another Windows 2000 Server running SQL Server 2000. It all works just peachy, and life is good. But (there is always a but). Now and then, I get a Query Failed message. I cannot find the source of the problem, no matter how hard I try. I can't even find an error message, which is really odd. The query looks like this (it is a dynamic query, so it can change): SELECT PhaseFK FROM Facts WHERE (CategoryFK=5) The message I get back looks like this: Warning: mssql_query(): Query failed in runsql.php on line 10 I tried looking at the following to get an error message: mssql_query(SELECT @@ERROR as ErrorCode); (it returns nothing) mssql_get_last_message (it returns nothing, OR, sometimes it tells me that it changed the database context) What I think might be going on (because it ONLY happens with certain tables) is that the table is locked (there are some external processes that work with this table, which fire off more or less randomly from my code's point of view) or is other unavailable for some reason which eludes me. But I can't seem to figure out how to tell if this is the case or not. It seems to me that if I get a Query Failed then somewhere lurks an error code, and thus I can trap it nicely, instead of simply failing (that bugs me from a user point of view). Any thoughts? Thanks! david -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL File Import problem (Was: HELP!!!)
I have the same problem right now, after a very short search in google i found this: http://www.mysql.com/documentation/mysql/bychapter/manual_Problems.html#Gone _away In my case i get the message max_allowed_packet is too small because i was trying to insert an image 1M into the database. - Original Message - From: Doug Thompson [EMAIL PROTECTED] To: Robin 'Sparky' Kopetzky [EMAIL PROTECTED] Cc: Erwin Kerk [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, February 27, 2004 1:04 AM Subject: Re: [PHP-DB] SQL File Import problem (Was: HELP!!!) On Fri, 27 Feb 2004 01:16:20 +0100, Erwin Kerk wrote: Robin 'Sparky' Kopetzky wrote: Good afternoon! I used SQLYOG to export the tables and data from a Mysql database. Now, when i try to re-import the data back into a different database, I get an error stating Error : MySQL server has gone away. What is happening and how do I fix this. I NEED this script to execute badly. I even tries running it under mysql using source filename.sql and got the same error. One table is over 75,000 entries. The queries are taking too long. Are this plain .sql files? If so, try to split them up in say, 10 separate files, and import them all separately. That should work. And in the futue, try putting a more descriptive text in youre subject. Erwin Kerk Web Developer -- Erwin is exactly right.That being said: You don't say and it's risky to assume if you can do any other tasks on the new server. In other words, is it running at all? You don't say which of SQLyog's methods you used to create the backup. In this case, I would have used DB - Export Database as Batch Scripts and I would save the file as somefile.sql and transfer that file to the mysql/bin directory on the new system. Assuming mysqld is running on the new system, cd to the mysql/bin subdirectory and type ./mysql -uusername -ppassword somefile.sql Of course, all the foregoing syntax for re-installing presumes *n*x. I move databases from my local windows system to a remote *n*x site frequently using the above process and it is very reliable and repeatable. hth, Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL File Import problem (Was: HELP!!!)
On Fri, 27 Feb 2004 01:16:20 +0100, Erwin Kerk wrote: Robin 'Sparky' Kopetzky wrote: Good afternoon! I used SQLYOG to export the tables and data from a Mysql database. Now, when i try to re-import the data back into a different database, I get an error stating Error : MySQL server has gone away. What is happening and how do I fix this. I NEED this script to execute badly. I even tries running it under mysql using source filename.sql and got the same error. One table is over 75,000 entries. The queries are taking too long. Are this plain .sql files? If so, try to split them up in say, 10 separate files, and import them all separately. That should work. And in the futue, try putting a more descriptive text in youre subject. Erwin Kerk Web Developer -- Erwin is exactly right.That being said: You don't say and it's risky to assume if you can do any other tasks on the new server. In other words, is it running at all? You don't say which of SQLyog's methods you used to create the backup. In this case, I would have used DB - Export Database as Batch Scripts and I would save the file as somefile.sql and transfer that file to the mysql/bin directory on the new system. Assuming mysqld is running on the new system, cd to the mysql/bin subdirectory and type ./mysql -uusername -ppassword somefile.sql Of course, all the foregoing syntax for re-installing presumes *n*x. I move databases from my local windows system to a remote *n*x site frequently using the above process and it is very reliable and repeatable. hth, Doug -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL File Import problem (Was: HELP!!!)
Thank you to all who helped. I was able using UltraEdit to chop up the sql file and created all of the individual INSERT statements. Now, I'm up and running again. Slow but it worked! Thanks again! Robin Kopetzky -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL File Import problem (Was: HELP!!!)
Robin 'Sparky' Kopetzky wrote: Good afternoon! I used SQLYOG to export the tables and data from a Mysql database. Now, when i try to re-import the data back into a different database, I get an error stating Error : MySQL server has gone away. What is happening and how do I fix this. I NEED this script to execute badly. I even tries running it under mysql using source filename.sql and got the same error. One table is over 75,000 entries. The queries are taking too long. Are this plain .sql files? If so, try to split them up in say, 10 separate files, and import them all separately. That should work. And in the futue, try putting a more descriptive text in youre subject. Erwin Kerk Web Developer -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql, problem with join and presentation
i think the newer versions of MYSQL allow for subselects and I think that is what you want. see www.mysql.net -Original Message- From: mayo [mailto:[EMAIL PROTECTED] Sent: Sunday, February 15, 2004 11:56 PM To: php-db Subject: [PHP-DB] sql, problem with join and presentation Currently I display a list of classes. Simplified SQL and display below: SELECT * FROM classes WHERE classCategory='$Category' AND classDeleted=0 ORDER BY $order $reorder The presentation is: +--+--++ | CLASS TITLE | LOCATION | CLASS CODE | +--+--++ | CLASS DESCRIPTION br/br/ | | CLASS INSTRUCTOR br/br/ | | CLASS TIME | +--+ Now, things are getting a little more complicated. Each class is going to have sections. So the display will be: CLASS TITLE CLASS DESCRIPTION CLASS CODE : CLASS SECTION .. LOCATION .. CLASS TIME .. INSTRUCTOR example (simplified) +-+ | INTRO TO AAA| +-+ | This is a really interesting | | | +-+-+++ |HT-111:A | NYC | 12:00-4:00 | Albert Alkin | |HT-111:B | JC | 2:00-6:00 | Bob Bailey | |HT-111:C | BX | 4:00-8:00 | Chris Cawley | +-+-+++ I'm having a really hard time coming up with the sql for this. I want to (pseudo) select * from classes and classSections where classDeleted=0 and group by classCode tables below CLASSES classID classDescription classTexts classCost classDeleted CLASSCODES classCodeID classID classCodeSection classDate classTime classLocation classInstructor I'm going nuts trying to get this. I must be missing something simple. (using mysql) thx for any clues Gil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Disclaimer This e-mail transmission contains confidential information, which is the property of the sender. The information in this e-mail or attachments thereto is intended for the attention and use only of the addressee. Should you have received this e-mail in error, please delete and destroy it and any attachments thereto immediately. Under no circumstances will the Cape Technikon or the sender of this e-mail be liable to any party for any direct, indirect, special or other consequential damages for any use of this e-mail. For the detailed e-mail disclaimer please refer to http://www.ctech.ac.za/polic or call +27 (0)21 460 3911 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql, problem with join and presentation
There are two ways to do what you ask:
1 - run your first query (just the classes no sections) and before the loop
to display the results open a new - different connection to the DB then
in the loop where you are displaying the results after displaying each class
run a query to find all of it's sections and display them. Possible
weakness is many connections to the DB (not a big deal but may not scale
well) and if there is a class that has no section this quarter then you
would only find that out after you displayed the class (could be fixed in
your first query or by checking for sections before you displayed the class)
2 - Select everything (classes and sections) in one query
SELECT *
FROM classes, CLASSCODES
WHERE classCategory='$Category'
AND classDeleted=0
AND CLASSCODES.classID = CLASSES classID
ORDER BY $order $reorder -- not sure what you are doing here but you
will need to add classID at the end of this list.
Now you will get back these columns:
classID classDescription classTexts classCost classDeleted classCodeID
classID classCodeSection classDate classTime classLocation classInstructor
And the columns from the CLASSES table will be duplicated for each section
(this is why you must sort by classID to keep them all together).
So before the loop to display the results you set $thisClassID = 0;
And first thing in the loop you check:
if ($thisClassID != result[classID])
{
/*This is a new class, display it's info*/
echo result[classDescription]
/* don't forget to reset this */
$thisClassID = result[classID];
}
/* now display the section info... */
Good Luck,
Frank
On 2/17/04 9:49 AM, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
From: mayo [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 15, 2004 11:56 PM
To: php-db
Subject: [PHP-DB] sql, problem with join and presentation
Currently I display a list of classes.
Simplified SQL and display below:
SELECT *
FROM classes
WHERE
classCategory='$Category' AND
classDeleted=0
ORDER BY $order $reorder
The presentation is:
+--+--++
| CLASS TITLE | LOCATION | CLASS CODE |
+--+--++
| CLASS DESCRIPTION br/br/ |
| CLASS INSTRUCTOR br/br/ |
| CLASS TIME |
+--+
Now, things are getting a little more complicated. Each class is going to
have sections. So the display will be:
CLASS TITLE
CLASS DESCRIPTION
CLASS CODE : CLASS SECTION .. LOCATION .. CLASS TIME .. INSTRUCTOR
example (simplified)
+-+
| INTRO TO AAA|
+-+
| This is a really interesting |
| |
+-+-+++
|HT-111:A | NYC | 12:00-4:00 | Albert Alkin |
|HT-111:B | JC | 2:00-6:00 | Bob Bailey |
|HT-111:C | BX | 4:00-8:00 | Chris Cawley |
+-+-+++
I'm having a really hard time coming up with the sql for this.
I want to (pseudo)
select *
from classes and classSections
where classDeleted=0
and group by classCode
tables below
CLASSES
classID
classDescription
classTexts
classCost
classDeleted
CLASSCODES
classCodeID
classID
classCodeSection
classDate
classTime
classLocation
classInstructor
I'm going nuts trying to get this. I must be missing something simple.
(using mysql)
thx for any clues
Gil
--
Frank Flynn
Poet, Artist Mystic
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql, grouping problem
mayo wrote:
cold fusion allows you to group output (see below)
Select c.classId, c.classTexts, c.classDescription,
cc.classCodeSection, cc.classDate, cc.classTime,
cc.classLocation, cc.classInstructor
FROM CLASSES c, CLASSCODES cc
WHERE c.classId = cc.classId
AND ...
ORDER BY c.classId, ...
cfoutput query=myQuery group=classId
#classTitle#
#classDescription#br...
cfoutput
#classCodeSection#br
#classDate#br
#classTime#br
/cfoutput
/cfoutput
I can't figure out how to do this in php.
You just have to remember the value of the classID as you loop through
the results, and only show the header row when the classID changes.
//Empty classID
$old_classID = '';
//Loop through results
while($row = mysql_fetch_assoc($result))
{
//show title and description when
//classID changes
if($row['classID'] != $old_classID)
{
echo trtd colspan=\3\{$row['title']}/td/tr;
echo trtd colspan=\3\{$row['description']}/td/tr;
$old_classID = $row['classID'];
}
//show rest of data
echo trtd{$row['code']}/td;
echo td{$row['section']}/td;
echo td{$row['location']}/td/tr;
}
The logic is that the title and description rows are only shown when
classID changes in the result set. I showed it using MySQL functions,
but that can apply to any database/abstraction layer you've got running.
Hope that helps.
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql query, editing?
Create a form for editing the record
Then on the display funtion just put a link on each record to that form
and pass the id of that record like a
href=editrecord.php?id=?=$row['id']?edit/a
On the edit form just grab the data of the $id passed on the url and put
those values on the input fields like input type=text name=field1
value=?=$row['field1']?
Than just save the form result into the database with an UPDATE
tablename SET filed1='$field1' ... WHERE id='$id'); ...
Dont' forget the bit of code here are just examples and very insecure
...
Need to work on the validation etc...
Humberto Silva
World Editing
Portugal
-Original Message-
From: Louie Miranda [mailto:[EMAIL PROTECTED]
Sent: quinta-feira, 15 de Janeiro de 2004 8:00
To: [EMAIL PROTECTED]
Subject: [PHP-DB] sql query, editing?
I have this code below, it fetches data on a mysql database. I was
hoping you could give me a code hint on where could, my goal is to
display this data on a browser which i did already and be able to edit
it via a form.
edit? - table1=value - table2=value
I dont know where to start. please help me, i hope i can display all the
data and have a button for editing and catch which one to edit.
## code ##
$result = mysql_query(select
product_code,title,language,issue,category,cost from iip_t_cp where
issue = $issue and category = '$category' and language = '$language' and
depleted = '$depleted', $connect); $num_rows = mysql_num_rows($result);
function display($result)
{
echo h1pricelist records/h1\n;
echo br;
echo \ntable cellspacing=3 cellpadding=3 border=1\ntr\n .
\nthproduct
code/ththtitle/ththlanguage/ththissue/ththcategory/th
thc
ost/th .
\n/tr;
while ($row = @ mysql_fetch_row($result))
{
echo \ntr;
foreach($row as $data)
echo \n\ttd $data /td;
echo \n/tr;
}
echo \n/table;
}
display($result);
## code ##
-- -
Louie Miranda
http://www.axishift.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL query...
SELECT DISTINCT(file_name), Count(file_name) FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by ??? desc In the above sql statement, I'm trying to achieve: 1. select all file names, between two dates. 2. list them, and order by the highest number of occurences of count() Basically, it's for a download tool we have, and my boss wants to easily be able to see the top downloaded files. It all works, but not the 'order by' bit... what do I have to order by... it's not 'file_name', and 'order by count(file_name0' causes an error... thoughts? Cheers, Tris... - can you not do this? SELECT DISTINCT(file_name), Count(file_name)fcount FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by fcount desc Please note that I have named count(file_name) fcount in the sql statement Hope this helps? Brett -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
