[PHP] SQL Injection
Dear List - I am aware of a long email trail on this subject, but there does not seem to be a resolution. Is it possible to have a meeting of the minds to come up with (an) appropriate method(s)? Thanks. Ethan Rosenberg -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection
On Fri, Jun 8, 2012 at 12:37 PM, Ethan Rosenberg eth...@earthlink.net wrote: Is it possible to have a meeting of the minds to come up with (an) appropriate method(s)? Minds, meet prepared statements :) Adam -- Nephtali: A simple, flexible, fast, and security-focused PHP framework http://nephtaliproject.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Injection
-Original Message- From: Adam Richardson [mailto:simples...@gmail.com] Sent: Friday, June 08, 2012 11:50 AM To: PHP-General Subject: Re: [PHP] SQL Injection On Fri, Jun 8, 2012 at 12:37 PM, Ethan Rosenberg eth...@earthlink.net wrote: Is it possible to have a meeting of the minds to come up with (an) appropriate method(s)? Minds, meet prepared statements :) Adam -- Nephtali: A simple, flexible, fast, and security-focused PHP framework http://nephtaliproject.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php PDO is the way to go :D Jen -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection
Is it possible to have a meeting of the minds to come up with (an) appropriate method(s)? Minds, meet prepared statements :) PDO is the way to go :D Not to refute the above advice one bit (not to mention oppose the arguments against escaping in general) ... but just curious - can anyone demo a hack that effectively injects past mysqli_real_escape_string(), while using utf-8 ? It may just be a matter of time (or already?) before mysqli_real_escape_string is *proven* ineffective (w/utf-8) ... but here I am just attempting to gather facts. Thanks -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection
On 06/08/2012 10:31 AM, Govinda wrote: Is it possible to have a meeting of the minds to come up with (an) appropriate method(s)? Minds, meet prepared statements :) PDO is the way to go :D Not to refute the above advice one bit (not to mention oppose the arguments against escaping in general) ... but just curious - can anyone demo a hack that effectively injects past mysqli_real_escape_string(), while using utf-8 ? It may just be a matter of time (or already?) before mysqli_real_escape_string is *proven* ineffective (w/utf-8) ... but here I am just attempting to gather facts. Thanks -Govinda Ah, but what if I use sqlite or postgres? IMHO, the discussion needs to be a the best way to prevent SQL injection across all possible DB types. Not just mysql. -- Jim Lucas http://www.cmsws.com/ http://www.cmsws.com/examples/ http://www.bendsource.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection
Jim Lucas wrote: Not to refute the above advice one bit (not to mention oppose the arguments against escaping in general) ... but just curious - can anyone demo a hack that effectively injects past mysqli_real_escape_string(), while using utf-8 ? It may just be a matter of time (or already?) before mysqli_real_escape_string is *proven* ineffective (w/utf-8) ... but here I am just attempting to gather facts. Ah, but what if I use sqlite or postgres? Or Firebird ;) IMHO, the discussion needs to be a the best way to prevent SQL injection across all possible DB types. Not just mysql. The main thing to avoid is building queries from elements that are directly loaded from the form inputs. While it is difficult to build sort elements for queries that use parameters, having a mechanism like ADOdb's datadict where one can filter SQL based on the identified field names does make life easier. While the problems of dealing with student names such as 'Delete from student' are easily solved by only using them in parameter arrays. A few simple basics cover the vast majority of traditional SQL injection problems? -- 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// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection
Ah, but what if I use sqlite or postgres? Or Firebird ;) good point. IMHO, the discussion needs to be a the best way to prevent SQL injection across all possible DB types. Not just mysql. The main thing to avoid is building queries from elements that are directly loaded from the form inputs. While it is difficult to build sort elements for queries that use parameters, having a mechanism like ADOdb's datadict where one can filter SQL based on the identified field names does make life easier. While the problems of dealing with student names such as 'Delete from student' are easily solved by only using them in parameter arrays. A few simple basics cover the vast majority of traditional SQL injection problems? Yes, apparently. Part of why I even asked is to get a sense of the shelf life on legacy code (that relies on escaping) which I am not keen to have to re-write, for free, until I really must. -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection
Govinda govinda.webdnat...@gmail.com wrote: Ah, but what if I use sqlite or postgres? Or Firebird ;) good point. IMHO, the discussion needs to be a the best way to prevent SQL injection across all possible DB types. Not just mysql. The main thing to avoid is building queries from elements that are directly loaded from the form inputs. While it is difficult to build sort elements for queries that use parameters, having a mechanism like ADOdb's datadict where one can filter SQL based on the identified field names does make life easier. While the problems of dealing with student names such as 'Delete from student' are easily solved by only using them in parameter arrays. A few simple basics cover the vast majority of traditional SQL injection problems? Yes, apparently. Part of why I even asked is to get a sense of the shelf life on legacy code (that relies on escaping) which I am not keen to have to re-write, for free, until I really must. -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I think you can happily sanitise data where it makes sense, and use bound parameters elsewise. So when you expect a number, its easy to check for and force a sensible default. Likewise for things like dates, or names of articles (probably a popular need with a CMS) you can check and enforce particular characters. Outside of that, without bound params you run a potential risk (even if only slight). You can do stuff like base64 encode values, but then you lose a lot of the ability to search through your DB after. Thanks, Ash http://ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection
I think you can happily sanitise data where it makes sense, and use bound parameters elsewise. So when you expect a number, its easy to check for and force a sensible default. Likewise for things like dates, or names of articles (probably a popular need with a CMS) you can check and enforce particular characters. Outside of that, without bound params you run a potential risk (even if only slight). You can do stuff like base64 encode values, but then you lose a lot of the ability to search through your DB after. What would you say in the case of having used CodeIgniter (w/it's modified 'Active Record Class', before PDO was an (easy/built-in) option in CodeIgniter) to develop an app that serves content in dozen(s) of languages through a custom international CMS... and now they want a search box so end users can search all the pages (db data) of the site for that country (in that country's main language)? IOW form input that I cannot just force/sanitize to e.g. (english) alphanumeric (+ spaces), and I cannot just switch to using PDO without rewriting all the code in all the model files. Thanks -Govinda -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql injection protection
This is an interesting conversation, so I'm glad it got brought up,but I find myself curious: Are you actually trying to avoid PDO, or just trying to learn how the security actually works? Well, It's a learning process. my point is this... If I can make it safe and sound without the PDO, then I really got to the bottom of it. Because once you reach there and I would be in a much better shape cause at the end, I will still use PDO level. PDO is not safe. I should say, it is not SAFE ENOUGH. You are still vulnerable with PDO as well. Cause PDO still requires you to validate your input. If you don't do a good job at it, then you are using PDO as a drug. You have to go down to bottom of it and that's validating the darn user input. Well, if you validate your input well, then one can turn the question around and ask, then why use PDO? It's not going to make it any safer! It was already so. The danger with the PDO articles... Using/or Recommending PDO without the nitty/gritty details of how important it is to validate your input is unfortunately leading people ( unexp. dev ) into thinking that it's a safer method, therefore they can go relax at certain things and PDO will cover them. I think one should try to make his data secure, first and foremost - without *relying* PDO to take care of things. Therefore, we should learn the crux of the matter. By that, I mean all that multibyte and GPK Greek and some other weird char sets that one should be aware of and what to do to really safe guard the databases against all kinds of user data. Only then and only then, one should START thinking about using PDO. http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection That's why I started this thread. On Tue, Jan 17, 2012 at 4:39 AM, Andy McKenzie amckenz...@gmail.com wrote: On Mon, Jan 16, 2012 at 10:34 PM, Haluk Karamete halukkaram...@gmail.com wrote: I understand some ways are better than others in this one, and it looks like the PDO based implementations shine the most as far as SQL Injection. But would not the following be good enough - without implementing a PDO solution? This is an interesting conversation, so I'm glad it got brought up, but I find myself curious: Are you actually trying to avoid PDO, or just trying to learn how the security actually works? Personally, my decision was that I could spend a lot of time learning all the ins and outs, or I could just use PDO and some basic input validation, and be more-or-less secure. I'm sure there are cases where that's not sensible, but it's always worked for me. -Andy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] sql injection protection
I understand some ways are better than others in this one, and it looks like the PDO based implementations shine the most as far as SQL Injection. But would not the following be good enough - without implementing a PDO solution? 1- get the user input 2- for each input, deliberately enforce a data type ( that is date/string/integer ) and validate it. 3- for each input, deliberately enforce a data length ( for strings data length and for integer a valid range for example ) 4- check the magic_quotes_gpc and do the stripslashes and then the mysqli_real_escape_string() and the htmlentities. 5- and on top pf all this, I also check for the specific occurrences of these following words; if any exist, I just do not execute that SQL query. and that list is sysobjects, syscolumns, systypes, EXEC(@, CHAR(, exec%20, DECLARE%20@, wscript. CAST( CONVERT( 6- I also count the number of 0x occurrences, if the user input contains more than 2 of 0x, again I do not execute that command. With all these in place, I don't know of a way that a userinput can still make it thru. These may raise a false negative on some valid user input that's coming from a textarea where the data type is string, and an accepted char length is big enough to create some havoc in the db, so be it, I reject that input. My question even after all these are there still ways to break in? All the aboce can be easily tucked in a function which does a data validation something like VallidateUserInput ( $_GET['first_name'], varchar(100),Please supply a first name that is less than 100 characters); VallidateUserInput ( $_GET['age'], smallint,Please supply an age that is between 1-20) Your insight is greatly appreciated on this. I also read somewhere that mysql does NOT allow statement chaining. So, that's even better. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Syntax
On Tue, Jun 15, 2010 at 8:58 PM, Jan Reiter the-fal...@gmx.net wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) This is known as an EAV (Entity-Attribute-Value) design. It is usually (some would say always) a very bad idea to implement this in a relational database. and this is no exception. The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! The easy solution is to redesign the tables. There are a lot of reasons why this design is usually a very bad idea. For starters, what should be a simple query is anything but simple, as you have just discovered. What's more, there is no simple way (if any way at all) for your design to prevent an image from having a mime-type of 20174 or a size of 'jpg'. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Syntax [improved SQL]
Hi, this is the solution I came up with, that is over 10 times faster than my first attemps. Tested @31,871 entries in table 'picture' and 222,712 entries in table 'picture_attrib_rel'. Old Version: SELECT * FROM picture as p INNER JOIN picture_attrib_rel as pr1 ON (p.pid = pr1.pid) INNER JOIN picture_attrib_rel as pr2 ON (p.pid = pr2.pid and pr2.val_int 1500) WHERE pr1.aid = 2 AND pr1.val_int = 1500 AND pr2.aid = 5 AND pr2.val_int 1000 Takes about 1.9 Seconds on average to return. The version with temporary tables: DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; CREATE temporary table tmp_size SELECT pid FROM picture_attrib_rel WHERE aid = 2 AND val_int = 1500; CREATE temporary table tmp_qi SELECT pid FROM picture_attrib_rel WHERE aid = 5 AND val_int 1000; SELECT pid,uid FROM tmp_size JOIN tmp_qi USING(pid) JOIN pictures USING(pid); DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; This takes 0.12 seconds to return, which is quite bearable for now. Thanks again for all your input! Regards, Jan -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Syntax [improved SQL]
-Original Message- From: Jan Reiter [mailto:the-fal...@gmx.net] Sent: Wednesday, June 16, 2010 8:55 AM To: php-general@lists.php.net Subject: Re: [PHP] SQL Syntax [improved SQL] Hi, this is the solution I came up with, that is over 10 times faster than my first attemps. Tested @31,871 entries in table 'picture' and 222,712 entries in table 'picture_attrib_rel'. Old Version: SELECT * FROM picture as p INNER JOIN picture_attrib_rel as pr1 ON (p.pid = pr1.pid) INNER JOIN picture_attrib_rel as pr2 ON (p.pid = pr2.pid and pr2.val_int 1500) WHERE pr1.aid = 2 AND pr1.val_int = 1500 AND pr2.aid = 5 AND pr2.val_int 1000 Takes about 1.9 Seconds on average to return. The version with temporary tables: DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; CREATE temporary table tmp_size SELECT pid FROM picture_attrib_rel WHERE aid = 2 AND val_int = 1500; CREATE temporary table tmp_qi SELECT pid FROM picture_attrib_rel WHERE aid = 5 AND val_int 1000; SELECT pid,uid FROM tmp_size JOIN tmp_qi USING(pid) JOIN pictures USING(pid); DROP temporary table if exists tmp_size; DROP temporary table if exists tmp_qi; This takes 0.12 seconds to return, which is quite bearable for now. Thanks again for all your input! Regards, Jan Jan, What do you get from this query and how fast does it execute? SELECT * FROM picture_attrib_rel par INNER JOIN pictures p ON p.pid = par.pid WHERE (par.aid = 2 AND par.val_int = 1500) OR (par.aid = 5 AND par.val_int 1000) Regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Syntax
[Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ We now offer SAME-DAY SETUP on a new line of servers! -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Syntax
On Wed, 2010-06-16 at 02:58 +0200, Jan Reiter wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan You'll be looking for something like this (untested): SELECT * FROM a LEFT JOIN b ON (a.pid = b.pid) WHERE (b.aid = 1 AND b.value 100) OR (b.aid = 3 AND b.value = 'jpg') Obviously instead of the * you may have to change to a list of field names to avoid fieldname collision on the two tables. Thanks, Ash http://www.ashleysheridan.co.uk
RE: [PHP] SQL Syntax
Thanks. That was my first attempt, too. Only this will throw out rows, that meet only one of the conditions, too. For example, I would get all pictures that are bigger than 100, regardless of type, and all pictures that are of type jpg, no matter the size. Doing it with a view would be an option, but that would immensely decrease flexibility. I guess I have to keep on cooking my brain on this ;-) I think I did it before, a few years ago when MySQL didn't support views yet, but I can't find that stuff ... @Dan: Thanks for forwarding my mail to the MySQL List! Regards, Jan From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] Sent: Wednesday, June 16, 2010 3:09 AM To: Jan Reiter Cc: php-general@lists.php.net Subject: Re: [PHP] SQL Syntax On Wed, 2010-06-16 at 02:58 +0200, Jan Reiter wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan You'll be looking for something like this (untested): SELECT * FROM a LEFT JOIN b ON (a.pid = b.pid) WHERE (b.aid = 1 AND b.value 100) OR (b.aid = 3 AND b.value = 'jpg') Obviously instead of the * you may have to change to a list of field names to avoid fieldname collision on the two tables. Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Syntax
On Wed, 2010-06-16 at 03:23 +0200, Jan Reiter wrote: Thanks. That was my first attempt, too. Only this will throw out rows, that meet only one of the conditions, too. For example, I would get all pictures that are bigger than 100, regardless of type, and all pictures that are of type jpg, no matter the size. Doing it with a view would be an option, but that would immensely decrease flexibility. I guess I have to keep on cooking my brain on this ;-) I think I did it before, a few years ago when MySQL didn't support views yet, but I can't find that stuff ... @Dan: Thanks for forwarding my mail to the MySQL List! Regards, Jan From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] Sent: Wednesday, June 16, 2010 3:09 AM To: Jan Reiter Cc: php-general@lists.php.net Subject: Re: [PHP] SQL Syntax On Wed, 2010-06-16 at 02:58 +0200, Jan Reiter wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan You'll be looking for something like this (untested): SELECT * FROM a LEFT JOIN b ON (a.pid = b.pid) WHERE (b.aid = 1 AND b.value 100) OR (b.aid = 3 AND b.value = 'jpg') Obviously instead of the * you may have to change to a list of field names to avoid fieldname collision on the two tables. Thanks, Ash http://www.ashleysheridan.co.uk I think maybe your table structure could do with a little work, as it doesn't lend itself to simple queries. It could probably be done the way you need with sub-queries, but as the tables become more populated and more people are triggering the queries, this is going to become very slow. Thanks, Ash http://www.ashleysheridan.co.uk
[PHP] SQL Syntax
Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sat, Feb 13, 2010 at 3:46 PM, Joseph Thayne webad...@thaynefam.org wrote: In order to make this as sql server independent as possible, the first thing you need to do is not use extended inserts as that is a MySQL capability. If you are insistent on using the extended inserts, then look at the mysql_info() function. That will return the number of rows inserted, etc. on the last query. But as previous posters had pointed out (thanks) i can't see which rows failed. As i'm dealing with 3rd-party data, that's an issue. I also didn't know it was mysql-specific, that multi-insert.. And i tried looking up the sql-standard docs, only to find that they cost over 200 euro per part (14 parts). I've sent angry emails to ansi.org and iso.org (commercial lamers operating under .org, yuck), about how cool a business model that charges a percentage of profits per implementation would be, instead of charging high prices up-front for a potentially bad/complicated piece of spec. But back to the problem at hand; it looks like i'll have to forget about using 100s of threads for my newsscraper at the same time, and settle for a few dozen instead. Then i can just do single inserts (per hit) and retrieve the last_insert_id(). One question remains: it is probably not (concurrently-)safe to do a sql-insert from php and then a last_insert_id() also from php..? I still have to build a stored procedure to do-the-inserting and return the last_insert_id()? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sunday 14 February 2010 03:15:16 am Rene Veerman wrote: On Sat, Feb 13, 2010 at 3:46 PM, Joseph Thayne webad...@thaynefam.org wrote: In order to make this as sql server independent as possible, the first thing you need to do is not use extended inserts as that is a MySQL capability. If you are insistent on using the extended inserts, then look at the mysql_info() function. That will return the number of rows inserted, etc. on the last query. But as previous posters had pointed out (thanks) i can't see which rows failed. As i'm dealing with 3rd-party data, that's an issue. I also didn't know it was mysql-specific, that multi-insert.. And i tried looking up the sql-standard docs, only to find that they cost over 200 euro per part (14 parts). I've sent angry emails to ansi.org and iso.org (commercial lamers operating under .org, yuck), about how cool a business model that charges a percentage of profits per implementation would be, instead of charging high prices up-front for a potentially bad/complicated piece of spec. But back to the problem at hand; it looks like i'll have to forget about using 100s of threads for my newsscraper at the same time, and settle for a few dozen instead. Then i can just do single inserts (per hit) and retrieve the last_insert_id(). One question remains: it is probably not (concurrently-)safe to do a sql-insert from php and then a last_insert_id() also from php..? I still have to build a stored procedure to do-the-inserting and return the last_insert_id()? That's perfectly safe to do as long as it's within the same PHP request. (Well, the same DB connection, really, which is 99% of the time the same thing.) last_insert_id() is connection-specific. I believe (it's been a while since I checked) the MySQL documentation says that last_insert_id() with a multi-insert statement is not reliable and you shouldn't rely on it having a worthwhile meaning anyway. Or at least it said something that made me conclude that it's safest to assume it's unreliable for a multi-insert statement. If you're concerned about performance of that many bulk writes, there's 3 things you can do to help: 1) Use InnoDB. It uses row-level locking so lots of writes doesn't lock your whole table as in MyISAM tables. 2) Disable indexes on the table in question before running your bulk insert, then re-enable them. That's considerably faster than rebuilding the index after each and every insert as they only need to be rebuilt once. 3) If you're on InnoDB, using transactions can sometimes give you a performance boost because the writes hit disk all at once when you commit. There may be other side effects and trade offs here, though, so take with a grain of salt. --Larry Garfield -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sat, Feb 13, 2010 at 7:41 PM, Jochem Maas joc...@iamjochem.com wrote:
Op 2/13/10 11:36 AM, Eric Lee schreef:
On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas joc...@iamjochem.com
mailto:joc...@iamjochem.com wrote:
Op 2/13/10 10:08 AM, Lester Caine schreef:
Rene Veerman wrote:
Hi.
I'm looking for the most efficient way to insert several records
and
retrieve the auto_increment values for the inserted rows, while
avoiding crippling concurrency problems caused by multiple php
threads
doing this on the same table at potentially the same time.
Any clues are greatly appreciated..
I'm looking for the most sql server independent way to do this.
Rene
The 'correct' way of doing this is to use a 'sequence' which is
something introduced in newer versions of the SQL standard.
Firebird(Interbase) has had 'generators' since the early days (20+
years) and these provide a unique number which can then be
inserted into
the table.
ADOdb emulates sequences in MySQL by creating a separate table for
the
insert value, so you can get the next value and work with it,
without
any worries. The only 'problem' is in situations were an insert is
rolled back, a number is lost, but that is ACTUALLY the correct
result,
since there is no way of knowing that a previous insert WILL
commit when
several people are adding records in parallel.
this is all true and correct ...
but that doesn't answer the problem. how do you get the IDs of all
the records
that we're actually inserted in a multi-insert statement, even if
you generate the
IDs beforehand you have to check them to see if any one of the set
INSERT VALUEs failed.
@Rene:
I don't think there is a really simple way of doing this in a RDBMS
agnostic
way, each RDBMS has it's own implementation - although many are
alike ... and MySQL is
pretty much the odd one out in that respect.
it might require a reevaluation of the problem, to either determine
that inserting
several records at once is not actually important in terms of
performance (this would depend
on how critical the speed is to you and exactly how many records
you're likely to be inserting
in a given run) and whether you can rework the logic to do away with
the requirement to
get at the id's of the newly inserted records ... possibly by
indentifying a unique
indentifier in the data that you already have.
one way to get round the issue might be to use a generated GUID and
have an extra field which
you populate with that value for all records inserted with a single
query, as such it could
function as kind of transaction indentifier which you could use to
retrieve the newly
inserted id's with one extra query:
$sql = SELECT id FROM foo WHERE insert_id = '{$insertGUID}';
... just an idea.
Hi
I would like to learn more correct way from both of you.
May I ask what is a sequences ?
it an RDBMS feature that offers a race-condition free method of
retrieving a new unique identifier for a record you wish to enter,
the firebird RDBMS that Lester mentions refers to this as 'generators'.
to learn more I would suggest STW:
http://lmgtfy.com/?q=sql+sequence
Jochem
Thanks,
Regards,
Eric
Thanks !
Regards,
Eric
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sat, Feb 13, 2010 at 2:07 PM, Rene Veerman rene7...@gmail.com wrote:
Hi.
I'm looking for the most efficient way to insert several records and
retrieve the auto_increment values for the inserted rows, while
avoiding crippling concurrency problems caused by multiple php threads
doing this on the same table at potentially the same time.
I'm using mysql atm, so i thought stored procedures!..
But alas, mysql docs are very basic.
I got the gist of how to setup a stored proc, but how to retrieve a
list of auto_increment ids still eludes me; last_insert_id() only
returns for the last row i believe.
So building an INSERT (...) VALUES (...),(...) at the php end, is
probably not the way to go then.
But the mysql docs don't show how to pass an array to a stored
procedure, so i can't just have the stored proc loop over an array,
insert per row, retrieve last_insert_id() into temp table, and return
the temp table contents for a list of auto_increment ids for inserted
rows.
Any clues are greatly appreciated..
I'm looking for the most sql server independent way to do this.
Rene
I have not been worked with mysql multi-insert before.
But just did a simple test on my mysql 5.0 copy.
I assume that you are using MyISAM table and will lock its read, writel
when inserting data.
When multi-insert was done, and did a select last_insert_id(). I saw that
only
the first inserted id was returned. Please take a look the following steps:
mysql select * from temp;
Empty set (0.00 sec)
mysql insert into temp (firstname, price) values ('dd', 10), ('cc', 3),
('bb', 99);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select last_insert_id();
+--+
| last_insert_id() |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql insert into temp (firstname, price) values ('dd', 10), ('cc', 3),
('bb', 99);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select last_insert_id();
+--+
| last_insert_id() |
+--+
|4 |
+--+
1 row in set (0.00 sec)
So, let's say three records was inserted, and the first inserted id was 1.
You get id from 1 to 3.
! This will not work on transaction-based insert !
Just a thought and tested on mysql but not on php.
Regards,
Eric
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
Rene Veerman wrote: Hi. I'm looking for the most efficient way to insert several records and retrieve the auto_increment values for the inserted rows, while avoiding crippling concurrency problems caused by multiple php threads doing this on the same table at potentially the same time. Any clues are greatly appreciated.. I'm looking for the most sql server independent way to do this. Rene The 'correct' way of doing this is to use a 'sequence' which is something introduced in newer versions of the SQL standard. Firebird(Interbase) has had 'generators' since the early days (20+ years) and these provide a unique number which can then be inserted into the table. ADOdb emulates sequences in MySQL by creating a separate table for the insert value, so you can get the next value and work with it, without any worries. The only 'problem' is in situations were an insert is rolled back, a number is lost, but that is ACTUALLY the correct result, since there is no way of knowing that a previous insert WILL commit when several people are adding records in parallel. -- 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// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
Op 2/13/10 10:08 AM, Lester Caine schreef:
Rene Veerman wrote:
Hi.
I'm looking for the most efficient way to insert several records and
retrieve the auto_increment values for the inserted rows, while
avoiding crippling concurrency problems caused by multiple php threads
doing this on the same table at potentially the same time.
Any clues are greatly appreciated..
I'm looking for the most sql server independent way to do this.
Rene
The 'correct' way of doing this is to use a 'sequence' which is
something introduced in newer versions of the SQL standard.
Firebird(Interbase) has had 'generators' since the early days (20+
years) and these provide a unique number which can then be inserted into
the table.
ADOdb emulates sequences in MySQL by creating a separate table for the
insert value, so you can get the next value and work with it, without
any worries. The only 'problem' is in situations were an insert is
rolled back, a number is lost, but that is ACTUALLY the correct result,
since there is no way of knowing that a previous insert WILL commit when
several people are adding records in parallel.
this is all true and correct ...
but that doesn't answer the problem. how do you get the IDs of all the records
that we're actually inserted in a multi-insert statement, even if you generate
the
IDs beforehand you have to check them to see if any one of the set INSERT
VALUEs failed.
@Rene:
I don't think there is a really simple way of doing this in a RDBMS agnostic
way, each RDBMS has it's own implementation - although many are alike ... and
MySQL is
pretty much the odd one out in that respect.
it might require a reevaluation of the problem, to either determine that
inserting
several records at once is not actually important in terms of performance (this
would depend
on how critical the speed is to you and exactly how many records you're likely
to be inserting
in a given run) and whether you can rework the logic to do away with the
requirement to
get at the id's of the newly inserted records ... possibly by indentifying a
unique
indentifier in the data that you already have.
one way to get round the issue might be to use a generated GUID and have an
extra field which
you populate with that value for all records inserted with a single query, as
such it could
function as kind of transaction indentifier which you could use to retrieve the
newly
inserted id's with one extra query:
$sql = SELECT id FROM foo WHERE insert_id = '{$insertGUID}';
... just an idea.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas joc...@iamjochem.com wrote:
Op 2/13/10 10:08 AM, Lester Caine schreef:
Rene Veerman wrote:
Hi.
I'm looking for the most efficient way to insert several records and
retrieve the auto_increment values for the inserted rows, while
avoiding crippling concurrency problems caused by multiple php threads
doing this on the same table at potentially the same time.
Any clues are greatly appreciated..
I'm looking for the most sql server independent way to do this.
Rene
The 'correct' way of doing this is to use a 'sequence' which is
something introduced in newer versions of the SQL standard.
Firebird(Interbase) has had 'generators' since the early days (20+
years) and these provide a unique number which can then be inserted into
the table.
ADOdb emulates sequences in MySQL by creating a separate table for the
insert value, so you can get the next value and work with it, without
any worries. The only 'problem' is in situations were an insert is
rolled back, a number is lost, but that is ACTUALLY the correct result,
since there is no way of knowing that a previous insert WILL commit when
several people are adding records in parallel.
this is all true and correct ...
but that doesn't answer the problem. how do you get the IDs of all the
records
that we're actually inserted in a multi-insert statement, even if you
generate the
IDs beforehand you have to check them to see if any one of the set INSERT
VALUEs failed.
@Rene:
I don't think there is a really simple way of doing this in a RDBMS
agnostic
way, each RDBMS has it's own implementation - although many are alike ...
and MySQL is
pretty much the odd one out in that respect.
it might require a reevaluation of the problem, to either determine that
inserting
several records at once is not actually important in terms of performance
(this would depend
on how critical the speed is to you and exactly how many records you're
likely to be inserting
in a given run) and whether you can rework the logic to do away with the
requirement to
get at the id's of the newly inserted records ... possibly by indentifying
a unique
indentifier in the data that you already have.
one way to get round the issue might be to use a generated GUID and have an
extra field which
you populate with that value for all records inserted with a single query,
as such it could
function as kind of transaction indentifier which you could use to retrieve
the newly
inserted id's with one extra query:
$sql = SELECT id FROM foo WHERE insert_id = '{$insertGUID}';
... just an idea.
Hi
I would like to learn more correct way from both of you.
May I ask what is a sequences ?
Thanks !
Regards,
Eric
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
Op 2/13/10 11:36 AM, Eric Lee schreef:
On Sat, Feb 13, 2010 at 6:55 PM, Jochem Maas joc...@iamjochem.com
mailto:joc...@iamjochem.com wrote:
Op 2/13/10 10:08 AM, Lester Caine schreef:
Rene Veerman wrote:
Hi.
I'm looking for the most efficient way to insert several records and
retrieve the auto_increment values for the inserted rows, while
avoiding crippling concurrency problems caused by multiple php
threads
doing this on the same table at potentially the same time.
Any clues are greatly appreciated..
I'm looking for the most sql server independent way to do this.
Rene
The 'correct' way of doing this is to use a 'sequence' which is
something introduced in newer versions of the SQL standard.
Firebird(Interbase) has had 'generators' since the early days (20+
years) and these provide a unique number which can then be
inserted into
the table.
ADOdb emulates sequences in MySQL by creating a separate table for the
insert value, so you can get the next value and work with it, without
any worries. The only 'problem' is in situations were an insert is
rolled back, a number is lost, but that is ACTUALLY the correct
result,
since there is no way of knowing that a previous insert WILL
commit when
several people are adding records in parallel.
this is all true and correct ...
but that doesn't answer the problem. how do you get the IDs of all
the records
that we're actually inserted in a multi-insert statement, even if
you generate the
IDs beforehand you have to check them to see if any one of the set
INSERT VALUEs failed.
@Rene:
I don't think there is a really simple way of doing this in a RDBMS
agnostic
way, each RDBMS has it's own implementation - although many are
alike ... and MySQL is
pretty much the odd one out in that respect.
it might require a reevaluation of the problem, to either determine
that inserting
several records at once is not actually important in terms of
performance (this would depend
on how critical the speed is to you and exactly how many records
you're likely to be inserting
in a given run) and whether you can rework the logic to do away with
the requirement to
get at the id's of the newly inserted records ... possibly by
indentifying a unique
indentifier in the data that you already have.
one way to get round the issue might be to use a generated GUID and
have an extra field which
you populate with that value for all records inserted with a single
query, as such it could
function as kind of transaction indentifier which you could use to
retrieve the newly
inserted id's with one extra query:
$sql = SELECT id FROM foo WHERE insert_id = '{$insertGUID}';
... just an idea.
Hi
I would like to learn more correct way from both of you.
May I ask what is a sequences ?
it an RDBMS feature that offers a race-condition free method of
retrieving a new unique identifier for a record you wish to enter,
the firebird RDBMS that Lester mentions refers to this as 'generators'.
to learn more I would suggest STW:
http://lmgtfy.com/?q=sql+sequence
Thanks !
Regards,
Eric
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
At 7:07 AM +0100 2/13/10, Rene Veerman wrote: Hi. I'm looking for the most efficient way to insert several records and retrieve the auto_increment values for the inserted rows, while avoiding crippling concurrency problems caused by multiple php threads doing this on the same table at potentially the same time. -snip- Any clues are greatly appreciated.. I'm looking for the most sql server independent way to do this. Rene: I'm not sure what would be the most efficient way to solve the race problems presented here, but you might want to not confront the race problem at all and solve this a bit more straight forward -- for example: Three steps for each record: 1. Generate a unique value (i.e., date/time). 2. Insert the record with the unique value in a field and the auto_increment ID will be automatically created. 3. Then search for the record with that unique value and retrieve the auto_incremented ID value. While this might take a few more cycles, it would work. If you want your auto_increment ID's to be in sequence, then that's a different problem and if so, maybe you should rethink the problem. I've never seen a problem where the ID's were required to be anything other than unique. Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
In order to make this as sql server independent as possible, the first thing you need to do is not use extended inserts as that is a MySQL capability. If you are insistent on using the extended inserts, then look at the mysql_info() function. That will return the number of rows inserted, etc. on the last query. -Original Message- From: Rene Veerman [mailto:rene7...@gmail.com] Sent: Saturday, February 13, 2010 12:08 AM To: php-general Subject: [PHP] SQL insert () values (),(),(); how to get auto_increments properly? Hi. I'm looking for the most efficient way to insert several records and retrieve the auto_increment values for the inserted rows, while avoiding crippling concurrency problems caused by multiple php threads doing this on the same table at potentially the same time. I'm using mysql atm, so i thought stored procedures!.. But alas, mysql docs are very basic. I got the gist of how to setup a stored proc, but how to retrieve a list of auto_increment ids still eludes me; last_insert_id() only returns for the last row i believe. So building an INSERT (...) VALUES (...),(...) at the php end, is probably not the way to go then. But the mysql docs don't show how to pass an array to a stored procedure, so i can't just have the stored proc loop over an array, insert per row, retrieve last_insert_id() into temp table, and return the temp table contents for a list of auto_increment ids for inserted rows. Any clues are greatly appreciated.. I'm looking for the most sql server independent way to do this. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
On Sat, 2010-02-13 at 08:46 -0600, Joseph Thayne wrote: In order to make this as sql server independent as possible, the first thing you need to do is not use extended inserts as that is a MySQL capability. If you are insistent on using the extended inserts, then look at the mysql_info() function. That will return the number of rows inserted, etc. on the last query. -Original Message- From: Rene Veerman [mailto:rene7...@gmail.com] Sent: Saturday, February 13, 2010 12:08 AM To: php-general Subject: [PHP] SQL insert () values (),(),(); how to get auto_increments properly? Hi. I'm looking for the most efficient way to insert several records and retrieve the auto_increment values for the inserted rows, while avoiding crippling concurrency problems caused by multiple php threads doing this on the same table at potentially the same time. I'm using mysql atm, so i thought stored procedures!.. But alas, mysql docs are very basic. I got the gist of how to setup a stored proc, but how to retrieve a list of auto_increment ids still eludes me; last_insert_id() only returns for the last row i believe. So building an INSERT (...) VALUES (...),(...) at the php end, is probably not the way to go then. But the mysql docs don't show how to pass an array to a stored procedure, so i can't just have the stored proc loop over an array, insert per row, retrieve last_insert_id() into temp table, and return the temp table contents for a list of auto_increment ids for inserted rows. Any clues are greatly appreciated.. I'm looking for the most sql server independent way to do this. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php But getting the number of rows isn't really all that useful, as it won't tell you what the auto increment id values are, and if any inserts fail, it won't tell you which ones. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] SQL insert () values (),(),(); how to get auto_increments properly?
Ashley Sheridan wrote: But getting the number of rows isn't really all that useful, as it won't tell you what the auto increment id values are, and if any inserts fail, it won't tell you which ones. Which is one of the reasons that MySQL still has problems with consistency ;) Auto-increment only has limited use, you need to have a mechanism outside of the transaction to manage the values, and handle those insertions on a one by one basis. A transaction can only ALL be rolled back or committed. If some part fails, then the whole should fail . If you need to detect failures, they need to be done one at a time. -- 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// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL insert () values (),(),(); how to get auto_increments properly?
Hi. I'm looking for the most efficient way to insert several records and retrieve the auto_increment values for the inserted rows, while avoiding crippling concurrency problems caused by multiple php threads doing this on the same table at potentially the same time. I'm using mysql atm, so i thought stored procedures!.. But alas, mysql docs are very basic. I got the gist of how to setup a stored proc, but how to retrieve a list of auto_increment ids still eludes me; last_insert_id() only returns for the last row i believe. So building an INSERT (...) VALUES (...),(...) at the php end, is probably not the way to go then. But the mysql docs don't show how to pass an array to a stored procedure, so i can't just have the stored proc loop over an array, insert per row, retrieve last_insert_id() into temp table, and return the temp table contents for a list of auto_increment ids for inserted rows. Any clues are greatly appreciated.. I'm looking for the most sql server independent way to do this. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
Kim Madsen wrote: But Skip, as the others say, use a date class, since you're passing a php var on to the SQL anyway, then you could determine the exact days from start to end of donation. Combine this with to_days and you have your solution Yes, this sounds like the best way to go. Thanks everyone! Skip -- Skip Evans PenguinSites.com, LLC 503 S Baldwin St, #1 Madison WI 53703 608.250.2720 http://penguinsites.com Those of you who believe in telekinesis, raise my hand. -- Kurt Vonnegut -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
At 9:54 PM -0600 1/25/10, Skip Evans wrote:
Hey all,
I have an SQL query that's stumping me.
I have two date variables, $start and $end that are in mm/dd/
format and two database fields, start_date and no_donations. The
start date is mm/dd/ format and no_donations is an integer that
represents the number of months from start_date that donations will
be made.
So if start date is say 02/01/2010 and no_dations is 4 then
donations will be made four times from the start date for four
months.
What I need to do is come up with a query that will determine if the
start_date + no_donations falls within $start and $end.
But I'm pretty stumped. How can I convert start_date + no_donations
in the database to the date when the last donation will take place
so I'll now if the donations fall between $start and $end?
Any suggestions would be very help and appreciated,
Skip
--
Skip Evans
Skip:
Here's a snip-it of code from one of my projects:
$qry = SELECT SUM(amount) AS subtotal, COUNT(*) AS num
FROM transaction
WHERE product_type = 'video'
AND UNIX_TIMESTAMP(transtime) . strtotime($startd) .
AND UNIX_TIMESTAMP(transtime) . strtotime($endd) .
AND is_charged = 1
AND notes = 'Approved'
AND is_refunded = 0
AND transnum NOT LIKE 'TEST-PNREF'
AND product_id LIKE '$key' ;
$db2-select($qry);
while ($db2-readrow())
{
$rev = $db2-data[subtotal]; // this is the total amount
collected for the sale
$num = $db2-data[num]; // this is the number
of this type of sale
}
Clearly, you don't need everything there, but the timestamp notation
will give you better insight into how to use dates in your query.
Cheers,
tedd
--
---
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
Michael A. Peters wrote on 26/01/2010 06:04: I use seconds from epoch in the database simply because it works so well with the php date() function. If you need something where Julian day really is better, I assume it isn't that hard to convert between posix and julian day, though it seems odd to me that it isn't part of the date() function. It probably should be. When I do date comparisons in MySQL I use the to_days() function. What I need to do is come up with a query that will determine if the start_date + no_donations falls within $start and $end. In the given example one could determine that a month is always 30 days and then say to_days(start_date)+(no_donations*30) to_days(end). This would however be a very loose method. You could go for finding the number of days in the current month and substract that (10th. = 30-10), play with MySQLs left() function But Skip, as the others say, use a date class, since you're passing a php var on to the SQL anyway, then you could determine the exact days from start to end of donation. Combine this with to_days and you have your solution -- Kind regards Kim Emax - masterminds.dk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL question
Hey all, I have an SQL query that's stumping me. I have two date variables, $start and $end that are in mm/dd/ format and two database fields, start_date and no_donations. The start date is mm/dd/ format and no_donations is an integer that represents the number of months from start_date that donations will be made. So if start date is say 02/01/2010 and no_dations is 4 then donations will be made four times from the start date for four months. What I need to do is come up with a query that will determine if the start_date + no_donations falls within $start and $end. But I'm pretty stumped. How can I convert start_date + no_donations in the database to the date when the last donation will take place so I'll now if the donations fall between $start and $end? Any suggestions would be very help and appreciated, Skip -- Skip Evans PenguinSites.com, LLC 503 S Baldwin St, #1 Madison WI 53703 608.250.2720 http://penguinsites.com Those of you who believe in telekinesis, raise my hand. -- Kurt Vonnegut -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
On Mon, Jan 25, 2010 at 09:54:40PM -0600, Skip Evans wrote: Hey all, I have an SQL query that's stumping me. I have two date variables, $start and $end that are in mm/dd/ format and two database fields, start_date and no_donations. The start date is mm/dd/ format and no_donations is an integer that represents the number of months from start_date that donations will be made. So if start date is say 02/01/2010 and no_dations is 4 then donations will be made four times from the start date for four months. What I need to do is come up with a query that will determine if the start_date + no_donations falls within $start and $end. But I'm pretty stumped. How can I convert start_date + no_donations in the database to the date when the last donation will take place so I'll now if the donations fall between $start and $end? Any suggestions would be very help and appreciated, If there's a way to do this in SQL itself, I don't know what it is. But in my opinion, you need a date class which can do date comparisons. (If you end up programming one yourself, save yourself some time and convert all dates to Julian day numbers internally. This saves massive amounts of computation in determining intervals and durations. Typically, coders try to store dates in unix timestamps internally, and then add 86400 seconds for every day to calculate intervals and such. This is often inaccurate. Julian days are far more accurate.) Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
Paul M Foster wrote: Typically, coders try to store dates in unix timestamps internally, and then add 86400 seconds for every day to calculate intervals and such. This is often inaccurate. Julian days are far more accurate.) Paul I use seconds from epoch in the database simply because it works so well with the php date() function. If you need something where Julian day really is better, I assume it isn't that hard to convert between posix and julian day, though it seems odd to me that it isn't part of the date() function. It probably should be. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL question
Michael A. Peters wrote: If you need something where Julian day really is better, I assume it isn't that hard to convert between posix and julian day, though it seems odd to me that it isn't part of the date() function. It probably should be. Looks like unixtojd() and jdtounix() do it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Queries
You should be able to do this from within the query. Try the following query: DELETE users.* FROM users LEFT JOIN notes USING(user_id) WHERE notes.note_id IS NULL Take care, Floyd On Dec 20, 2009, at 4:30 PM, דניאל דנון wrote: Hey, Lets assume I got a table named users. It contains id name. I have another table called notes - which contains id, user_id, contents I want to delete all users from table users that don't have notes (SELECT ... FROM notes WHERE user_id=ID) returns empty result. What is the fastest way to do it? -- Use ROT26 for best security -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL Queries
Hey, Lets assume I got a table named users. It contains id name. I have another table called notes - which contains id, user_id, contents I want to delete all users from table users that don't have notes (SELECT ... FROM notes WHERE user_id=ID) returns empty result. What is the fastest way to do it? -- Use ROT26 for best security
Re: [PHP] SQL help?
Skip Evans wrote: Hey all, I have a SQL requirement I'm not quite sure how to compose. I have two tables, shows, and shows_dates. It's a one to many relationship where there is a single entry in shows and multiple entries in shows_dates that list each date and time for a play production for a run of entries in shows, like I need a query that will read each record in shows, but I only want the first record from shows_dates, the first one sorted by date, so I can display all shows in order of their opening date. Not sure how to grab just the first record from shows_dates though. Hint, anyone? Thanks, Skip Join the two tables like you normally would do and aggregate the opening date column with your dbms-specific max function and finally group the result by a distinct value from shows. It would have bee easier if you stated which rdbms you use ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL help?
Hey all, I have a SQL requirement I'm not quite sure how to compose. I have two tables, shows, and shows_dates. It's a one to many relationship where there is a single entry in shows and multiple entries in shows_dates that list each date and time for a play production for a run of entries in shows, like I need a query that will read each record in shows, but I only want the first record from shows_dates, the first one sorted by date, so I can display all shows in order of their opening date. Not sure how to grab just the first record from shows_dates though. Hint, anyone? Thanks, Skip -- Skip Evans Big Sky Penguin, LLC 503 S Baldwin St, #1 Madison WI 53703 608.250.2720 http://bigskypenguin.com Those of you who believe in telekinesis, raise my hand. -- Kurt Vonnegut -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL Injection - Solution
Hi folks,
Someone know how i can improve this function to protect my envairounment
vars of sql injection attacks.
that is the function i use to do this, but, some people think is not enough:
* @uses $_REQUEST= _antiSqlInjection($_REQUEST);
* @uses $_POST = _antiSqlInjection($_POST);
* @uses $_GET = _antiSqlInjection($_GET);
*
* @author Igor Escobar
* @email blog [at] igorescobar [dot] com
*
*/
function _antiSqlInjection($Target){
$sanitizeRules =
array('OR','FROM,'SELECT','INSERT','DELETE','WHERE','DROP TABLE','SHOW
TABLES','*','--','=');
foreach($Target as $key = $value):
if(is_array($value)): $arraSanitized[$key] =
_antiSqlInjection($value);
else:
$arraSanitized[$key] =
addslashes(strip_tags(trim(str_replace($sanitizeRules,,$value;
endif;
endforeach;
return $arraSanitized;
}
You can help me to improve them?
Regards,
Igor Escobar
Systems Analyst Interface Designer
--
Personal Blog
~ blog.igorescobar.com
Online Portifolio
~ www.igorescobar.com
Twitter
~ @igorescobar
Re: [PHP] SQL Injection - Solution
Hi there!
2009/5/6 Igor Escobar titiolin...@gmail.com
Hi folks,
Someone know how i can improve this function to protect my envairounment
vars of sql injection attacks.
that is the function i use to do this, but, some people think is not enough:
* @uses $_REQUEST= _antiSqlInjection($_REQUEST);
* @uses $_POST = _antiSqlInjection($_POST);
* @uses $_GET = _antiSqlInjection($_GET);
*
* @author Igor Escobar
* @email blog [at] igorescobar [dot] com
*
*/
function _antiSqlInjection($Target){
$sanitizeRules =
array('OR','FROM,'SELECT','INSERT','DELETE','WHERE','DROP TABLE','SHOW
TABLES','*','--','=');
foreach($Target as $key = $value):
if(is_array($value)): $arraSanitized[$key] =
_antiSqlInjection($value);
else:
$arraSanitized[$key] =
addslashes(strip_tags(trim(str_replace($sanitizeRules,,$value;
endif;
endforeach;
return $arraSanitized;
}
You can help me to improve them?
What if someone posts, in any form of your app, a message containing
or, from or where? Those are very common words, and eliminate
them is not the best solution, IMO.
Use mysql_real_escape_string() like Shawn said, possibly something
like this would do the trick (from
http://br2.php.net/manual/en/function.mysql-query.php):
$query = sprintf(SELECT firstname, lastname, address, age FROM
friends WHERE firstname='%s' AND lastname='%s',
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
Cheers,
Bruno.
Regards,
Igor Escobar
Systems Analyst Interface Designer
--
Personal Blog
~ blog.igorescobar.com
Online Portifolio
~ www.igorescobar.com
Twitter
~ @igorescobar
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection - Solution
On Wed, May 6, 2009 at 12:06 PM, Bruno Fajardo bsfaja...@gmail.com wrote:
Hi there!
2009/5/6 Igor Escobar titiolin...@gmail.com
Hi folks,
Someone know how i can improve this function to protect my envairounment
vars of sql injection attacks.
that is the function i use to do this, but, some people think is not enough:
* @uses $_REQUEST= _antiSqlInjection($_REQUEST);
* @uses $_POST = _antiSqlInjection($_POST);
* @uses $_GET = _antiSqlInjection($_GET);
*
* @author Igor Escobar
* @email blog [at] igorescobar [dot] com
*
*/
function _antiSqlInjection($Target){
$sanitizeRules =
array('OR','FROM,'SELECT','INSERT','DELETE','WHERE','DROP TABLE','SHOW
TABLES','*','--','=');
foreach($Target as $key = $value):
if(is_array($value)): $arraSanitized[$key] =
_antiSqlInjection($value);
else:
$arraSanitized[$key] =
addslashes(strip_tags(trim(str_replace($sanitizeRules,,$value;
endif;
endforeach;
return $arraSanitized;
}
You can help me to improve them?
What if someone posts, in any form of your app, a message containing
or, from or where? Those are very common words, and eliminate
them is not the best solution, IMO.
Use mysql_real_escape_string() like Shawn said, possibly something
like this would do the trick (from
http://br2.php.net/manual/en/function.mysql-query.php):
$query = sprintf(SELECT firstname, lastname, address, age FROM
friends WHERE firstname='%s' AND lastname='%s',
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
Cheers,
Bruno.
+1
I would stick with parameterized queries if available, or just use
mysql_real_escape_string() for these and a few more reasons:
1) You'll find lots of posts in the archives explaining why
mysql_real_escape_string() is preferred over addslashes() for this
purpose.
2) strip_tags has absolutely nothing to do with SQL injection. Neither
does trim(). There are cases where you would not want to use either of
those functions on input, but you would still need to guard against
injection.
3) DROP TABLE will work no matter how many white-space characters
appeared between the words. For that matter, I am pretty sure that
'DROP /* some bogus SQL comment to make it past your filter */ TABLE'
will work also.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Injection - Solution
I know that use the mysql_real_escape_string to do de job is better but you
should consider that the this function don't have any access to the data
base, to objective of this function is sanitize the string.
And please, see my second answer, i make some updates in the function that
possibly is relevant.
Regards,
Igor Escobar
Systems Analyst Interface Designer
--
Personal Blog
~ blog.igorescobar.com
Online Portifolio
~ www.igorescobar.com
Twitter
~ @igorescobar
On Wed, May 6, 2009 at 1:14 PM, Andrew Ballard aball...@gmail.com wrote:
On Wed, May 6, 2009 at 12:06 PM, Bruno Fajardo bsfaja...@gmail.com
wrote:
Hi there!
2009/5/6 Igor Escobar titiolin...@gmail.com
Hi folks,
Someone know how i can improve this function to protect my envairounment
vars of sql injection attacks.
that is the function i use to do this, but, some people think is not
enough:
* @uses $_REQUEST= _antiSqlInjection($_REQUEST);
* @uses $_POST = _antiSqlInjection($_POST);
* @uses $_GET = _antiSqlInjection($_GET);
*
* @author Igor Escobar
* @email blog [at] igorescobar [dot] com
*
*/
function _antiSqlInjection($Target){
$sanitizeRules =
array('OR','FROM,'SELECT','INSERT','DELETE','WHERE','DROP TABLE','SHOW
TABLES','*','--','=');
foreach($Target as $key = $value):
if(is_array($value)): $arraSanitized[$key] =
_antiSqlInjection($value);
else:
$arraSanitized[$key] =
addslashes(strip_tags(trim(str_replace($sanitizeRules,,$value;
endif;
endforeach;
return $arraSanitized;
}
You can help me to improve them?
What if someone posts, in any form of your app, a message containing
or, from or where? Those are very common words, and eliminate
them is not the best solution, IMO.
Use mysql_real_escape_string() like Shawn said, possibly something
like this would do the trick (from
http://br2.php.net/manual/en/function.mysql-query.php):
$query = sprintf(SELECT firstname, lastname, address, age FROM
friends WHERE firstname='%s' AND lastname='%s',
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
Cheers,
Bruno.
+1
I would stick with parameterized queries if available, or just use
mysql_real_escape_string() for these and a few more reasons:
1) You'll find lots of posts in the archives explaining why
mysql_real_escape_string() is preferred over addslashes() for this
purpose.
2) strip_tags has absolutely nothing to do with SQL injection. Neither
does trim(). There are cases where you would not want to use either of
those functions on input, but you would still need to guard against
injection.
3) DROP TABLE will work no matter how many white-space characters
appeared between the words. For that matter, I am pretty sure that
'DROP /* some bogus SQL comment to make it past your filter */ TABLE'
will work also.
Andrew
Re: [PHP] SQL Injection - Solution
mysql_escape_string can be used instead. You just lose the ability to
have it match coallation. I still think there should be the
mysql_escape_string or real one and allow it to pass the coallation
without a database handle -or- just make a unicode/utf8 one and be
done with it.
On May 6, 2009, at 9:40 AM, Igor Escobar titiolin...@gmail.com wrote:
I know that use the mysql_real_escape_string to do de job is better
but you
should consider that the this function don't have any access to the
data
base, to objective of this function is sanitize the string.
And please, see my second answer, i make some updates in the
function that
possibly is relevant.
Regards,
Igor Escobar
Systems Analyst Interface Designer
--
Personal Blog
~ blog.igorescobar.com
Online Portifolio
~ www.igorescobar.com
Twitter
~ @igorescobar
On Wed, May 6, 2009 at 1:14 PM, Andrew Ballard aball...@gmail.com
wrote:
On Wed, May 6, 2009 at 12:06 PM, Bruno Fajardo bsfaja...@gmail.com
wrote:
Hi there!
2009/5/6 Igor Escobar titiolin...@gmail.com
Hi folks,
Someone know how i can improve this function to protect my
envairounment
vars of sql injection attacks.
that is the function i use to do this, but, some people think is
not
enough:
* @uses $_REQUEST= _antiSqlInjection($_REQUEST);
* @uses $_POST = _antiSqlInjection($_POST);
* @uses $_GET = _antiSqlInjection($_GET);
*
* @author Igor Escobar
* @email blog [at] igorescobar [dot] com
*
*/
function _antiSqlInjection($Target){
$sanitizeRules =
array('OR','FROM,'SELECT','INSERT','DELETE','WHERE','DROP
TABLE','SHOW
TABLES','*','--','=');
foreach($Target as $key = $value):
if(is_array($value)): $arraSanitized[$key] =
_antiSqlInjection($value);
else:
$arraSanitized[$key] =
addslashes(strip_tags(trim(str_replace($sanitizeRules,,
$value;
endif;
endforeach;
return $arraSanitized;
}
You can help me to improve them?
What if someone posts, in any form of your app, a message containing
or, from or where? Those are very common words, and eliminate
them is not the best solution, IMO.
Use mysql_real_escape_string() like Shawn said, possibly something
like this would do the trick (from
http://br2.php.net/manual/en/function.mysql-query.php):
$query = sprintf(SELECT firstname, lastname, address, age FROM
friends WHERE firstname='%s' AND lastname='%s',
mysql_real_escape_string($firstname),
mysql_real_escape_string($lastname));
Cheers,
Bruno.
+1
I would stick with parameterized queries if available, or just use
mysql_real_escape_string() for these and a few more reasons:
1) You'll find lots of posts in the archives explaining why
mysql_real_escape_string() is preferred over addslashes() for this
purpose.
2) strip_tags has absolutely nothing to do with SQL injection.
Neither
does trim(). There are cases where you would not want to use either
of
those functions on input, but you would still need to guard against
injection.
3) DROP TABLE will work no matter how many white-space characters
appeared between the words. For that matter, I am pretty sure that
'DROP /* some bogus SQL comment to make it past your filter */ TABLE'
will work also.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL syntax?
Hi I am having problems (yep me again) with my sql, I have looked and tried
different things (ASC, DESC, etc) but it same error:
Here is the error:
You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'ORDER BY
StartDate DESC' at line 2 --and the actual line the code is on
is line 21 not 2 so that is weird...and I had a comma between DESC and the
field but nothing
Code:
$sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b.
%e, %Y %l:%i %p') AS Start_Date,
DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ;
$sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number,
Impressions AS Ad_Impressions, ;
$sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ;
$sql .= ORDER BY StartDate DESC;
could it be the AS, I copied that from another code--- I am trying to
make a report from the tableHere is my full script:
?php
include(../inc/dbconn_open.php);
if (empty($_SESSION['AdminLogin']) OR $_SESSION['AdminLogin'] 'OK' ){
header (Location: LogOut.php);
}
$query = SELECT WorkOrderID, Advertiser, AccountNum, Impressions,
AdSize, StartDate, EndDate, CPM, OnlineDate FROM workorderform;
$result = mysql_query ($query) or die(mysql_error());
$row = mysql_fetch_object ($result);
if ($row-UserReport == NO) {
header (Location: Welcome.php?AdminID=$AdminIDmsg=Sorry, you do
not have access to that page.);
}
$sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b.
%e, %Y %l:%i %p') AS Start_Date,
DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ;
$sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number,
Impressions AS Ad_Impressions, ;
$sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ;
$sql .= ORDER BY StartDate DESC;
$export = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_object ($result);
$fields = mysql_num_fields($export);
$header = ;
$value = ;
$data = ;
for ($i = 0; $i $fields; $i++) {
$header .= mysql_field_name($export, $i) . \t;
}
while($row2 = mysql_fetch_row($export)) {
$line = '';
foreach($row2 as $value)
{
if ((!isset($value)) OR ($value == )) {
$value = \t;
} else {
$value = str_replace('', '', $value);
$value = '' . $value . '' . \t;
}
$line .= $value;
}
$data .= trim($line).\n;
}
$data = str_replace(\r,,$data);
if ($data == ) {
$data = \n(0) Records Found!\n;
}
header(Content-type: application/x-msdownload);
header(Content-Disposition: attachment; filename=AdDates_Report.xls);
header(Pragma: no-cache);
header(Expires: 0);
print $header\n$data;
?
Re: [PHP] SQL syntax?
$sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL syntax?
On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
Try this: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate ; $sql.= FROM workorderform ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; I just removed extra commas from CPM_Rate and workorderform Terion Miller wrote: ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
Excellent Allan thanks so much, sometimes I think php is causing me blindness!! Terion On Fri, Dec 5, 2008 at 4:26 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Try this: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate ; $sql.= FROM workorderform ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; I just removed extra commas from CPM_Rate and workorderform Terion Miller wrote: ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error
Re: [PHP] SQL syntax?
On Fri, 2008-12-05 at 16:51 -0600, Terion Miller wrote: Excellent Allan thanks so much, sometimes I think php is causing me blindness!! Terion On Fri, Dec 5, 2008 at 4:26 PM, Allan Arguelles [EMAIL PROTECTED]wrote: Try this: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate ; $sql.= FROM workorderform ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; I just removed extra commas from CPM_Rate and workorderform Terion Miller wrote: ah...I also though it was because I didn't have a statement like where adsize = adsize or something but I tried that and got the same error I have been getting ... You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM workorderform, WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC' at line 2 and why does it keep saying line 2... here is the snippet as it is now: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql.= FROM workorderform, ; $sql .= WHERE WorkOrderID = WorkOrderID ORDER BY StartDate DESC; On Fri, Dec 5, 2008 at 4:14 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: Umm.. I meant you need to put $sql .= FROM workorderform ; between these: $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; :) Terion Miller wrote: On Fri, Dec 5, 2008 at 3:57 PM, Allan Arguelles [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED]wrote: $sql = SELECT WorkOrderID AS Work_Order_ID, DATE_FORMAT(StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= Advertiser AS Advertiser_Name,AccountNum AS Account_Number, Impressions AS Ad_Impressions, ; $sql .= AdSize AS Ad_Size, CPM AS CPM_Rate, ; $sql .= ORDER BY StartDate DESC; You forgot the tables, plus you have an extra comma after CPM_Rate. well I changed it to: $sql = SELECT workorderform.WorkOrderID AS Work_Order_ID, DATE_FORMAT(workorderform.StartDate, '%b. %e, %Y %l:%i %p') AS Start_Date, DATE_FORMAT(workorderform.EndDate, '%b. %e, %Y %l:%i %p') AS End_Date, ; $sql .= workorderform.Advertiser AS Advertiser_Name,workorderform.AccountNum AS Account_Number, workorderform.Impressions AS Ad_Impressions, ; $sql .= workorderform.AdSize AS Ad_Size, workorderform.CPM AS CPM_Rate ; $sql .= ORDER BY StartDate DESC; and got the same error If I run into troubles with SQL (specifically MySQL) I run the query in phpMyAdmin, which is so helpful. If you're using another SQL variant like that god-forsaken M$ SQL, then you have to use the appropriate tool to interface with the database there. It sure helps remove the SQL problems from PHP, which was your problem in this case. Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL - RANDOM
Hi, i would ask what is the best method for random select from database. I have table with 20k items.. - If i use ,,select * from table order by rand() limit 3, the query took 0.0524 sec ... its slightly enough - but if i generate rand id and then i use ,,select where id in(rand1, rand2, rand3), the query took only 0.0005 sec, but there is a problem that id doesnt exist thanks for responses -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL - RANDOM
Korgan wrote: Hi, i would ask what is the best method for random select from database. I have table with 20k items.. - If i use ,,select * from table order by rand() limit 3, the query took 0.0524 sec ... its slightly enough and as your table grows in size, it gets slower and slower. Try this: http://edrackham.com/featured/get-random-row-with-mysql-without-order-by-rand/ There are other suggestions out there too, search for mysql don't use random(). -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL - RANDOM
For large tables, I generally create a static_rand column, and pre-populated
it with random numbers and create an index on it.
Then, after using up the records, I have application logic to reset those
records (and only those records) to new random numbers.
This provides MUCH better performance than using rand() on huge tables.
alter table whatever add static_rand float;
create index rand_index on whatever(static_rand);
select id from whatever order by static_rand limit 3;
-- PHP code to create an array of the 3 IDs you got:
$ids_sql = implode(',', $ids);
$query = update whatever set static = rand() where id in ($ids_sql);
It's a bit crude in that you have to remember to do the UPDATE, but quite fast,
and not THAT tricky to modularize your code to always do the UPDATE.
Or not, actually, if it's okay to cache the random answer for a short period
of time. Then you can just have a cron job that does:
update whatever set static_rand = rand() order by static_rand limit 3;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL - RANDOM
thx it looks good
[EMAIL PROTECTED] napsal(a):
For large tables, I generally create a static_rand column, and pre-populated
it with random numbers and create an index on it.
Then, after using up the records, I have application logic to reset those
records (and only those records) to new random numbers.
This provides MUCH better performance than using rand() on huge tables.
alter table whatever add static_rand float;
create index rand_index on whatever(static_rand);
select id from whatever order by static_rand limit 3;
-- PHP code to create an array of the 3 IDs you got:
$ids_sql = implode(',', $ids);
$query = update whatever set static = rand() where id in ($ids_sql);
It's a bit crude in that you have to remember to do the UPDATE, but quite fast,
and not THAT tricky to modularize your code to always do the UPDATE.
Or not, actually, if it's okay to cache the random answer for a short period
of time. Then you can just have a cron job that does:
update whatever set static_rand = rand() order by static_rand limit 3;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL outer join problem
I've tried using the same php.ini file and phpinfo() shows the same info for both servers. I think I've narrowed the problem down a little bit though. After looking into the Driver Manager's log and I found that SQLExtendedFetch is being called on server 2 (incomplete result) and SQLFetch on server 1 (correct result). Can someone please tell me when PHP calls SQLExtendedFetch in stead of SQLFetch? How can I configure PHP to use SQLFetch instead? Thanks! On Tue, Sep 16, 2008 at 4:25 PM, Micah Gersten [EMAIL PROTECTED] wrote: Compare phpinfo() on both servers. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Liz Kim wrote: have a very simple script which connects to a database, execute a query then displays the result. This script uses odbc_connect and functions. There are two identical Apache web servers and one MS SQL database server. When the script is ran on web server #1, all the results are returned correctly. Ran on server #2, however, some entries are missing. Same script, different result.. I am using left outer join in the SQL query and the missing items are the ones that should be canceled out from this join. I directly ran the query on the database and made sure it is not causing and problems. I checked the odbc setting on php.ini and odbc.ini on both servers. No difference. No permission problems either as I am logging in as the same user... Any ideas??? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL outer join problem
Liz Kim wrote: I've tried using the same php.ini file and phpinfo() shows the same info for both servers. I think I've narrowed the problem down a little bit though. After looking into the Driver Manager's log and I found that SQLExtendedFetch is being called on server 2 (incomplete result) and SQLFetch on server 1 (correct result). Can someone please tell me when PHP calls SQLExtendedFetch in stead of SQLFetch? How can I configure PHP to use SQLFetch instead? Seems to be something to do with your odbc settings. Are they the same on both servers? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL outer join problem
have a very simple script which connects to a database, execute a query then displays the result. This script uses odbc_connect and functions. There are two identical Apache web servers and one MS SQL database server. When the script is ran on web server #1, all the results are returned correctly. Ran on server #2, however, some entries are missing. Same script, different result.. I am using left outer join in the SQL query and the missing items are the ones that should be canceled out from this join. I directly ran the query on the database and made sure it is not causing and problems. I checked the odbc setting on php.ini and odbc.ini on both servers. No difference. No permission problems either as I am logging in as the same user... Any ideas???
Re: [PHP] SQL outer join problem
Compare phpinfo() on both servers. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Liz Kim wrote: have a very simple script which connects to a database, execute a query then displays the result. This script uses odbc_connect and functions. There are two identical Apache web servers and one MS SQL database server. When the script is ran on web server #1, all the results are returned correctly. Ran on server #2, however, some entries are missing. Same script, different result.. I am using left outer join in the SQL query and the missing items are the ones that should be canceled out from this join. I directly ran the query on the database and made sure it is not causing and problems. I checked the odbc setting on php.ini and odbc.ini on both servers. No difference. No permission problems either as I am logging in as the same user... Any ideas??? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] sql injection
i have implemented a way to avoid sql injection from the php website from
this url
http://in.php.net/mysql_real_escape_string from the Example #3 A Best
Practice query section of this page
following are the steps i have followed after the form values are submitted
to a php file.
step 1.
if(get_magic_quotes_gpc())
{
$username = stripslashes($_POST[username]);
.
}
else
{
$username = $_POST[username];
.
}
step 2.
$conn = mysql_connect($hostname, $user, $password);
step 3.
$insertquery = sprintf(INSERT INTO table (`username`, ...) VALUES ('%s',
...), mysql_real_escape_string($username, $conn), ...);
step 4.
if(!$conn)
{
header(Location: http://website/dberror.html;);
exit;
}
else
{
mysql_select_db($database, $conn);
$insertqueryresult = mysql_query($insertquery);
if(!$insertqueryresult) {
header(Location: http://website/error.html;);
exit; }
}
with the above method i am able to insert values into the table even with if
i enter the ' special character which can cause problems.
i have also used a simple sql insert query like
$insertquery = INSERT INTO table(username, ...) VALUES ('$username', ...);
when i used this simple insert query and if i entered ' in the form and
submitted the form the php file is unable to process the information entered
because of the ' character and as per the code error.html file is being
displayed where as if i use
$insertquery = sprintf(INSERT INTO table (`username`, ...) VALUES ('%s',
...), mysql_real_escape_string($username, $conn), ...);
even if i enter any number of ' characters in more than 1 form field data is
being inserted into the table
a)
so i am thinking that the steps i have taken from the php site is correct
and the right way to avoid sql injection though there are several ways to
avoid sql injection.
b)
for example if i enter data in the form as = abc'''def for name, the data in
the table for the name field is being written as abc'''def
based on how i have written the steps to avoid sql injection is this the
right way for the data to be stored with ' characters along with the data
example as i mentioned = abc'''def
please answer the questions a) and b) if there is something else i need to
do please suggest what needs to be done exactly and at which step.
any help will be greatly appreciated.
thanks.
Re: [PHP] sql injection
YOU can write (') characters in the database.. that fine..
mysql_real_escape_string avoid injections doing that: escaping characters
then when you put in a form
abc'''def
the query will be
INSERT . (name.) VALUES ( 'abc\'\'\'def'
each ' = \'
for me the steps are right
saludos
On Thu, May 29, 2008 at 4:10 PM, Sudhakar [EMAIL PROTECTED] wrote:
i have implemented a way to avoid sql injection from the php website from
this url
http://in.php.net/mysql_real_escape_string from the Example #3 A Best
Practice query section of this page
following are the steps i have followed after the form values are submitted
to a php file.
step 1.
if(get_magic_quotes_gpc())
{
$username = stripslashes($_POST[username]);
.
}
else
{
$username = $_POST[username];
.
}
step 2.
$conn = mysql_connect($hostname, $user, $password);
step 3.
$insertquery = sprintf(INSERT INTO table (`username`, ...) VALUES ('%s',
...), mysql_real_escape_string($username, $conn), ...);
step 4.
if(!$conn)
{
header(Location: http://website/dberror.html;);
exit;
}
else
{
mysql_select_db($database, $conn);
$insertqueryresult = mysql_query($insertquery);
if(!$insertqueryresult) {
header(Location: http://website/error.html;);
exit; }
}
with the above method i am able to insert values into the table even with if
i enter the ' special character which can cause problems.
i have also used a simple sql insert query like
$insertquery = INSERT INTO table(username, ...) VALUES ('$username', ...);
when i used this simple insert query and if i entered ' in the form and
submitted the form the php file is unable to process the information entered
because of the ' character and as per the code error.html file is being
displayed where as if i use
$insertquery = sprintf(INSERT INTO table (`username`, ...) VALUES ('%s',
...), mysql_real_escape_string($username, $conn), ...);
even if i enter any number of ' characters in more than 1 form field data is
being inserted into the table
a)
so i am thinking that the steps i have taken from the php site is correct
and the right way to avoid sql injection though there are several ways to
avoid sql injection.
b)
for example if i enter data in the form as = abc'''def for name, the data in
the table for the name field is being written as abc'''def
based on how i have written the steps to avoid sql injection is this the
right way for the data to be stored with ' characters along with the data
example as i mentioned = abc'''def
please answer the questions a) and b) if there is something else i need to
do please suggest what needs to be done exactly and at which step.
any help will be greatly appreciated.
thanks.
--
Los sabios buscan la sabiduría; los necios creen haberla encontrado.
Gabriel Sosa
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] sql syntax using sprintf
until i started using the techniques for avoiding sql injection, i have been
using a normal insert and select sql query which worked fine.
i have a registration page where a user enters their username and if this
already exists i display a message by executing a select query and if the
username does not exist then i run an insert query.
after adopting the technique to avoid sql injection
if(get_magic_quotes_gpc())
{
$username = stripslashes($_POST[username]);
$email =stripslashes($_POST[email]);
}
else
{
$username = $_POST[username];
$email =$_POST[email];
}
previously my select and insert query were
INSERT INTO individuals(username, email) values('$username', '$email')
Select username from individuals where username = '$username'
presently the insert query is
$insertquery = sprintf(INSERT INTO individuals (username, email) VALUES
('%s', '%s'),
mysql_real_escape_string($username), mysql_real_escape_string($email));
This insert query is working however the select query is not doing its task
as before of checking if the username already exists or not, even if i
register with the same username again it does not alert that the username
exists.
the select query is
$selectqueryusername = sprintf(Select username from individuals where
username='%s', mysql_real_escape_string($username));
should i change the syntax of the above select query or is there something
else in need to do to fix the select query.
please advice.
thanks.
SV: SV: SV: [PHP] Sql support
hereis my php.ini i think this file are correct [PHP] ;;; ; About php.ini ; ;;; ; This file controls many aspects of PHP's behavior. In order for PHP to ; read it, it must be named 'php.ini'. PHP looks for it in the current ; working directory, in the path designated by the environment variable ; PHPRC, and in the path that was defined in compile time (in that order). ; Under Windows, the compile-time path is the Windows directory. The ; path in which the php.ini file is looked for can be overridden using ; the -c argument in command line mode. ; ; The syntax of the file is extremely simple. Whitespace and Lines ; beginning with a semicolon are silently ignored (as you probably guessed). ; Section headers (e.g. [Foo]) are also silently ignored, even though ; they might mean something in the future. ; ; Directives are specified using the following syntax: ; directive = value ; Directive names are *case sensitive* - foo=bar is different from FOO=bar. ; ; The value can be a string, a number, a PHP constant (e.g. E_ALL or M_PI), one ; of the INI constants (On, Off, True, False, Yes, No and None) or an expression ; (e.g. E_ALL ~E_NOTICE), or a quoted string (foo). ; ; Expressions in the INI file are limited to bitwise operators and parentheses: ; |bitwise OR ; bitwise AND ; ~bitwise NOT ; !boolean NOT ; ; Boolean flags can be turned on using the values 1, On, True or Yes. ; They can be turned off using the values 0, Off, False or No. ; ; An empty string can be denoted by simply not writing anything after the equal ; sign, or by using the None keyword: ; ; foo = ; sets foo to an empty string ; foo = none; sets foo to an empty string ; foo = none ; sets foo to the string 'none' ; ; If you use constants in your value, and these constants belong to a ; dynamically loaded extension (either a PHP extension or a Zend extension), ; you may only use these constants *after* the line that loads the extension. ; ; ;;; ; About this file ; ;;; ; This is the recommended, PHP 5-style version of the php.ini-dist file. It ; sets some non standard settings, that make PHP more efficient, more secure, ; and encourage cleaner coding. ; ; The price is that with these settings, PHP may be incompatible with some ; applications, and sometimes, more difficult to develop with. Using this ; file is warmly recommended for production sites. As all of the changes from ; the standard settings are thoroughly documented, you can go over each one, ; and decide whether you want to use it or not. ; ; For general information about the php.ini file, please consult the php.ini-dist ; file, included in your PHP distribution. ; ; This file is different from the php.ini-dist file in the fact that it features ; different values for several directives, in order to improve performance, while ; possibly breaking compatibility with the standard out-of-the-box behavior of ; PHP. Please make sure you read what's different, and modify your scripts ; accordingly, if you decide to use this file instead. ; ; - register_long_arrays = Off [Performance] ; Disables registration of the older (and deprecated) long predefined array ; variables ($HTTP_*_VARS). Instead, use the superglobals that were ; introduced in PHP 4.1.0 ; - display_errors = Off [Security] ; With this directive set to off, errors that occur during the execution of ; scripts will no longer be displayed as a part of the script output, and thus, ; will no longer be exposed to remote users. With some errors, the error message ; content may expose information about your script, web server, or database ; server that may be exploitable for hacking. Production sites should have this ; directive set to off. ; - log_errors = On[Security] ; This directive complements the above one. Any errors that occur during the ; execution of your script will be logged (typically, to your server's error log, ; but can be configured in several ways). Along with setting display_errors to off, ; this setup gives you the ability to fully understand what may have gone wrong, ; without exposing any sensitive information to remote users. ; - output_buffering = 4096[Performance] ; Set a 4KB output buffer. Enabling output buffering typically results in less ; writes, and sometimes less packets sent on the wire, which can often lead to ; better performance. The gain this directive actually yields greatly depends ; on which Web server you're working with, and what kind of scripts you're using. ; - register_argc_argv = Off [Performance] ; Disables registration of the somewhat redundant $argv and $argc global ; variables. ; - magic_quotes_gpc = Off [Performance] ; Input data is no longer escaped with slashes so that it can be sent into ; SQL databases without further
Re: SV: SV: [PHP] Sql support
Either you see a section about mssql extension or you don't. If you see it, you have it. If you don't, you don't. It's really that simple... You have bcmath, calendar, COM, ctype, date, DOM, filter, ftp, hash and iconv. You don't have mssql, nor ODBC for that matter. (Unless ODBC is built-in, which I doubt...) On Thu, January 24, 2008 6:31 pm, mattias wrote: yes but if you check my phpinfo.php http://mjw.se/phpinfo.php i can't reed out if sql support are enabled -Ursprungligt meddelande- Från: Chris [mailto:[EMAIL PROTECTED] Skickat: den 25 januari 2008 01:18 Till: mattias Kopia: php-general@lists.php.net Ämne: Re: SV: [PHP] Sql support mattias wrote: oops sorry i meen sql You could probably access it through odbc but I can't see any other method available. If you want to use the mssql_* functions the php site has info about how to get it going: http://php.net/mssql Read the comments too - it looks like there are a few hints there. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Sql support
I try to add php-support to my php installation on windows 2000 server Can someone check my phpinfo.php? http://mjw.se/phpinfo.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sql support
mattias wrote: I try to add php-support to my php installation on windows 2000 server Eh? What sort of support are you trying to add? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
SV: [PHP] Sql support
oops sorry i meen sql -Ursprungligt meddelande- Från: Chris [mailto:[EMAIL PROTECTED] Skickat: den 25 januari 2008 00:06 Till: mattias Kopia: php-general@lists.php.net Ämne: Re: [PHP] Sql support mattias wrote: I try to add php-support to my php installation on windows 2000 server Eh? What sort of support are you trying to add? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: SV: [PHP] Sql support
mattias wrote: oops sorry i meen sql You could probably access it through odbc but I can't see any other method available. If you want to use the mssql_* functions the php site has info about how to get it going: http://php.net/mssql Read the comments too - it looks like there are a few hints there. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sql support
On Thu, January 24, 2008 5:05 pm, Chris wrote: mattias wrote: I try to add php-support to my php installation on windows 2000 server Eh? What sort of support are you trying to add? The subject said Sql support so I'd guess MS SQL. The answer being: #1 Look at a page like this on your server: ?php phpinfo();? #2 Find (or put) a php.ini file in the directory where PHP is looking for it. #3 Edit that php.ini file and un-comment the mssql.dll bit in the extensions list. #4 Restart the computer. [It's Windows. He's used to re-booting.] -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: SV: SV: [PHP] Sql support
mattias wrote: yes but if you check my phpinfo.php http://mjw.se/phpinfo.php i can't reed out if sql support are enabled If it was there would be a big section called 'mssql' - like there is for odbc. If that's not there, then no you don't have mssql support enabled. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
SV: SV: [PHP] Sql support
yes but if you check my phpinfo.php http://mjw.se/phpinfo.php i can't reed out if sql support are enabled -Ursprungligt meddelande- Från: Chris [mailto:[EMAIL PROTECTED] Skickat: den 25 januari 2008 01:18 Till: mattias Kopia: php-general@lists.php.net Ämne: Re: SV: [PHP] Sql support mattias wrote: oops sorry i meen sql You could probably access it through odbc but I can't see any other method available. If you want to use the mssql_* functions the php site has info about how to get it going: http://php.net/mssql Read the comments too - it looks like there are a few hints there. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL Distinct-like behaviour
Read them all into an array and do an array-unique on it bastien Date: Thu, 23 Aug 2007 23:54:40 -0300 From: [EMAIL PROTECTED] To: php-general@lists.php.net Subject: [PHP] SQL Distinct-like behaviour How could I iterate over the files in a directory and build a list of unique filenames? Take the following filelist: file1_01.jpg file2_01.jpg file2_02.jpg file2_03.jpg file3_01.jpg file3_02.jpg file3_03.jpg file4_01.jpg file4_02.jpg file4_03.jpg I would like to build an array like this: $names = (file1,file2,file3,file4) How could I do that ? Thanks in advance. _ Discover the new Windows Vista http://search.msn.com/results.aspx?q=windows+vistamkt=en-USform=QBRE
Re: [PHP] SQL Distinct-like behaviour
I don't know what I was thinking when I wrote that actually, that is so
blatantly obvious :)
Thanks Brian.
Hamza.
brian [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Marcelo de Moraes Serpa wrote:
How could I iterate over the files in a directory and build a list of
unique
filenames? Take the following filelist:
file1_01.jpg
file2_01.jpg
file2_02.jpg
file2_03.jpg
file3_01.jpg
file3_02.jpg
file3_03.jpg
file4_01.jpg
file4_02.jpg
file4_03.jpg
I would like to build an array like this: $names =
(file1,file2,file3,file4)
As mentioned, use array_unique(). But that'll only help once you've built
up an array of filenames (after trimming off the last bit). I think Jay
Hamza missed the fact that the files are *already unique*. One would be
hard-pressed to store multiple files with the same name in a directory.
So, i'm assuming your filenames wil be more like:
foo_01.jpg
foo_02.jpg
bar_01.jpg
etc. IOW, you want to perform a regexp such that you isolate the last part
to remove it before shuffling out the dupes. So:
$filenames = Array('foo_01.jpg', 'foo_02.jpg', 'bar_01.jpg', 'baz_01.jpg',
'bar_02.jpg');
$out = array_unique(preg_replace('/^([a-z]+)_[0-9]+\.jpg$/', '$1',
$filenames));
var_dump($out);
--snip--
array(3) {
[0]= string(3) foo
[2]= string(3) bar
[3]= string(3) baz
}
--snip--
Note that if you might have uppercase letters, dashes, underscores, etc.
in the filename you'll need to modify that a bit. Something like:
'/^([a-zA-Z-_]+)_[0-9]+\.jpg$/'
If you'll have more than one file extension, replace 'jpg' with '[a-z]+'
However, the array_unique call will cause, eg. both 'bar_01.jpg' and
'bar_01.png' to output 'bar' only once, which may not be what you want.
HTH,
brian
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL Injection protection
Hi Everyone, I have a application that I am writing for managing addresses on- line, I will have a fancy login system using sessions to help store the info, and to provide security for logging in. What I am wondering now, is if someone is willing to try and do some sql injection's on my database? Nothing that will kill the database, just basically try to insert something into it and to see if it lets you? I have tried with a article I found on phpsec.org and couldn't make it work, but I just want to double check with someone who has more experience. If you are interested, and knowledgeable, e-mail me off list so I can point you to the right place to try it. Thanks for looking! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
RE: [PHP] SQL Distinct-like behaviour
[snip] How could I iterate over the files in a directory and build a list of unique filenames? Take the following filelist: file1_01.jpg file2_01.jpg file2_02.jpg file2_03.jpg file3_01.jpg file3_02.jpg file3_03.jpg file4_01.jpg file4_02.jpg file4_03.jpg I would like to build an array like this: $names = (file1,file2,file3,file4) How could I do that ? [/snip] http://us2.php.net/manual/en/function.array-unique.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Distinct-like behaviour
Marcelo de Moraes Serpa wrote:
How could I iterate over the files in a directory and build a list of unique
filenames? Take the following filelist:
file1_01.jpg
file2_01.jpg
file2_02.jpg
file2_03.jpg
file3_01.jpg
file3_02.jpg
file3_03.jpg
file4_01.jpg
file4_02.jpg
file4_03.jpg
I would like to build an array like this: $names =
(file1,file2,file3,file4)
As mentioned, use array_unique(). But that'll only help once you've
built up an array of filenames (after trimming off the last bit). I
think Jay Hamza missed the fact that the files are *already unique*.
One would be hard-pressed to store multiple files with the same name in
a directory.
So, i'm assuming your filenames wil be more like:
foo_01.jpg
foo_02.jpg
bar_01.jpg
etc. IOW, you want to perform a regexp such that you isolate the last
part to remove it before shuffling out the dupes. So:
$filenames = Array('foo_01.jpg', 'foo_02.jpg', 'bar_01.jpg',
'baz_01.jpg', 'bar_02.jpg');
$out = array_unique(preg_replace('/^([a-z]+)_[0-9]+\.jpg$/', '$1',
$filenames));
var_dump($out);
--snip--
array(3) {
[0]= string(3) foo
[2]= string(3) bar
[3]= string(3) baz
}
--snip--
Note that if you might have uppercase letters, dashes, underscores, etc.
in the filename you'll need to modify that a bit. Something like:
'/^([a-zA-Z-_]+)_[0-9]+\.jpg$/'
If you'll have more than one file extension, replace 'jpg' with '[a-z]+'
However, the array_unique call will cause, eg. both 'bar_01.jpg' and
'bar_01.png' to output 'bar' only once, which may not be what you want.
HTH,
brian
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL Distinct-like behaviour
How could I iterate over the files in a directory and build a list of unique filenames? Take the following filelist: file1_01.jpg file2_01.jpg file2_02.jpg file2_03.jpg file3_01.jpg file3_02.jpg file3_03.jpg file4_01.jpg file4_02.jpg file4_03.jpg I would like to build an array like this: $names = (file1,file2,file3,file4) How could I do that ? Thanks in advance.
[PHP] SQL Source Control
Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Cheers, Rich -- Zend Certified Engineer http://www.corephp.co.uk Never trust a computer you can't throw out of a window -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Richard Davey wrote: Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? lets forget that updating SQL schemas on massive DBs will likely take so much time that you will have to plan in downtime on the systems involved ... that's clearly out of the scope of this question. my strategy is also pretty weak in this regard but generally: I write my code in such a way that older code can run with newer db schemas, which basically means I add stuff but never remove it (tables, fields, etc) ... schema updates are always 'expansive'. If I'm feeling very tidy I'll create a seperate CVS module for the schema and updates/rollbacks. this involves writing sql files that update for each version of the project I have/will rollout ... and also sql files that perform the reverse/rollback actions between project versions. (when I'm being tidy I always do DB schema update when the major version number of a project changes) I end up with files named something like: v1-to-v2.sql v2-to-v3.sql v2-to-v1.sql v3-to-v2.sql then I include a script which I can call with the desired version number and it works out which sql files it needs to run and in which order (the current version is either stored in the DB or stored in a txt file outside of CVS) ... I have considered making this 'change version' script also automatically perform the required 'cvs up -r Foo' command on the actual project files but I have not got round to ever actually do it (time, money, inspiration, lackof) - maybe that brainfart inspires somewhat, then again maybe you'll pass out fom the smell :-) Cheers, Rich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Jochem Maas wrote: lets forget that updating SQL schemas on massive DBs will likely take so much time that you will have to plan in downtime on the systems involved ... that's clearly out of the scope of this question. Yes, this was part of the problem (and reason for my original post). At the moment I'm dealing with a 2GB SQL database, with hundreds of modifications per minute. Rolling out new features always requires that we take the site down anyway, just so we can stablise the changes coming in and back-up the database. But this is more disaster recovery than version control, and doesn't get around a problem such as: running with a site upgrade (which expands an existing set of tables), taking new valid data from users into that new schema, plus into older un-touched tables, then needing to rollback for whatever reason - we're left with a horrendous 'merge' issue. I'm surprised (or rather, I'm unaware of) there is no native MySQL solution for this situation. Perhaps that's left to the bigger boys? Or maybe it's the main 'weak area' of most web developers :) - maybe that brainfart inspires somewhat, then again maybe you'll pass out fom the smell :-) It did actually. I'm thinking that perhaps I tag all data with the *version* of the site in which it was created. And tag schema updates in a similar way as you suggested. Still.. am amazed nothing more 'standard' exists. Cheers, Rich -- Zend Certified Engineer http://www.corephp.co.uk Never trust a computer you can't throw out of a window -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Richard Davey wrote: Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Cheers, Rich Rich, This is a well-known problem that comes from the fact that the database is an infrastructure component rather than a source component. You would have the same issue if you were to upgrade the operating system software, for example. What most people do is use their tool of choice to create the DDL/DML update scripts and put those under source control. To version the data you need to make database backups at well-understood times and grab the data files if appropriate. Rolling back a change is a matter of recovering the database and files to a point in time along with the sources. I don't know of any management tools for this part of it. Most larger organizations have different people responsible for the database and web tiers, so a single tool won't do. Some folks are trying to use ClearCase automation to manage a lot of it, but it's still a work in progress... In a smaller environment I would be inclined to create shell/whatever scripts to do the actual implementation. If you parameterize the connection/server details you can test the implementation in a QA environment before going live - less need for rollbacks that way. The shell scripts greatly reduce the chance of finger trouble which is key if your implementation is being done at some uncivilized hour or by rookies. If you want to truly embrace the New World, you can do all of this using Ant, which has built-in tasks for CVS/SVN as well as file movement, etc. It can also run shell scripts for the database stuff. ...Lori -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
Richard Davey wrote: Hi all, I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Cheers, Rich One thing we do is add a table called 'versions' to each application, this table just has one row and a column for the schema version (and sometimes other stuff less important.) When the app runs it checks to ensure that its defined constant DBVERSION matches that of the database it is running against. This has actually helped out more than once, though not a solution to the actual problem. Travis Doherty -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
I'm surprised (or rather, I'm unaware of) there is no native MySQL solution for this situation. Considering it's not a mysql specific problem I'd be surprised if there was. Postgres lets you do database changes inside a transaction, eg: begin; alter table x add column y int; ... commit; but two problems with that: 1) mysql doesn't support it (nor do a lot of databases) even when using innodb (and using myisam is out of the question for this because it doesn't support transactions) 2) it won't help in a web environment because you can't rollback once the transaction has finished you are testing the changes and then find the problem. No easily solution unfortunately apart from writing 'undo' scripts or having a backup handy.. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Source Control
On Wed, April 11, 2007 10:13 am, Richard Davey wrote: I know a lot of you use various means for PHP source control (CVS, SVN, etc), which is all well and fine, but how do you manage source control on your databases? Say you've got an upgrade to a site, all of the new PHP files are controlled by SVN, so you can rollback at any time, but say the upgrade includes several key modifications to a MySQL table and perhaps the changing of some core data. How (if at all?!) do you handle the versioning of the database and data itself, so you can keep both PHP and SQL structure in sync? Good thread! Though perhaps a MySQL list would have more insight... I wonder if perhaps some larger-scale sites might not just buy a whole 'nother DB server, and flip the connection line to the new one... Granted, you lose all new data if you have to revert, but at least you know you have a valid state to revert to... Kinda pricey, but there it is. I suppose the other thing I do that hasn't been mentioned is plan my DB schema a bit farther out than my PHP code, so that I've got new empty unused fields/tables sitting there, and not doing any harm, but that I can add features without worrying that they won't work. I've done this successfully a couple times for simple stuff, but it usually won't work for anything remotely complex, as I don't anticipate the DB needs correctly. Still, better to have an unused Future Tech #1 field if you know you'll need it than to try to add it at the last minute when you do need it, for simple stuff. I suppose one could do a mysql dump of at least the schema and svn that... -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP + SQL..
On Thu, February 22, 2007 7:53 pm, Liz Kim wrote: When I try to get a really long string and print it out on a website, it tends to stop in the middle of the string. Is this some sort of a limitation that I can change? It's probably a limit on the number of characters that can be stored in your database datatype. So it's not getting lost when you display it -- It never got into the DB in the first place. You may be able to re-configure your DB to change the limit for TEXT (which I assume you are using) but there may also be a pre-existing LONGTEXT which has an even higher limit, or you may need to go for a BLOB which has an even higher limit (often the limit there is the size of your hard drive). Better than all of that, however, is to take a step back and consider if cramming such a large chunk of data into your database is really the correct solution. Unless you are actually doing a DB search, sort, or other SQL operation on that field, you would be better off storing it in a file on the hard drive, and leaving the DB operations in the DB, and the large-file operations in the highly-optimized, more mature, and customized db solution specifically designed for large file managements. (aka, your file system) It's also possible that your datatypes are correct, but the QUERY size is limited. This is unlikely given your description, but it's the NEXT problem you are going to run into, and is even more tricky to solve unless you have complete control over your environment (dedicated servers). You'll have to do more research about the database you are using -- and further questions in this arena really belong on that database's mailing list, rather than here, as PHP is almost certainly not involved, really, in the problem. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some starving artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP + SQL..
I am storing a lot of text into my mssql database. Are there any special characters I should watch out for? I think so far I've got ' covered. Also, when I retrieved the string later on... The ' is printed out with a \ in the front. How do I get rid of the \? When I try to get a really long string and print it out on a website, it tends to stop in the middle of the string. Is this some sort of a limitation that I can change? Thank you!
Re: [PHP] PHP + SQL..
On Thu, 2007-02-22 at 17:53 -0800, Liz Kim wrote: I am storing a lot of text into my mssql database. Are there any special characters I should watch out for? I think so far I've got ' covered. Skip the guesswork and use an appropriate quoting mechanism for your database -- that said I'm not aware of an mssql specific quoting mechanism (and I don't care to find out :) -- so maybe just use addSlashes(). Also, when I retrieved the string later on... The ' is printed out with a \ in the front. How do I get rid of the \? Turn off magic quotes. When I try to get a really long string and print it out on a website, it tends to stop in the middle of the string. Is this some sort of a limitation that I can change? Sounds like your storing the data into a table column that is too short for the data and so it gets truncated when stored. Then when you retrieve it you get the truncated text. Increase the column width. Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
Larry Garfield wrote: On Saturday 27 January 2007 1:14 pm, Jochem Maas wrote: query builders are alot more fiddly to get 'right' than one might imagine, dealing with NULLs, booleans and dates for example (as Satyam pointed out) can be a right PITA. I actually almost never use native date types in the SQL database. I just store unix timestamps and do the math in PHP. Dates are completely unportable anyway. I also tend to use ints for booleans, too, although beefing up the switch statements in the code to handle native booleans should be trivial. mysql doesn't have booleans does it? at least not versions I have to use. with regard to date stuff, many people take the opposite approach and do most of the date math inside SQL - most DBs have kickass date calculation functions btw. and for the times when you need/want unix timestamps, mysql atleast, gives you UNIX_TIMSTAMP(). At least as of MySQL 4.1 (haven't played with MySQL 5 much yet), yes, MySQL has no native boolean data type that I know of. The standard alternative is TINYINT(1), which technically gives you values 0-9. And yes, I agree that MySQL has fairly decent date manipulation routines. But at work we do try for database independence when possible, so except on specific projects we try to avoid it. again we differ :-) I have never bought the 'data independence' story - in practice it's of little value imho most of the time (granted certain products do benefit - but what I build doesn't fall into that category) and I find it crazy to end up with a situation where the most advanced peice of data manipulation software in a given stack is dumbed down to the lowest common denominator [of DB engines]. On more complex project I try to cram as much of the data intregity and business logic in to the database itself (for which I use firebird mostly) because it means being able to create different clients to the data without replicating [as much] business logic (e.g. website and desktop app). besides which the required stored procedures and triggers are usually hundreds of lines less than their php equivalent AND more importantly they are intrinsically atomic (in the sense that database transaction 'should' be). rgds :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
- Original Message - From: Jochem Maas [EMAIL PROTECTED] To: Larry Garfield [EMAIL PROTECTED] Cc: php-general@lists.php.net Sent: Sunday, January 28, 2007 12:55 PM Subject: Re: [PHP] SQL Readability.. (was Re: most powerful php editor) Larry Garfield wrote: On Saturday 27 January 2007 1:14 pm, Jochem Maas wrote: query builders are alot more fiddly to get 'right' than one might imagine, dealing with NULLs, booleans and dates for example (as Satyam pointed out) can be a right PITA. I actually almost never use native date types in the SQL database. I just store unix timestamps and do the math in PHP. Dates are completely unportable anyway. I also tend to use ints for booleans, too, although beefing up the switch statements in the code to handle native booleans should be trivial. mysql doesn't have booleans does it? at least not versions I have to use. with regard to date stuff, many people take the opposite approach and do most of the date math inside SQL - most DBs have kickass date calculation functions btw. and for the times when you need/want unix timestamps, mysql atleast, gives you UNIX_TIMSTAMP(). At least as of MySQL 4.1 (haven't played with MySQL 5 much yet), yes, MySQL has no native boolean data type that I know of. The standard alternative is TINYINT(1), which technically gives you values 0-9. And yes, I agree that MySQL has fairly decent date manipulation routines. But at work we do try for database independence when possible, so except on specific projects we try to avoid it. again we differ :-) I have never bought the 'data independence' story - in practice it's of little value imho most of the time (granted certain products do benefit - but what I build doesn't fall into that category) and I find it crazy to end up with a situation where the most advanced peice of data manipulation software in a given stack is dumbed down to the lowest common denominator [of DB engines]. On more complex project I try to cram as much of the data intregity and business logic in to the database itself (for which I use firebird mostly) because it means being able to create different clients to the data without replicating [as much] business logic (e.g. website and desktop app). besides which the required stored procedures and triggers are usually hundreds of lines less than their php equivalent AND more importantly they are intrinsically atomic (in the sense that database transaction 'should' be). rgds :-) Hear!, hear! (or something to that effect) Satyam -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
On Sunday 28 January 2007 5:55 am, Jochem Maas wrote: And yes, I agree that MySQL has fairly decent date manipulation routines. But at work we do try for database independence when possible, so except on specific projects we try to avoid it. again we differ :-) I have never bought the 'data independence' story - in practice it's of little value imho most of the time (granted certain products do benefit - but what I build doesn't fall into that category) and I find it crazy to end up with a situation where the most advanced peice of data manipulation software in a given stack is dumbed down to the lowest common denominator [of DB engines]. On more complex project I try to cram as much of the data intregity and business logic in to the database itself (for which I use firebird mostly) because it means being able to create different clients to the data without replicating [as much] business logic (e.g. website and desktop app). besides which the required stored procedures and triggers are usually hundreds of lines less than their php equivalent AND more importantly they are intrinsically atomic (in the sense that database transaction 'should' be). rgds :-) Well, business reasons dictate that we keep our code portable when possible at work. I'm not the business person. I just write the code. :-) -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
- Original Message -
From: Larry Garfield [EMAIL PROTECTED]
To: php-general@lists.php.net
Sent: Saturday, January 27, 2007 12:18 AM
Subject: Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
I have long since given up on raw insert/update/delete statements as the
syntax is all kinds nasty. These days I just do this, which is even
easier
and more powerful:
http://www.garfieldtech.com/blog/simplifying-sql
I tried the following:
insert('sometable',array('when' = mktime(0,0,0,2,1,2007),'if' = true));
which produced the following SQL statement:
INSERT INTO sometable (when) VALUES (1170284400,1170284400)
The problem is that PHP types do not correspond to SQL types. Though a
boolean does identify itself as such, a date never does. Your switch() on
the gettype() of the value misses the type 'boolean' so it falls through the
default: case which then appends whatever was left from the previous pass.
However, even adding a case for type boolean there is no way to recognize
dates since they are no more than integers for all PHP cares. Finally, what
happens with an expression that produces a sort-of boolean, like anything
non-zero for true?
Those are the reasons I used type modifiers in my BuildSql function
(http://www.satyam.com.ar/int/BuildSql.php), I couldn't rely on PHP figuring
them out correctly. This also allowed me to expand those modifiers to
optional positional modifiers and null handling ones.
I even tried to query the SQL engine to report them back, but that was also
unreliable, MySql for one, reports the type of what it used to store it, not
what you declared them to be. Thus, for a boolean field it will report
integer, but if you try to store a number other than 0 or 1 it then
complains. So, unable to get reliable information from either end, I
decided on stating the type explicitly on the query string.
Satyam
On Friday 26 January 2007 10:03 am, [EMAIL PROTECTED] wrote:
My contribution to the insanity.. INSERT statements made easy:
$genericQY = INSERT INTO MOD_LMGR_Leads (; $genericQYvalues =
VALUES
(; $genericQY .= FirstName,; $genericQYvalues .=
'John',; $genericQY .= LastName; $genericQYvalues
.= 'Smith'; $genericQY .= );
$genericQYvalues .= );; $genericQY .= $genericQYvalues;
$genericRS = mysql_query($genericQY);
I use this structure so if I decide that I don't need certain data I can
comment out a single line to remove the column name and corresponding
value. Also helpful for making updates to column/value pairs and not
worry
about the dreaded error involve # of columns not matching.
Only things you have to watch for:
1. Make sure you don't have a comma on the last item
2. Make sure you have spaces where appropriate so when it concatenates
the
strings, you don't get stuff crammed together (not really an issue with
the
INSERT statement, but I try to keep a consistant practice with all my
queries so I don't slip up.. SELECT columnsFROM tableWHERE something =
something is where it really gets ya if you forget spaces.. just as an
example) 3. Make sure to remember to concatenate the query and values
parts
I like to think this is a little outside the box thinking since common
practice is one command, one line or total chaos hah.
Any comments on improving this or other unique stylistic ways people like
to design their code?
-TG
= = = Original message = = =
On Wed, January 24, 2007 8:07 pm, Robert Cummings wrote:
On Wed, 2007-01-24 at 18:23 -0600, Richard Lynch wrote:
On Wed, January 24, 2007 7:41 am, Roman Neuhauser wrote:
# [EMAIL PROTECTED] / 2007-01-24 13:57:03 +0200:
and also in these days I'm looking for 19 inch (or more) wide LCD
sceerns to able to fit longer lines in my screen...
Number of reading errors people make grows with line length,
this has been known for as long as I remember. You're increasing
the
probability of bugs in the code, and get tired sooner because
following
long lines requires more energy.
I believe those results are specific to what is being read.
Surely it's easier to read:
SELECT blah, blah, blah, blah, blah, blah, blah, blah, blah
if it's all on one line, no matter how many fields there are, while
trying to read the code as a whole.
Sure, it can be hard to find/read the individual field names, on
the
rare occasion that you need to do that...
Dear Mr Lynch, normally I highly respect your commentary on the list,
but today I think you've been-a-smoking the crackpipe a tad too much.
There is no way in hell one long line of SQL is easier to read than
formatted SQL that clearly delineates the clause structure.
SELECT A.field1 AS afield1, A.field2 AS afield2, B.field1 AS bfield1,
B.field2 AS bfield2, C.field1 AS cfield1, C.field2 AS cfield2,
D.field1
AS dfield1, D.field2 AS dfield2 FROM tableA as A LEFT JOIN tableB AS B
ON B.fee = A.foo LEFT JOIN tableC AS C ON C.fii = B.fee LEFT JOIN
tableD
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
Larry Garfield wrote: I have long since given up on raw insert/update/delete statements as the syntax is all kinds nasty. These days I just do this, which is even easier and more powerful: http://www.garfieldtech.com/blog/simplifying-sql a quick look at those funcs gives me the impression that they are woefully inadequate for any level of complex realworld use. query builders are alot more fiddly to get 'right' than one might imagine, dealing with NULLs, booleans and dates for example (as Satyam pointed out) can be a right PITA. perfect automated CRUD (it's an acronym!) is kind a holy grail - and that is, I think, the driving force behind most attempts to crteate query builders. also I don't really agree with the sentiment that SQL syntax is nasty, personally I find it, mostly, very easy to read and powerful ... but as this thread shows there is no accounting for taste! :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
On Sat, 2007-01-27 at 14:43 +0100, Jochem Maas wrote: also I don't really agree with the sentiment that SQL syntax is nasty, Hear, hear :) Cheers, Rob. -- .. | InterJinn Application Framework - http://www.interjinn.com | :: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
On Saturday 27 January 2007 7:43 am, Jochem Maas wrote: Larry Garfield wrote: I have long since given up on raw insert/update/delete statements as the syntax is all kinds nasty. These days I just do this, which is even easier and more powerful: http://www.garfieldtech.com/blog/simplifying-sql a quick look at those funcs gives me the impression that they are woefully inadequate for any level of complex realworld use. That's interesting, because I've been using variants of that for a year now with much success in a dozen projects. query builders are alot more fiddly to get 'right' than one might imagine, dealing with NULLs, booleans and dates for example (as Satyam pointed out) can be a right PITA. I actually almost never use native date types in the SQL database. I just store unix timestamps and do the math in PHP. Dates are completely unportable anyway. I also tend to use ints for booleans, too, although beefing up the switch statements in the code to handle native booleans should be trivial. perfect automated CRUD (it's an acronym!) is kind a holy grail - and that is, I think, the driving force behind most attempts to crteate query builders. Orthogonal persistence is, yes. The goal here was simply to make dealing with arbitrary insert and update statements easier, which in practice I've found to be a huge success. Full arbitrary CRUD and orthogonal persistence is much harder. That's why there's a dozen ORMs out there, all of which have some major flaw. :-) also I don't really agree with the sentiment that SQL syntax is nasty, personally I find it, mostly, very easy to read and powerful ... but as this thread shows there is no accounting for taste! :-) What bugs me most about SQL syntax is INSERT vs. UPDATE. I don't know the underlying implementation details of the engine, but from the level I work at (sending SQL to a database from a web app) I see no legitimate reason why those two very-similar statements should have ridiculously different syntax. -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Readability.. (was Re: most powerful php editor)
Larry Garfield wrote: On Saturday 27 January 2007 7:43 am, Jochem Maas wrote: Larry Garfield wrote: I have long since given up on raw insert/update/delete statements as the syntax is all kinds nasty. These days I just do this, which is even easier and more powerful: http://www.garfieldtech.com/blog/simplifying-sql a quick look at those funcs gives me the impression that they are woefully inadequate for any level of complex realworld use. That's interesting, because I've been using variants of that for a year now with much success in a dozen projects. I was nitpicking - I'm quite sure they are useful within the bounds of the intended scope and wielded by a pair of hands that knows the code intimately (including any limitations). I run plenty of stuff that falls in the same category :-) query builders are alot more fiddly to get 'right' than one might imagine, dealing with NULLs, booleans and dates for example (as Satyam pointed out) can be a right PITA. I actually almost never use native date types in the SQL database. I just store unix timestamps and do the math in PHP. Dates are completely unportable anyway. I also tend to use ints for booleans, too, although beefing up the switch statements in the code to handle native booleans should be trivial. mysql doesn't have booleans does it? at least not versions I have to use. with regard to date stuff, many people take the opposite approach and do most of the date math inside SQL - most DBs have kickass date calculation functions btw. and for the times when you need/want unix timestamps, mysql atleast, gives you UNIX_TIMSTAMP(). (just some loose thoughts) perfect automated CRUD (it's an acronym!) is kind a holy grail - and that is, I think, the driving force behind most attempts to crteate query builders. Orthogonal persistence is, yes. The goal here was simply to make dealing with arbitrary insert and update statements easier, which in practice I've found to be a huge success. Full arbitrary CRUD and orthogonal persistence is much harder. That's why there's a dozen ORMs out there, all of which have some major flaw. :-) including mine :-) (not released because it, well, needs a big manual that only exists in my head - besides is firebird/ibase specific and I'm one of about 5 people who actually use php+firebird :-) also I don't really agree with the sentiment that SQL syntax is nasty, personally I find it, mostly, very easy to read and powerful ... but as this thread shows there is no accounting for taste! :-) What bugs me most about SQL syntax is INSERT vs. UPDATE. I don't know the underlying implementation details of the engine, but from the level I work at (sending SQL to a database from a web app) I see no legitimate reason why those two very-similar statements should have ridiculously different syntax. granted it's not perfect, somebody made a design 'fault' way back when and we're stuck with it. maybe someone else has some real info about why this is so. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
