Re: [PHP-DB] SQL injection

[Lester Caine]

On 21/06/15 20:14, Mark Murphy wrote:
> But what does your application do when it gets an invalid SQL statement?
> Maybe it is telling the attacker something important about your database so
> that they can compromise it with the appropriate injection.

It just defaults to the first news article in this case ... and counts
it as another hit on that article. We have never allowed free text SQL
to be included in any query, and any variable passed via the URL to
provide navigation is only ever passed as a parameter, so even if there
was no filtering of the parameter it would just fail. I'd only expect a
continued 'attack' if the URL was returning something useful so to carry
on just did not make sense ...

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL injection

[Mark Murphy]

But what does your application do when it gets an invalid SQL statement?
Maybe it is telling the attacker something important about your database so
that they can compromise it with the appropriate injection.

On 2:36PM, Sun, Jun 21, 2015 Lester Caine  wrote:

> On 21/06/15 18:55, Richard wrote:
> >>> OK - this had no chance of success since publish_date_desc is
> >>> >> processed using the _desc ( or _asc ) and any invalid data
> >>> >> stripped
> >>> >>
> >>> >>
> >>> >> &sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20n
> >>> >> ame_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const
> >>> >> (CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3
> >>> >> D1
> >>> >>
> >>> >> The question is more of interest in just what it was trying to
> >>> >> achieve? I presume hack MySQL? So Firebird would barf anyway, but
> >>> >> just trying to something that has generated some several hundred
> >>> >> error log entries in the last two days ...
> >>> >>
> >>> >> Lester Caine - G8HFL
> >>> >>
> >>> >>
> >> > The sub-query is invalid, if valid it would've been equivalent to:
> >> > or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy'
> >> > as 1))a) -- and 1=1
> >> >
> >> > Seems non threatening to me.
> > Regardless of whether this specific attack could have resulted in
> > harmful sql injection or not, user input should be sanitized so that
> > things never get this far.
>
> ? That is taken direct off the URL! Sod all I can do to prevent it, but
> I was simply asking if I was missing something as it did not make any
> sense? It got no further than the error log but as I said several
> hundred attempts via a few different filter options all of which
> suggested something that was expected to work if the site was a
> vulnerable mysql powered site ... which it's not.
>
> Seems that is just a pointless URL rather than some recently identified
> potential vulnerability?
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
> --

Sent from my android

Re: [PHP-DB] SQL injection

[Lester Caine]

On 21/06/15 18:55, Richard wrote:
>>> OK - this had no chance of success since publish_date_desc is
>>> >> processed using the _desc ( or _asc ) and any invalid data
>>> >> stripped
>>> >> 
>>> >> 
>>> >> &sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20n
>>> >> ame_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const
>>> >> (CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3
>>> >> D1
>>> >> 
>>> >> The question is more of interest in just what it was trying to
>>> >> achieve? I presume hack MySQL? So Firebird would barf anyway, but
>>> >> just trying to something that has generated some several hundred
>>> >> error log entries in the last two days ...
>>> >> 
>>> >> Lester Caine - G8HFL
>>> >> 
>>> >> 
>> > The sub-query is invalid, if valid it would've been equivalent to:
>> > or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy'
>> > as 1))a) -- and 1=1
>> > 
>> > Seems non threatening to me.
> Regardless of whether this specific attack could have resulted in
> harmful sql injection or not, user input should be sanitized so that
> things never get this far.

? That is taken direct off the URL! Sod all I can do to prevent it, but
I was simply asking if I was missing something as it did not make any
sense? It got no further than the error log but as I said several
hundred attempts via a few different filter options all of which
suggested something that was expected to work if the site was a
vulnerable mysql powered site ... which it's not.

Seems that is just a pointless URL rather than some recently identified
potential vulnerability?

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL injection

[Richard]

> Date: Sunday, June 21, 2015 12:39:06 PM -0400
> From: Aziz Saleh 
>
> On Sun, Jun 21, 2015 at 9:19 AM, Lester Caine 
> wrote:
> 
>> OK - this had no chance of success since publish_date_desc is
>> processed using the _desc ( or _asc ) and any invalid data
>> stripped
>> 
>> 
>> &sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20n
>> ame_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const
>> (CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3
>> D1
>> 
>> The question is more of interest in just what it was trying to
>> achieve? I presume hack MySQL? So Firebird would barf anyway, but
>> just trying to something that has generated some several hundred
>> error log entries in the last two days ...
>> 
>> Lester Caine - G8HFL
>> 
>> 
> The sub-query is invalid, if valid it would've been equivalent to:
> or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy'
> as 1))a) -- and 1=1
> 
> Seems non threatening to me.

Regardless of whether this specific attack could have resulted in
harmful sql injection or not, user input should be sanitized so that
things never get this far.



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL injection

[Aziz Saleh]

On Sun, Jun 21, 2015 at 9:19 AM, Lester Caine  wrote:

> OK - this had no chance of success since publish_date_desc is processed
> using the _desc ( or _asc ) and any invalid data stripped
>
>
> &sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20name_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const(CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3D1
>
> The question is more of interest in just what it was trying to achieve?
> I presume hack MySQL? So Firebird would barf anyway, but just trying to
> something that has generated some several hundred error log entries in
> the last two days ...
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
The sub-query is invalid, if valid it would've been equivalent to:
 or (1,2)=(select*from(select 'b2xvbG9zaGVy' as 1, 'b2xvbG9zaGVy' as 1))a)
-- and 1=1

Seems non threatening to me.

[PHP-DB] SQL injection

[Lester Caine]

OK - this had no chance of success since publish_date_desc is processed
using the _desc ( or _asc ) and any invalid data stripped

&sort_mode=publish_date_desc%20or%20(1,2)=(select*from(select%20name_const(CHAR(111,108,111,108,111,115,104,101,114),1),name_const(CHAR(111,108,111,108,111,115,104,101,114),1))a)%20--%20and%201%3D1

The question is more of interest in just what it was trying to achieve?
I presume hack MySQL? So Firebird would barf anyway, but just trying to
something that has generated some several hundred error log entries in
the last two days ...

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Lester Caine]

On 16/05/15 14:51, Karl DeSaulniers wrote:
> Interesting. I program in MySQL on a hosting plan by a third party.
> I have heard/read MySQL is not an enterprise solution, but 
> for the basic business with say less than 100,000 customers,
> it does the job and well. Larger than that I had hear Postgres
> and oracle were good to look at. Havent heard any good things about
> SQL server (.NET), but did't have too much trouble working with one a few 
> years back.
> I guess I don't know enough about what is available to do with a good 
> database and which
> to pick to do what I want with. There are so many. Hence my question here.

That probably sums up 'hosted' plans. The number of available database
engines has declined in recent years, and where a site 'outgrows' MySQL,
there are a few custom developments, but bottom line ... there is not a
single obvious answer ;)

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Karl DeSaulniers]

On May 16, 2015, at 8:42 AM, Lester Caine  wrote:

> On 16/05/15 10:00, Karl DeSaulniers wrote:
>> That does clarify things a bit better on both the @ question
>> and prepared statements. Thank you for the link as well.
>> 
>> So new question.. what is the best type of database to use
>> for someone who wants to start small and grow big?
>> 
>> My findings led me to MySQL InnoDB.
> 
> I'm somewhat biased since much of my data goes back to a time before
> MySQL even existed. Using Interbase which is now open source as
> Firebird. Early versions of MySQL were never stable enough to use in the
> environments I work, and while Postgres was also appearing on the radar,
> I've no reason to change. Little things like being able to run backups
> automatically even if I've never actually had to use one. And some SQL
> functions available in Firebird have yet to appear in other engines, and
> having to decide if you want the security InnoDB provides is simply
> standard in other engines.
> 
> The first question is are you hosting yourself or using third party
> hosting? MySQL tends to be available on all third party posting, with
> some providing Postgres, while Firebird tends to be privately hosted. If
> you are hosting yourself, then of cause MySQL may actually be MariaDB
> and you end up with a mix of sources. It's a bit like Internbase and
> Firebird where the commercial charges can affect one installation where
> the other is totally free.
> 
> If you are only looking for a single installation, then MySQL is
> probably fine. I'm running 50+ databases and with Firebird each is
> isolated in it's own directory and automatically backs up to the website
> storage area.
> 
> -- 
> Lester Caine - G8HFL
> -

Interesting. I program in MySQL on a hosting plan by a third party.
I have heard/read MySQL is not an enterprise solution, but 
for the basic business with say less than 100,000 customers,
it does the job and well. Larger than that I had hear Postgres
and oracle were good to look at. Havent heard any good things about
SQL server (.NET), but did't have too much trouble working with one a few years 
back.
I guess I don't know enough about what is available to do with a good database 
and which
to pick to do what I want with. There are so many. Hence my question here.

Again, thanks for your response.


Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Lester Caine]

On 16/05/15 10:00, Karl DeSaulniers wrote:
> That does clarify things a bit better on both the @ question
> and prepared statements. Thank you for the link as well.
> 
> So new question.. what is the best type of database to use
> for someone who wants to start small and grow big?
> 
> My findings led me to MySQL InnoDB.

I'm somewhat biased since much of my data goes back to a time before
MySQL even existed. Using Interbase which is now open source as
Firebird. Early versions of MySQL were never stable enough to use in the
environments I work, and while Postgres was also appearing on the radar,
I've no reason to change. Little things like being able to run backups
automatically even if I've never actually had to use one. And some SQL
functions available in Firebird have yet to appear in other engines, and
having to decide if you want the security InnoDB provides is simply
standard in other engines.

The first question is are you hosting yourself or using third party
hosting? MySQL tends to be available on all third party posting, with
some providing Postgres, while Firebird tends to be privately hosted. If
you are hosting yourself, then of cause MySQL may actually be MariaDB
and you end up with a mix of sources. It's a bit like Internbase and
Firebird where the commercial charges can affect one installation where
the other is totally free.

If you are only looking for a single installation, then MySQL is
probably fine. I'm running 50+ databases and with Firebird each is
isolated in it's own directory and automatically backs up to the website
storage area.

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Karl DeSaulniers]

On May 16, 2015, at 3:51 AM, Lester Caine  wrote:

> On 15/05/15 06:21, Karl DeSaulniers wrote:
>> Oh ok. Now it makes a little more sense. 
>> I have worked in ASP before, but I am programming in PHP and MySQL at the 
>> moment. 
>> 
>> I am going to look into Prepared Statements. Thanks for your feedback.
> 
> Just to clarify things a little here and explain
> http://php.net/manual/en/pdo.prepared-statements.php a little more ...
> 
> Many of the legacy injection problems where/are caused by building up
> the query as a fully self contained string. Various methods like
> 'magic_quotes' and wrapping $var in things like makesafe($var) were the
> only way some database engines could handle adding variables to the SQL
> string and much code still follows that style even today. Other database
> engines have always had the ability to pass the variables as a separate
> array of data, and the @x is more normally seen as a simple ? in the SQL
> string, so PDO and other frameworks map the ':var' elements of the first
> example to the relevant style used by the database. Actually naming
> parameters is not the norm, so one has to have the right number of '?'
> elements to go with the array of data passed, so PDO is adding a layer
> of code which hides the underlying execute(sql_query, array_of_data);
> 
> -- 
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

Thank you Lester. 
That does clarify things a bit better on both the @ question
and prepared statements. Thank you for the link as well.

So new question.. what is the best type of database to use
for someone who wants to start small and grow big?

My findings led me to MySQL InnoDB.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Lester Caine]

On 15/05/15 06:21, Karl DeSaulniers wrote:
> Oh ok. Now it makes a little more sense. 
> I have worked in ASP before, but I am programming in PHP and MySQL at the 
> moment. 
> 
> I am going to look into Prepared Statements. Thanks for your feedback.

Just to clarify things a little here and explain
http://php.net/manual/en/pdo.prepared-statements.php a little more ...

Many of the legacy injection problems where/are caused by building up
the query as a fully self contained string. Various methods like
'magic_quotes' and wrapping $var in things like makesafe($var) were the
only way some database engines could handle adding variables to the SQL
string and much code still follows that style even today. Other database
engines have always had the ability to pass the variables as a separate
array of data, and the @x is more normally seen as a simple ? in the SQL
string, so PDO and other frameworks map the ':var' elements of the first
example to the relevant style used by the database. Actually naming
parameters is not the norm, so one has to have the right number of '?'
elements to go with the array of data passed, so PDO is adding a layer
of code which hides the underlying execute(sql_query, array_of_data);

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Onatawahtaw]

-Kevin Waddell
Proverbs 3:5-6



On Fri, 5/15/15, Ruprecht Helms  wrote:

 Subject: Re: [PHP-DB] SQL Injection
 To: php-db@lists.php.net
 Date: Friday, May 15, 2015, 10:16 AM
 
 
 
 On 15.05.2015 07:21, Karl DeSaulniers wrote:
 > On May 14, 2015, at 11:11 PM, Onatawahtaw
 
 wrote:
 >
 >> Hi
 Karl,
 >>
 >> If
 you look at the link you provided you'll notice that
 some of the code is for ASP.net and some is for PHP.
 
 I have looked in the link.
 Most problems by inject an sql-Code is to add 
 something in the where-clause let it end with a
 semicolon and add an 
 additional sql-command
 behind the semicolon. In this case you have two 
 SQL-Command. The first maybe a Select-Command
 and the next can be to 
 drop a whole table
 with all its content.
 
 One
 thing you can do is to trim the Select-Statement and trough
 all 
 behind a semicolon in addition the
 semicolon away.
 
 To assume that any SQL injection is going to be by a second statement is very 
risky. Someone can simply add an "or" section to the where clause. It will 
still be one SQL statement and your trim will have done nothing to solve the 
problem. It is best not to make that assumption and not bother with trimming. 
Also, it would be tricky finding a proper trim function especially if your form 
input should contain semi-colons, etc.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Ruprecht Helms]

On 15.05.2015 07:21, Karl DeSaulniers wrote:

On May 14, 2015, at 11:11 PM, Onatawahtaw  wrote:


Hi Karl,

If you look at the link you provided you'll notice that some of the code is for 
ASP.net and some is for PHP.


I have looked in the link. Most problems by inject an sql-Code is to add 
something in the where-clause let it end with a semicolon and add an 
additional sql-command behind the semicolon. In this case you have two 
SQL-Command. The first maybe a Select-Command and the next can be to 
drop a whole table with all its content.


One thing you can do is to trim the Select-Statement and trough all 
behind a semicolon in addition the semicolon away.


Another securitymethod of mysql that the fieldvarables are capseled by 
escaping. So mysql get note that this is a variable content for a 
formfield and should looked like that.


Regards,
Ruprecht

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Karl DeSaulniers]

On May 14, 2015, at 11:11 PM, Onatawahtaw  wrote:

> Hi Karl,
> 
> If you look at the link you provided you'll notice that some of the code is 
> for ASP.net and some is for PHP. What of the two are you programming in? If 
> you are programming in ASP.net you are asking your question to the wrong 
> mailing list as this list is for PHP. If you are programming in PHP, then the 
> @ symbol does not apply to you.
> 
> Both prepared statements and mysqli_real_escape_string do provide adequate 
> security (if used correctly).  However, my recommendation is to learn how to 
> use PDO with prepared statements. PDO also offers the benefit of being able 
> to connect to multiple types of databases without needing to change your 
> code. If you use mysqli and down the road you decide you want to use Oracle, 
> MS SQL Server, or some other database server, you will ned to rework a lot of 
> your code. Not so with PDO.
> 
> Hope this helps,
> 
> -Kevin Waddell
> Proverbs 3:5-6

Oh ok. Now it makes a little more sense. 
I have worked in ASP before, but I am programming in PHP and MySQL at the 
moment. 

I am going to look into Prepared Statements. Thanks for your feedback. 

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Onatawahtaw]

Hi Karl,

If you look at the link you provided you'll notice that some of the code is for 
ASP.net and some is for PHP. What of the two are you programming in? If you are 
programming in ASP.net you are asking your question to the wrong mailing list 
as this list is for PHP. If you are programming in PHP, then the @ symbol does 
not apply to you.

Both prepared statements and mysqli_real_escape_string do provide adequate 
security (if used correctly).  However, my recommendation is to learn how to 
use PDO with prepared statements. PDO also offers the benefit of being able to 
connect to multiple types of databases without needing to change your code. If 
you use mysqli and down the road you decide you want to use Oracle, MS SQL 
Server, or some other database server, you will ned to rework a lot of your 
code. Not so with PDO.

Hope this helps,

-Kevin Waddell
Proverbs 3:5-6



On Thu, 5/14/15, Karl DeSaulniers  wrote:

 
 Ok, so understand my own situation, the method
 I have been using, mysqli real escape string is suffice?
 Or is the @ symbol is the better preferred
 method?
 
 Best,
 
 Karl DeSaulniers
 Design
 Drumm
 http://designdrumm.com
 
 
 
 
 
 
 
 
 
 
 --
 PHP
 Database Mailing List (http://www.php.net/)
 To
 unsubscribe, visit: http://www.php.net/unsub.php
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Karl DeSaulniers]

On May 14, 2015, at 8:37 PM, Jigme Datse Yli-Rasku  
wrote:

> On 15/05/14 18:19 , Karl DeSaulniers wrote:
>> On May 14, 2015, at 8:09 PM, Aziz Saleh  wrote:
>> 
>>> 
>>> 
>>> On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers  
>>> wrote:
>>> Hello Everyone,
>>> Have a quick question. Was reading some material and wanted some Players 
>>> perspective.
>>> I know w3schools is not the de-facto on everything, so I wanted to know how 
>>> reliable is the information on this page.
>>> 
>>> http://www.w3schools.com/sql/sql_injection.asp
>>> 
>>> Namely the @ symbol before SQL Values and because this talks about SQL and 
>>> not MySQL specifically, does this not apply to MySQL?
>>> To my uneducated eyes it seems legit. Any clarification is greatly 
>>> appreciated.
>>> 
>>> TIA,
>>> 
>>> Best,
>>> 
>>> Karl DeSaulniers
>>> Design Drumm
>>> http://designdrumm.com
>>> 
>>> 
>>> 
>>> That is preferred in PHP as well. The SQL/MySQL isn't specifically doing 
>>> the replacement, but rather the driver object. Using parametrized queries:
>>> 
>>> http://php.net/manual/en/pdo.prepared-statements.php
>>> 
>> 
>> 
>> Thank you Aziz,
>> Interesting link, thank you for that. I have not worked with prepared 
>> statements on my own, just in WordPress.
>> 
>> So the @ symbol is a preferred method even outside the SQL world because?
>> 
>> What specifically is the @ symbol doing?
>> 
>> From what I read, and from what you just mentioned,
>> it's the PHP->SQL driver that check this @ symbol and treats the data as 
>> literal text?
>> Meaning it will not execute the text that comes after the @ symbol as code.


> 
> If I understand correctly it is not the @ symbol itself which is the thing 
> you should be looking at.  What you should be looking at is how your 
> programming language handles prepared statements.  What I see is that the @ 
> symbol is how ASP.Net defines the variable name, and also the variable 
> position.
> 
> I am not sure about this, but it looks like PHP uses : for the same function.
> 
> I am even less sure about this, but I think with prepared statements you can 
> also define what "type" of data is being passed.  So if you try to pass a 
> "string" (ie. something that cannot be converted to a number) to a "number" 
> defined variable, you will get an error thrown.  If you use a catch statement 
> that error can be handled by your code, rather than PHP handling it in 
> default manner.
> 
> It really has been a long time since I have been hands on with any of this, 
> and there is a good chance at least some of what I am saying is wrong.
> 
> The point of prepared statements is that what variables you are passing 
> through them, they are passed as literal values, rather than simply putting 
> them through as straight text put into your string you are passing to SQL.
> 
> Even if the string ends up "breaking" your query in a way that can harm 
> either security of data, or your database itself (also a security issue), it 
> is not passed in a way that SQL handles as such.
> 
> I discovered an issue on one of the web apps I used where I would get a SQL 
> error message if I entered certain strings into the input field. Even though 
> what I was doing wasn't at all trying to test for it, my inputs made it clear 
> what was going on.
> 
> With that amount of "what is going on" figured out.  I could send a 
> meaningful bug report that got this issue fixed.  Most people using the site 
> would have had no idea what was happening.
> 
> If I recall, I was putting a " or ' in my input, thus closing the string, 
> which then left the rest being interpreted as SQL code.



Thanks Jigme,

Ok, so understand my own situation, the method I have been using, mysqli real 
escape string is suffice?
Or is the @ symbol is the better preferred method?

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com










--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Jigme Datse Yli-Rasku]

On 15/05/14 18:19 , Karl DeSaulniers wrote:

On May 14, 2015, at 8:09 PM, Aziz Saleh  wrote:




On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers  wrote:
Hello Everyone,
Have a quick question. Was reading some material and wanted some Players 
perspective.
I know w3schools is not the de-facto on everything, so I wanted to know how 
reliable is the information on this page.

http://www.w3schools.com/sql/sql_injection.asp

Namely the @ symbol before SQL Values and because this talks about SQL and not 
MySQL specifically, does this not apply to MySQL?
To my uneducated eyes it seems legit. Any clarification is greatly appreciated.

TIA,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



That is preferred in PHP as well. The SQL/MySQL isn't specifically doing the 
replacement, but rather the driver object. Using parametrized queries:

http://php.net/manual/en/pdo.prepared-statements.php




Thank you Aziz,
Interesting link, thank you for that. I have not worked with prepared 
statements on my own, just in WordPress.

So the @ symbol is a preferred method even outside the SQL world because?

What specifically is the @ symbol doing?

 From what I read, and from what you just mentioned,
it's the PHP->SQL driver that check this @ symbol and treats the data as 
literal text?
Meaning it will not execute the text that comes after the @ symbol as code.


If I understand correctly it is not the @ symbol itself which is the 
thing you should be looking at.  What you should be looking at is how 
your programming language handles prepared statements.  What I see is 
that the @ symbol is how ASP.Net defines the variable name, and also the 
variable position.


I am not sure about this, but it looks like PHP uses : for the same 
function.


I am even less sure about this, but I think with prepared statements you 
can also define what "type" of data is being passed.  So if you try to 
pass a "string" (ie. something that cannot be converted to a number) to 
a "number" defined variable, you will get an error thrown.  If you use a 
catch statement that error can be handled by your code, rather than PHP 
handling it in default manner.


It really has been a long time since I have been hands on with any of 
this, and there is a good chance at least some of what I am saying is wrong.


The point of prepared statements is that what variables you are passing 
through them, they are passed as literal values, rather than simply 
putting them through as straight text put into your string you are 
passing to SQL.


Even if the string ends up "breaking" your query in a way that can harm 
either security of data, or your database itself (also a security 
issue), it is not passed in a way that SQL handles as such.


I discovered an issue on one of the web apps I used where I would get a 
SQL error message if I entered certain strings into the input field. 
Even though what I was doing wasn't at all trying to test for it, my 
inputs made it clear what was going on.


With that amount of "what is going on" figured out.  I could send a 
meaningful bug report that got this issue fixed.  Most people using the 
site would have had no idea what was happening.


If I recall, I was putting a " or ' in my input, thus closing the 
string, which then left the rest being interpreted as SQL code.



--
Datse Multimedia Productions
http://DatseMultimedia.com/
Tel:250-362-5701
Mobile: 250-354-7094



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection

[Karl DeSaulniers]

On May 14, 2015, at 8:09 PM, Aziz Saleh  wrote:

> 
> 
> On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers  
> wrote:
> Hello Everyone,
> Have a quick question. Was reading some material and wanted some Players 
> perspective.
> I know w3schools is not the de-facto on everything, so I wanted to know how 
> reliable is the information on this page.
> 
> http://www.w3schools.com/sql/sql_injection.asp
> 
> Namely the @ symbol before SQL Values and because this talks about SQL and 
> not MySQL specifically, does this not apply to MySQL?
> To my uneducated eyes it seems legit. Any clarification is greatly 
> appreciated.
> 
> TIA,
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
> 
> 
> 
> That is preferred in PHP as well. The SQL/MySQL isn't specifically doing the 
> replacement, but rather the driver object. Using parametrized queries:
> 
> http://php.net/manual/en/pdo.prepared-statements.php  
> 


Thank you Aziz,
Interesting link, thank you for that. I have not worked with prepared 
statements on my own, just in WordPress.

So the @ symbol is a preferred method even outside the SQL world because?

What specifically is the @ symbol doing? 

From what I read, and from what you just mentioned,
it's the PHP->SQL driver that check this @ symbol and treats the data as 
literal text?
Meaning it will not execute the text that comes after the @ symbol as code.

Yes?

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com

Re: [PHP-DB] SQL Injection

[Aziz Saleh]

On Thu, May 14, 2015 at 9:05 PM, Karl DeSaulniers 
wrote:

> Hello Everyone,
> Have a quick question. Was reading some material and wanted some Players
> perspective.
> I know w3schools is not the de-facto on everything, so I wanted to know
> how reliable is the information on this page.
>
> http://www.w3schools.com/sql/sql_injection.asp
>
> Namely the @ symbol before SQL Values and because this talks about SQL and
> not MySQL specifically, does this not apply to MySQL?
> To my uneducated eyes it seems legit. Any clarification is greatly
> appreciated.
>
> TIA,
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
>
That is preferred in PHP as well. The SQL/MySQL isn't specifically doing
the replacement, but rather the driver object. Using parametrized queries:

http://php.net/manual/en/pdo.prepared-statements.php

[PHP-DB] SQL Injection

[Karl DeSaulniers]

Hello Everyone,
Have a quick question. Was reading some material and wanted some Players 
perspective.
I know w3schools is not the de-facto on everything, so I wanted to know how 
reliable is the information on this page.

http://www.w3schools.com/sql/sql_injection.asp

Namely the @ symbol before SQL Values and because this talks about SQL and not 
MySQL specifically, does this not apply to MySQL?
To my uneducated eyes it seems legit. Any clarification is greatly appreciated.

TIA,

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com

[PHP-DB] SQL injection attempt

[Lester Caine]

Been a while since I've had a concerted hacker attempt, but over night
this has appeared in the logs.

'sort_mode' => 'last_modified_desc\' and(/**/sElEcT 1
/**/fRoM(/**/sElEcT count(*),/**/cOnCaT((/**/sElEcT(/**/sElEcT
/**/uNhEx(/**/hEx(/**/cOnCaT(0x217e21,0x4142433134355a5136324457514146504f4959434644,0x217e21
/**/fRoM information_schema./**/tAbLeS /**/lImIt 0,1),floor(rand(0)*2))x
/**/fRoM information_schema./**/tAbLeS /**/gRoUp/**/bY x)a)

Does not get anywhere since 'sort_mode' gets filtered in this case to
LAST_MODIFIED DESC and the trash gets ignored. Presume this is some
MySQL hack attempt ( bit lost on Firebird anyway ;) ) but the question
as usual is it malicious in the content of MySQL, or just fishing?

In my case it just white screens anyway so I don't know why they keep
trying to send the same style of url thousands of times?

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL syntax

[Karl DeSaulniers]

On Jan 15, 2013, at 5:25 AM, Amit Tandon wrote:


SELECT orderid
   FROM ORDERS_TABLE
   WHERE orderstatus IN ( 'Cancelled', 'New'", 'Denied',
'Expired' , 'Failed' , 'Pending' , 'Refunded' , 'Reversed' , 'Under  
Review'

, 'Voided') AND orderdate < '".mysqli_real_escape_string($
yesterday);

Another option would be to use either  of these functions

  - Find-in-set.

  This is useful if your data type is SET/ENUM type
  - Field



On Tue, Jan 15, 2013 at 2:59 PM, Karl DeSaulniers >wrote:



SELECT orderid
   FROM ORDERS_TABLE
   WHERE orderstatus = 'Cancelled' OR (orderstatus =  
('New'

OR 'Denied' OR 'Expired' OR 'Failed' OR 'Pending' OR 'Refunded' OR
'Reversed' OR 'Under Review' OR 'Voided') AND orderdate <
'".mysqli_real_escape_string($**yesterday)."')";






regds
amit

"The difference between fiction and reality? Fiction has to make  
sense."



I am wanting Cancelled to be without a date check, but thanks for the  
suggestion.

I will try the IN option.

Thank you.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL syntax

[Amit Tandon]

SELECT orderid
FROM ORDERS_TABLE
WHERE orderstatus IN ( 'Cancelled', 'New'", 'Denied',
'Expired' , 'Failed' , 'Pending' , 'Refunded' , 'Reversed' , 'Under Review'
, 'Voided') AND orderdate < '".mysqli_real_escape_string($
yesterday);

Another option would be to use either  of these functions

   - 
Find-in-set.
   This is useful if your data type is SET/ENUM type
   - 
Field


On Tue, Jan 15, 2013 at 2:59 PM, Karl DeSaulniers wrote:

> SELECT orderid
> FROM ORDERS_TABLE
> WHERE orderstatus = 'Cancelled' OR (orderstatus = ('New'
> OR 'Denied' OR 'Expired' OR 'Failed' OR 'Pending' OR 'Refunded' OR
> 'Reversed' OR 'Under Review' OR 'Voided') AND orderdate <
> '".mysqli_real_escape_string($**yesterday)."')";
>




regds
amit

"The difference between fiction and reality? Fiction has to make sense."

[PHP-DB] SQL syntax

[Karl DeSaulniers]

Hello Everyone,
Hope your 2013 is treating you well.
Quick question and apologies if it is a stupid question.
Is this a viable syntax?

$sql = "SELECT orderid
FROM ORDERS_TABLE
	WHERE orderstatus = 'Cancelled' OR (orderstatus = ('New' OR  
'Denied' OR 'Expired' OR 'Failed' OR 'Pending' OR 'Refunded' OR  
'Reversed' OR 'Under Review' OR 'Voided') AND orderdate <  
'".mysqli_real_escape_string($yesterday)."')";


Namely the orderstatus = (a whole bunch of options).
In my database `orderstatus` field is an enum() list btw.
Or is there a better way to check multiple options against an enum  
inside your select statement?

Reason I am doing this is to avoid having to do...

$sql = "SELECT orderid
FROM ORDERS_TABLE
	WHERE orderstatus = 'Cancelled' OR (orderstatus = 'New'  AND  
orderdate < '".mysqli_real_escape_string($yesterday)."') OR  
(orderstatus = 'Denied'  AND orderdate <  
'".mysqli_real_escape_string($yesterday)."') OR (orderstatus =  
'Expired'  AND orderdate <  
'".mysqli_real_escape_string($yesterday)."') ... etc";


TIA,

Karl DeSaulniers
Design Drumm
http://designdrumm.com

Re: [PHP-DB] SQL omit record if ever had value set

[Dee Ayy]

On Mon, Jun 18, 2012 at 6:26 PM, Matijn Woudt  wrote:
> On Mon, Jun 18, 2012 at 11:56 PM, Dee Ayy  wrote:
>> I would like a query that lists records where a column has not taken
>> on a specific value when grouped by another column.
>>
>> N  V
>> n1 v1
>> n1 v2
>> n2 v1
>> n2 v2
>> n2 v3
>> n3 v1
>>
>> If v3 has ever been set for N, do not list N.  So the result would be n1, n3
>>
>> If v2 has ever been set for N, do not list N.  So the result would be only n3
>>
>> MSSQL
>>
>> TIA
>>
>
> I guess this should work in MSSQL too:
>
> SELECT N FROM table WHERE N NOT IN (SELECT N FROM table WHERE v = v3)
>
> - Matijn

Thank you.  So simple.  I must have had Monday afternoon burnout.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL omit record if ever had value set

[Matijn Woudt]

On Mon, Jun 18, 2012 at 11:56 PM, Dee Ayy  wrote:
> I would like a query that lists records where a column has not taken
> on a specific value when grouped by another column.
>
> N  V
> n1 v1
> n1 v2
> n2 v1
> n2 v2
> n2 v3
> n3 v1
>
> If v3 has ever been set for N, do not list N.  So the result would be n1, n3
>
> If v2 has ever been set for N, do not list N.  So the result would be only n3
>
> MSSQL
>
> TIA
>

I guess this should work in MSSQL too:

SELECT N FROM table WHERE N NOT IN (SELECT N FROM table WHERE v = v3)

- Matijn

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL omit record if ever had value set

[Dee Ayy]

I would like a query that lists records where a column has not taken
on a specific value when grouped by another column.

N  V
n1 v1
n1 v2
n2 v1
n2 v2
n2 v3
n3 v1

If v3 has ever been set for N, do not list N.  So the result would be n1, n3

If v2 has ever been set for N, do not list N.  So the result would be only n3

MSSQL

TIA

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL for counting comments - is this smart?

[Gerardo Benitez]

Chris your answer is the better solution, I thinked that the option suggest
by Martin was fine.

anywhere, tanks for help us to improve us code.

Gerardo.

2009/3/16 Chris 

> Martin Zvarík wrote:
>
>> Is it smart to use all of this on one page?
>> Or should I rather do one SQL and let PHP count it?
>>
>>
>> $q = $DB->q("SELECT COUNT(*) FROM comments");
>> $int_total = $DB->frow($q);
>>
>> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved IS NULL");
>> $int_waiting = $DB->frow($q);
>>
>> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=0");
>> $int_deleted = $DB->frow($q);
>>
>> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=1");
>> $int_approved = $DB->frow($q);
>>
>> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=2");
>> $int_banned = $DB->frow($q);
>>
>
> Each one of these probably going to scan the whole table because the
> approved column isn't going to be selective enough to use an index.
>
> You might be better off doing:
>
> select approved, count(*) from comments group by approved;
>
> then in php separating them out:
>
> while ($row = $DB->frow($q)) {
>  switch ($row['approved']) {
> case null:
>   $waiting = $row['count'];
> break;
> case 0:
>   $deleted = $row['count'];
> break;
> case 1:
>   $approved = $row['count'];
> break;
>  }
> }
>
> $total = $waiting + $approved + $deleted;
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] SQL for counting comments - is this smart?

[Chris]

Martin Zvarík wrote:

Is it smart to use all of this on one page?
Or should I rather do one SQL and let PHP count it?


$q = $DB->q("SELECT COUNT(*) FROM comments");
$int_total = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved IS NULL");
$int_waiting = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=0");
$int_deleted = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=1");
$int_approved = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=2");
$int_banned = $DB->frow($q);


Each one of these probably going to scan the whole table because the 
approved column isn't going to be selective enough to use an index.


You might be better off doing:

select approved, count(*) from comments group by approved;

then in php separating them out:

while ($row = $DB->frow($q)) {
  switch ($row['approved']) {
 case null:
   $waiting = $row['count'];
 break;
 case 0:
   $deleted = $row['count'];
 break;
 case 1:
   $approved = $row['count'];
 break;
  }
}

$total = $waiting + $approved + $deleted;

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL for counting comments - is this smart?

[Martin Zvarík]

Is it smart to use all of this on one page?
Or should I rather do one SQL and let PHP count it?


$q = $DB->q("SELECT COUNT(*) FROM comments");
$int_total = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved IS NULL");
$int_waiting = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=0");
$int_deleted = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=1");
$int_approved = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=2");
$int_banned = $DB->frow($q);


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql injections/best practises

[Christopher Jones]

mignon hunter wrote:
> Hi Christopher
>
> One other question. Our current site is written in jsp with
> Oracle. I'd like to use PHP. Do you have any thoughts on this?

My recommendation is to utilize the existing skills you have; this
echoes Fergus's comment.  However, PHP is very popular and if you have
the luxury of being able to learn a new language, choosing PHP is not
like choosing an esoteric language that someone will struggle to
maintain when you move on.

> We're not really using Jsp as it was intended ( like using classes )
> and I think it has alot of overhead and is overkill. It seems Php
> would be a better choice for imbedded html. For the most part the
> site mainly consist of relatively simple db retrieval, for several
> of our products. Which then lists various documentation and
> reference material for each, all dynamic. And then we have a few
> very simple stand alone user input forms occasionally.
>
> Oracle is the db on most of the site - a little mysql too.

PHP will certainly help you get a working website up quickly.  Oracle
can easily be accessed in PHP to do the things you describe.

Discussion of frameworks and abstraction layers is just a way to make
you aware of their place and to ensure the application is architected
to suit your current & future requirements.

Chris

--
Email: [EMAIL PROTECTED]  Tel: +1 650 506 8630
Twitter:  http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql injections/best practises

[mignon hunter]

Hi Christopher
 
One other question. Our current site is written in jsp with Oracle. I'd like to 
use PHP. Do you have any thoughts on this?
 
We're not really using Jsp as it was intended ( like using classes ) and I 
think it has alot of overhead and is overkill. It seems Php would be a better 
choice for imbedded html. For the most part the site mainly consist of 
relatively simple db retrieval, for several of our products. Which then lists 
various documentation and reference material for each, all dynamic. And then we 
have a few very simple stand alone user input forms occasionally.
 
Oracle is the db on most of the site - a little mysql too.


--- On Fri, 11/7/08, Christopher Jones <[EMAIL PROTECTED]> wrote:

From: Christopher Jones <[EMAIL PROTECTED]>
Subject: Re: [PHP-DB] sql injections/best practises
To: [EMAIL PROTECTED]
Cc: php-db@lists.php.net
Date: Friday, November 7, 2008, 5:39 PM

mignon hunter wrote:
> I'm am trying to find some definitive best practises on database
connections with php on both mysql and oracle.
>
> I'm starting to redesign a corporate website and am trying to find out
more about security and the best practises for database queries and user input
form handling.
>
> For example - what's the best usage - prepared statements? And does it
have to be php 5? I need preferably a one stop shop as opposed to looking at
dozens of different places. Can you advise a particular book? Website?
>
> I have checked out the security area on the php manual and some users
notes - some were useful. But it didnt really have a lot of info and I dont
think it is comprehenive or all inclusive.
>
> Thanks in advance. PS I would like to switch the current site from jsp to
php. I was going to look into Zend IDE. Comments? Suggestions?
>
> thanks


PHP 5.2 is the way to go for new projects: PHP 4 isn't being
maintained.

Binding/preparing statements is the way to go.  Here are quotes about
them with MySQL & Oracle

"They are useful for speeding up execution when you are performing
large numbers of the same query with different data.  They also
protect against SQL injection-style attacks."  (From "PHP and
MySQL Web Development", 4th Edition, Luke Welling and Laura
Thomson)

"If I were to write a book about how to build nonscalable [note
the NON] Oracle applications, then 'Don't Use Bind Variables'
would be the title of the first and last chapters. [...] If you
want to make Oracle run slowly [...] just refuse to use bind
variables" (From "Expert Oracle Database Architecture", Tom
Kyte)

Depending on the site needs, consider a DB abstraction layer or a
framework.

For high performance connections in PHP OCI8 for Oracle, use
oci_pconnect() and pass the character set.

There are a number of Oracle-PHP books available.  One free,
introductory one is the "Underground PHP & Oracle Manual",
http://tinyurl.com/f8jad (A new edition will be released in the next
couple of weeks)

Chris

-- Email: [EMAIL PROTECTED]  Tel: +1 650 506 8630
Twitter:  http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad

-- PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




  

Re: [PHP-DB] sql injections/best practises

[Fergus Gibson]

On Mon, Nov 10, 2008 at 8:49 AM, mignon hunter <[EMAIL PROTECTED]> wrote:
> One other question. Our current site is written in jsp with Oracle. I'd like 
> to use PHP. Do you have any thoughts on this?

Your post, mignon, was pretty clearly directed to Christopher, but I
hope neither of you will be unhappy if I make my own comment.  I look
forward to seeing what Christopher thinks as well, but I do have some
thoughts on this.

First, I will say that I have never worked with JSP.  I have worked
with Java, and I rather enjoy it, but I have not travelled down any of
the JSP trail.  For that reason, I'm not going to attempt to assess
JSP specifically, other than to say that it seems rather more
time-consuming to learn than PHP.

There is a simple reason that I choose PHP exclusively for web
development.  It has nothing to do with any intrinsic value of PHP.  I
actually think PHP is the worst designed platform I've ever worked
with.  There is so much about it I really don't like.  But it has one
overriding strength: it's everywhere.  It's simple to integrate with
any server, it's widely available, and hosting for other technologies
like JSP, Ruby, and Python tends to be more expensive than PHP.  And
don't even get me started on ASP and ASP.Net, which are only truly
supported on (shudder) MS servers.

Now all of this said, I am leery of your idea to switch to PHP for
this application you are working on.  If you're not planning to move
servers, the wider support for PHP isn't an advantage at all.
Switching technologies because you think PHP is somehow cooler is a
poor justification for what could be a costly exercise for the client,
so you ought to have a really practical explanation.  Why don't you
want to continue working with JSP?  Is there really a problem in using
that platform?


> We're not really using Jsp as it was intended ( like using classes ) and I 
> think it has alot of overhead and is overkill. It seems Php would
> be a better choice for imbedded html.

This is probably true, but I am no authority on JSP.  But in most
cases, embedding PHP in HTML is not a best practice.  Most
professionals agree that there is a real value in separating
presentation (HTML in this case) from logic (the PHP code).  This is
something all the frameworks attempt to help you do.  This same idea
was the impetus for the rise of model-view-controller (MVC) design
pattern, its later application to web projects, and the development of
Cascading Stylesheets (CSS) to separate presentation of web content
from structure (HTML).

As I mentioned previously, I am a little reluctant to use frameworks.
For this reason I have implemented my own simple template script that
allows me to put all my PHP logic in one file and all my presentation
in a separate template file.  My approach is similar to that of Brian
Lozer.  Brian is the author of bTemplate, but abandoned its
development when he hit upon the real weakness in template engines and
frameworks.  Here's a link to his article on the subject.  I'm not
suggesting anyone use bTemplate; I'm encouraging people to understand
why he decided it wasn't productive to use a convention template
engine in the first place.

http://massassi.com/php/articles/template_engines/

All the above notwithstanding, there's always an argument for saying a
script is so simple the benefits of abstraction or design patterns or
MVC are not of much value.  But I think there are very few
applications where this is true, and worst of all, I tend to find that
even if you start off thinking that something is so simple and
straightforward you should just bang it out in the most simple and
direct way, you'll end up regretting it because the concept wasn't as
simple as you thought or because the client keeps adding on to his
original goal for the script 'til it becomes a messy monster you
wished you designed properly in the first place...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql injections/best practises

[mignon hunter]

thank you so much Fergus for all this great info - this will get me started. 

--- On Sat, 11/8/08, Fergus Gibson <[EMAIL PROTECTED]> wrote:

From: Fergus Gibson <[EMAIL PROTECTED]>
Subject: Re: [PHP-DB] sql injections/best practises
To: php-db@lists.php.net
Date: Saturday, November 8, 2008, 12:42 PM

On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones
<[EMAIL PROTECTED]> wrote:
>
> mignon hunter wrote:
>> I'm am trying to find some definitive best practises on database
>> connections with php on both mysql and oracle.

Most security issues come back to a simple concept.  Assume anything
in your scripts that is not a constant or literal to be a threat.
That means any and all user submitted data is a potential attack.
Ideally you should also assume that any and all data read in from the
database or files is a potential attack.  Assume everything is
"tainted".  Your job then is to "clean" any and all input
through
inspection and filtering before you use it.

I recommend the book "Essential PHP Security" by Chris Shiflett (ISBN
0-596-00656-X).  It deals with database security and more.

I would be happy to go into more detail on this or provide examples if
it would be helpful.


>> For example - what's the best usage - prepared statements? And
does it
>> have to be php 5? I need preferably a one stop shop as opposed to
looking at
>> dozens of different places. Can you advise a particular book? Website?

Prepared statements will prevent SQL injection, but that is only one
potential vector for attack.  Keep in mind too that prepared
statements are not necessary to prevent SQL injection and they aren't
always the most appropriate way to do it.  That said, they are the
simplest way to protect your database.

I'll outline a way that a database was used to attack an application.
The attack wasn't particularly dangerous, but it was embarrassing for
the company involved.  In this case, the application took form input
from a site visitor and saved it in the database.  Then the site owner
could retrieve the input and view it.  Unfortunately, some visitors
decided to put 

Re: [PHP-DB] sql injections/best practises

[mignon hunter]

Thank you Christopher - this gives me some much needed direction.

--- On Fri, 11/7/08, Christopher Jones <[EMAIL PROTECTED]> wrote:

From: Christopher Jones <[EMAIL PROTECTED]>
Subject: Re: [PHP-DB] sql injections/best practises
To: [EMAIL PROTECTED]
Cc: php-db@lists.php.net
Date: Friday, November 7, 2008, 5:39 PM

mignon hunter wrote:
> I'm am trying to find some definitive best practises on database
connections with php on both mysql and oracle.
>
> I'm starting to redesign a corporate website and am trying to find out
more about security and the best practises for database queries and user input
form handling.
>
> For example - what's the best usage - prepared statements? And does it
have to be php 5? I need preferably a one stop shop as opposed to looking at
dozens of different places. Can you advise a particular book? Website?
>
> I have checked out the security area on the php manual and some users
notes - some were useful. But it didnt really have a lot of info and I dont
think it is comprehenive or all inclusive.
>
> Thanks in advance. PS I would like to switch the current site from jsp to
php. I was going to look into Zend IDE. Comments? Suggestions?
>
> thanks


PHP 5.2 is the way to go for new projects: PHP 4 isn't being
maintained.

Binding/preparing statements is the way to go.  Here are quotes about
them with MySQL & Oracle

"They are useful for speeding up execution when you are performing
large numbers of the same query with different data.  They also
protect against SQL injection-style attacks."  (From "PHP and
MySQL Web Development", 4th Edition, Luke Welling and Laura
Thomson)

"If I were to write a book about how to build nonscalable [note
the NON] Oracle applications, then 'Don't Use Bind Variables'
would be the title of the first and last chapters. [...] If you
want to make Oracle run slowly [...] just refuse to use bind
variables" (From "Expert Oracle Database Architecture", Tom
Kyte)

Depending on the site needs, consider a DB abstraction layer or a
framework.

For high performance connections in PHP OCI8 for Oracle, use
oci_pconnect() and pass the character set.

There are a number of Oracle-PHP books available.  One free,
introductory one is the "Underground PHP & Oracle Manual",
http://tinyurl.com/f8jad (A new edition will be released in the next
couple of weeks)

Chris

-- Email: [EMAIL PROTECTED]  Tel: +1 650 506 8630
Twitter:  http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad



  

[PHP-DB] SQL Server Express 2008

[Yves Sucaet]

Hello,

Anybody knows how to make PHP connect to Microsoft SQL Server Express 2008?

I know about the php_mssql extension, but that's apparently only the beginning. 
What else do I need to do, assuming I have a standard SQLServer setup...

Thanks in advance,

Yves

Re: [PHP-DB] sql injections/best practises

[Fergus Gibson]

On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones
<[EMAIL PROTECTED]> wrote:
>
> mignon hunter wrote:
>> I'm am trying to find some definitive best practises on database
>> connections with php on both mysql and oracle.

Most security issues come back to a simple concept.  Assume anything
in your scripts that is not a constant or literal to be a threat.
That means any and all user submitted data is a potential attack.
Ideally you should also assume that any and all data read in from the
database or files is a potential attack.  Assume everything is
"tainted".  Your job then is to "clean" any and all input through
inspection and filtering before you use it.

I recommend the book "Essential PHP Security" by Chris Shiflett (ISBN
0-596-00656-X).  It deals with database security and more.

I would be happy to go into more detail on this or provide examples if
it would be helpful.


>> For example - what's the best usage - prepared statements? And does it
>> have to be php 5? I need preferably a one stop shop as opposed to looking at
>> dozens of different places. Can you advise a particular book? Website?

Prepared statements will prevent SQL injection, but that is only one
potential vector for attack.  Keep in mind too that prepared
statements are not necessary to prevent SQL injection and they aren't
always the most appropriate way to do it.  That said, they are the
simplest way to protect your database.

I'll outline a way that a database was used to attack an application.
The attack wasn't particularly dangerous, but it was embarrassing for
the company involved.  In this case, the application took form input
from a site visitor and saved it in the database.  Then the site owner
could retrieve the input and view it.  Unfortunately, some visitors
decided to put 

Re: [PHP-DB] sql injections/best practises

[Christopher Jones]

mignon hunter wrote:
> I'm am trying to find some definitive best practises on database connections 
with php on both mysql and oracle.
>
> I'm starting to redesign a corporate website and am trying to find out more 
about security and the best practises for database queries and user input form 
handling.
>
> For example - what's the best usage - prepared statements? And does it have 
to be php 5? I need preferably a one stop shop as opposed to looking at dozens of 
different places. Can you advise a particular book? Website?
>
> I have checked out the security area on the php manual and some users notes - 
some were useful. But it didnt really have a lot of info and I dont think it is 
comprehenive or all inclusive.
>
> Thanks in advance. PS I would like to switch the current site from jsp to 
php. I was going to look into Zend IDE. Comments? Suggestions?
>
> thanks


PHP 5.2 is the way to go for new projects: PHP 4 isn't being
maintained.

Binding/preparing statements is the way to go.  Here are quotes about
them with MySQL & Oracle

"They are useful for speeding up execution when you are performing
large numbers of the same query with different data.  They also
protect against SQL injection-style attacks."  (From "PHP and
MySQL Web Development", 4th Edition, Luke Welling and Laura
Thomson)

"If I were to write a book about how to build nonscalable [note
the NON] Oracle applications, then 'Don't Use Bind Variables'
would be the title of the first and last chapters. [...] If you
want to make Oracle run slowly [...] just refuse to use bind
variables" (From "Expert Oracle Database Architecture", Tom Kyte)

Depending on the site needs, consider a DB abstraction layer or a
framework.

For high performance connections in PHP OCI8 for Oracle, use
oci_pconnect() and pass the character set.

There are a number of Oracle-PHP books available.  One free,
introductory one is the "Underground PHP & Oracle Manual",
http://tinyurl.com/f8jad (A new edition will be released in the next
couple of weeks)

Chris

--
Email: [EMAIL PROTECTED]  Tel: +1 650 506 8630
Twitter:  http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] sql injections/best practises

[mignon hunter]

I'm am trying to find some definitive best practises on database connections 
with php on both mysql and oracle. 
 
I'm starting to redesign a corporate website and am trying to find out more 
about security and the best practises for database queries and user input form 
handling.
 
For example - what's the best usage - prepared statements? And does it have to 
be php 5? I need preferably a one stop shop as opposed to looking at dozens of 
different places. Can you advise a particular book? Website?
 
I have checked out the security area on the php manual and some users notes - 
some were useful. But it didnt really have a lot of info and I dont think it is 
comprehenive or all inclusive.
 
Thanks in advance. PS I would like to switch the current site from jsp to php. 
I was going to look into Zend IDE. Comments? Suggestions?
 
thanks


  

RE: [PHP-DB] sql problem

[Bastien Koert]

http://www.php.net/manual/en/function.number-format.php

bastien




> Date: Sun, 16 Dec 2007 17:17:41 +0600
> From: [EMAIL PROTECTED]
> To: php-db@lists.php.net
> Subject: [PHP-DB] sql problem
> 
> my problem in the  following code
> 
> INSERT INTO `test` ( `debit` )
> VALUES (
> '2'
> )
> when i search it shows like this:
> 
> 
> SELECT  debit  FROM `test`
> 
> output is :2.
> 
> but
> i have to show
> 
> output :20,000.00
> 
> 
> like
> input 2
> output 20,000.00
> input 3000
> output 3,000.00
> input 10
> output 1,0.00

_
Read what Santa`s been up to! For all the latest, visit 
asksantaclaus.spaces.live.com!
http://asksantaclaus.spaces.live.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] sql problem

[arafat uddin]

my problem in the  following code

INSERT INTO `test` ( `debit` )
VALUES (
'2'
)
when i search it shows like this:


SELECT  debit  FROM `test`

output is :2.

but
i have to show

output :20,000.00


like
input 2
output 20,000.00
input 3000
output 3,000.00
input 10
output 1,0.00

Re: [PHP-DB] sql statement - complex order by

[Chris]

Bryan wrote:

SELECT * FROM productgroup WHERE groupid = $productid
AND label =  'Cats' ORDER BY title

SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

I'd like to find a way to combine these 2 statements. I want to list out 
all the products, ordered by title but listing out all the Cats products 
first. Any way to do that without having separate statements?


select *, case when label='cats' then 1 else 2 end as order_by where 
groupid=$productid order by order_by, title;


See http://www.databasejournal.com/features/mssql/article.php/3288921 
for a better example.


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql statement - complex order by

[David Mitchell]

How about a union?

SELECT * FROM productgroup WHERE groupid = $productid
AND label =  'Cats' ORDER BY title
UNION
SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

Also, for long-term maintenance, it would probably be better to list the
columns rather than SELECT *.  Someone adds one column to your table and
whamo.  Things break.

Bonne chance.


On 7/2/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]>
wrote:


Yeah, that's a bit of an important piece of information.  Some tricks do
work across versions of SQL, but not always.

Something else you can try is creating an artificial column to sort
by.  Excuse the code, it's been ages since I've worked with MS SQL so syntax
is probably off, but just to demonstrate the point:

SELECT * FROM productgroup WHERE groupid = $productid
ORDER BY IIF(label = 'Cats', 0, 1), title

Was that MS SQL or just Access that used IIF?  (immediate IF.. wtf is an
'immediate IF' anyway?)

-TG


= = = Original message = = =

I think there's one small piece of data I left out. I'm working with
php/mssql, no mysql. I'll move to mysql when I get everything else
built. Mssql 2000 doesn't seem to like the = sign in the order by
clause. It looks like both of you so far have come up with the same
syntax though so it must work on mysql. ;-)

Thanks guys...

[EMAIL PROTECTED] wrote:
> Try this:
>
> SELECT * FROM productgroup WHERE groupid = $productid
> ORDER BY label = 'Cats' DESC, title
>
> The test SQL I did to make sure I understood it was this (against our
Users table):
>
> select * from users order by first = 'Bob' DESC, first, last
>
> It put all the "Bob"s first, sorting them by first/last, then put
everyone else after the "Bob"s sorted by first/last.
>
> If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats'
at the bottom of the list.
>
> Also refer to the user comments here:
> http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html
>
> good luck!
>
> -TG
>
> = = = Original message = = =
>
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label =  'Cats' ORDER BY title
>
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label != 'Cats' ORDER BY label,title
>
> I'd like to find a way to combine these 2 statements. I want to list out
> all the products, ordered by title but listing out all the Cats products
> first. Any way to do that without having separate statements?
>
> Thanks...
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql statement - complex order by

[tg-php]

Yeah, that's a bit of an important piece of information.  Some tricks do work 
across versions of SQL, but not always.

Something else you can try is creating an artificial column to sort by.  Excuse 
the code, it's been ages since I've worked with MS SQL so syntax is probably 
off, but just to demonstrate the point:

SELECT * FROM productgroup WHERE groupid = $productid
ORDER BY IIF(label = 'Cats', 0, 1), title

Was that MS SQL or just Access that used IIF?  (immediate IF.. wtf is an 
'immediate IF' anyway?)

-TG


= = = Original message = = =

I think there's one small piece of data I left out. I'm working with 
php/mssql, no mysql. I'll move to mysql when I get everything else 
built. Mssql 2000 doesn't seem to like the = sign in the order by 
clause. It looks like both of you so far have come up with the same 
syntax though so it must work on mysql. ;-)

Thanks guys...

[EMAIL PROTECTED] wrote:
> Try this:
> 
> SELECT * FROM productgroup WHERE groupid = $productid
> ORDER BY label = 'Cats' DESC, title
> 
> The test SQL I did to make sure I understood it was this (against our Users 
> table):
> 
> select * from users order by first = 'Bob' DESC, first, last
> 
> It put all the "Bob"s first, sorting them by first/last, then put everyone 
> else after the "Bob"s sorted by first/last.
> 
> If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at 
> the bottom of the list.
> 
> Also refer to the user comments here:
> http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html
> 
> good luck!
> 
> -TG
> 
> = = = Original message = = =
> 
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label =  'Cats' ORDER BY title
> 
> SELECT * FROM productgroup WHERE groupid = $productid
> AND label != 'Cats' ORDER BY label,title
> 
> I'd like to find a way to combine these 2 statements. I want to list out 
> all the products, ordered by title but listing out all the Cats products 
> first. Any way to do that without having separate statements?
> 
> Thanks...
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql statement - complex order by

[Bryan]

I think there's one small piece of data I left out. I'm working with 
php/mssql, not mysql. I'll move to mysql when I get everything else 
built. Mssql 2000 doesn't seem to like the = sign in the order by 
clause. It looks like both of you so far have come up with the same 
syntax though so it must work on mysql. ;-)


Thanks guys...

[EMAIL PROTECTED] wrote:

Try this:

SELECT * FROM productgroup WHERE groupid = $productid
ORDER BY label = 'Cats' DESC, title

The test SQL I did to make sure I understood it was this (against our Users 
table):

select * from users order by first = 'Bob' DESC, first, last

It put all the "Bob"s first, sorting them by first/last, then put everyone else after the 
"Bob"s sorted by first/last.

If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the 
bottom of the list.

Also refer to the user comments here:
http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html

good luck!

-TG

= = = Original message = = =

SELECT * FROM productgroup WHERE groupid = $productid
AND label =  'Cats' ORDER BY title

SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

I'd like to find a way to combine these 2 statements. I want to list out 
all the products, ordered by title but listing out all the Cats products 
first. Any way to do that without having separate statements?


Thanks...



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql statement - complex order by

[Bryan]

I think there's one small piece of data I left out. I'm working with 
php/mssql, no mysql. I'll move to mysql when I get everything else 
built. Mssql 2000 doesn't seem to like the = sign in the order by 
clause. It looks like both of you so far have come up with the same 
syntax though so it must work on mysql. ;-)


Thanks guys...

[EMAIL PROTECTED] wrote:

Try this:

SELECT * FROM productgroup WHERE groupid = $productid
ORDER BY label = 'Cats' DESC, title

The test SQL I did to make sure I understood it was this (against our Users 
table):

select * from users order by first = 'Bob' DESC, first, last

It put all the "Bob"s first, sorting them by first/last, then put everyone else after the 
"Bob"s sorted by first/last.

If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the 
bottom of the list.

Also refer to the user comments here:
http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html

good luck!

-TG

= = = Original message = = =

SELECT * FROM productgroup WHERE groupid = $productid
AND label =  'Cats' ORDER BY title

SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

I'd like to find a way to combine these 2 statements. I want to list out 
all the products, ordered by title but listing out all the Cats products 
first. Any way to do that without having separate statements?


Thanks...



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql statement - complex order by

[tg-php]

Try this:

SELECT * FROM productgroup WHERE groupid = $productid
ORDER BY label = 'Cats' DESC, title

The test SQL I did to make sure I understood it was this (against our Users 
table):

select * from users order by first = 'Bob' DESC, first, last

It put all the "Bob"s first, sorting them by first/last, then put everyone else 
after the "Bob"s sorted by first/last.

If you don't put DESC on the 'Cats', it looks like it'll put the 'Cats' at the 
bottom of the list.

Also refer to the user comments here:
http://dev.mysql.com/doc/refman/4.1/en/sorting-rows.html

good luck!

-TG

= = = Original message = = =

SELECT * FROM productgroup WHERE groupid = $productid
AND label =  'Cats' ORDER BY title

SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

I'd like to find a way to combine these 2 statements. I want to list out 
all the products, ordered by title but listing out all the Cats products 
first. Any way to do that without having separate statements?

Thanks...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql statement - complex order by

[Stut]

Bryan wrote:

SELECT * FROM productgroup WHERE groupid = $productid
AND label =  'Cats' ORDER BY title

SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

I'd like to find a way to combine these 2 statements. I want to list out 
all the products, ordered by title but listing out all the Cats products 
first. Any way to do that without having separate statements?


Thanks...


select * from productgroup where groupid = $productid order by (label = 
'Cats') desc, title


And I do hope you're properly validating and escaping $productid.

-Stut

--
http://stut.net/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] sql statement - complex order by

[Bryan]

SELECT * FROM productgroup WHERE groupid = $productid
AND label =  'Cats' ORDER BY title

SELECT * FROM productgroup WHERE groupid = $productid
AND label != 'Cats' ORDER BY label,title

I'd like to find a way to combine these 2 statements. I want to list out 
all the products, ordered by title but listing out all the Cats products 
first. Any way to do that without having separate statements?


Thanks...

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL unexpected T_CONSTANT_ENCAPSED_STRING Error

[Jean-Marc Guillermin]

Hi Laitha,

And with backslashes before them ??

jm

- Original Message - 
From: "Lasitha Alawatta" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 22, 2007 2:06 PM
Subject: [PHP-DB] SQL unexpected T_CONSTANT_ENCAPSED_STRING Error



Hi All,

 I have a sql script unable to execute. Because I need to insert 
double-coats ( " ) also to one field(SPParams field).


 Ones I execuit this using mysql_query($sql), it gives "Parse error: parse 
error, unexpected T_CONSTANT_ENCAPSED_STRING " error message.



$strSql = "";
  $strSql = $strSql."UPDATE Masters.tblBranches SET FHeadMail= 
'[EMAIL PROTECTED];', ";
  $strSql = $strSql."BranchDBConnection='Server=SLBC05; 
Database=BackOffice; UID=b0;pwd=sSLap.+05);', ";
  $strSql = $strSql."SPParams='t="VarChar">CustomerNamet="TinyInt">CustomerType' ";

  $strSql = $strSql."WHERE BranchCode=101";

 How can I be able to solve this issue..?


 Thank you very much,
 Lasitha


-
What kind of emailer are you? Find out today - get a free analysis of your 
email personality. Take the quiz at the Yahoo! Mail Championship. 


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL unexpected T_CONSTANT_ENCAPSED_STRING Error

[Lasitha Alawatta]

Hi All,
   
  I have a sql script unable to execute. Because I need to insert double-coats 
( " ) also to one field(SPParams field).
   
  Ones I execuit this using mysql_query($sql), it gives "Parse error: parse 
error, unexpected T_CONSTANT_ENCAPSED_STRING " error message.
   
   
 $strSql = "";
   $strSql = $strSql."UPDATE Masters.tblBranches SET FHeadMail= '[EMAIL 
PROTECTED];', ";
   $strSql = $strSql."BranchDBConnection='Server=SLBC05; Database=BackOffice; 
UID=b0;pwd=sSLap.+05);', ";
   $strSql = $strSql."SPParams='CustomerNameCustomerType' ";   
   $strSql = $strSql."WHERE BranchCode=101";
   
  How can I be able to solve this issue..?
   
   
  Thank you very much,
  Lasitha


-
 What kind of emailer are you? Find out today - get a free analysis of your 
email personality. Take the quiz at the Yahoo! Mail Championship.

Re: [PHP-DB] SQL Query - Using variable from another SQL Query

[Matthew Ferry]

Thanks Everyone...

After I sent that...I got thinking about doing both queries in one statement.
So thats what I did.

Its working fine...

Here is the updated code: 

 '$tstamp' and 
egw_cal.cal_id=egw_cal_dates.cal_id", $db);



if ($event = mysql_fetch_array($events)) {

echo "\n";

echo "\n";

do {

echo "$event[cal_title]-   $event[cal_location]\n";

echo "\n";

$start = date('F jS\, Y \a\t g:ia', $event[cal_start]);

echo "Starting Date/Time:   $start";

echo "\n";

echo "\n";

echo "$event[cal_description]";

echo "\n";

echo "\n";

} while ($event = mysql_fetch_array($events));

} else {

echo "No Public Events Are Currently Scheduled...";

}

?>

- Original Message - 
From: "Matthew Ferry" <[EMAIL PROTECTED]>
To: 
Sent: Monday, February 12, 2007 11:14 AM
Subject: [PHP-DB] SQL Query - Using variable from another SQL Query


Hello Everyone

Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.

The query "event_time" brings back the calendar id for each event that is 
pending in the future.
ie 12, 13, 14, 26  (There could be 100 of them out there)

The second query "events" needs to meet both reqirements.  
 1 - cal_category='501' 
 2 - cal_id= a number from the "event_time" query

I think i need to do a loop inside of a loop

Thanks...

Matt 


Here is my code: 

 
$tstamp", $db);

$events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and 
cal_id='$event_time'\n", $db);



if ($event = mysql_fetch_array($events)) {

echo "\n";

echo "\n";

do {

echo "$event[cal_title]-   $event[cal_location]\n";

echo "\n";

echo "$event[cal_description]";

echo "\n";

echo "\n";

} while ($event = mysql_fetch_array($events));

} else {

echo "No Public Events Are Currently Scheduled...";

}

?>

Re: [PHP-DB] SQL Query - Using variable from another SQL Query

[Micah Stevens]

This is a join - Read up on them, they're very useful and don't require 
the overhead of a sub-query.



SELECT egw_cal.* FROM egw_cal_dates
LEFT JOIN egw_cal using (cal_id)
 where egw_cal_dates.cal_start > $tstamp
 AND egw_cal.cal_category = '501'



-Micah


On 02/12/2007 08:14 AM, Matthew Ferry wrote:

Hello Everyone

Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.

The query "event_time" brings back the calendar id for each event that is 
pending in the future.
ie 12, 13, 14, 26  (There could be 100 of them out there)

The second query "events" needs to meet both reqirements.  
 1 - cal_category='501' 
 2 - cal_id= a number from the "event_time" query


I think i need to do a loop inside of a loop

Thanks...

Matt 



Here is my code: 


 
$tstamp", $db);

$events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and 
cal_id='$event_time'\n", $db);



if ($event = mysql_fetch_array($events)) {

echo "\n";

echo "\n";

do {

echo "$event[cal_title]-   $event[cal_location]\n";

echo "\n";

echo "$event[cal_description]";

echo "\n";

echo "\n";

} while ($event = mysql_fetch_array($events));

} else {

echo "No Public Events Are Currently Scheduled...";

}

?>


  


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Query - Using variable from another SQL Query

[tg-php]

Try this as your SQL. It should give you all the results, then you can use PHP 
to sort it all out.

SELECT * FROM egw_cal WHERE cal_category='501' and cal_id in (SELECT cal_id 
FROM egw_cal_dates where cal_start > $tstamp)

-TG



= = = Original message = = =

Hello Everyone

Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.

The query "event_time" brings back the calendar id for each event that is 
pending in the future.
ie 12, 13, 14, 26  (There could be 100 of them out there)

The second query "events" needs to meet both reqirements.  
 1 - cal_category='501' 
 2 - cal_id= a number from the "event_time" query

I think i need to do a loop inside of a loop

Thanks...

Matt 


Here is my code: 

 
$tstamp", $db);

$events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and 
cal_id='$event_time'\n", $db);



if ($event = mysql_fetch_array($events)) 

echo "\n";

echo "\n";

do 

echo "$event[cal_title]-   $event[cal_location]\n";

echo "\n";

echo "$event[cal_description]";

echo "\n";

echo "\n";

 while ($event = mysql_fetch_array($events));

 else 

echo "No Public Events Are Currently Scheduled...";



?>


___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Query - Using variable from another SQL Query

[Brad Bonkoski]

Matthew Ferry wrote:

Hello Everyone

Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.

The query "event_time" brings back the calendar id for each event that is 
pending in the future.
ie 12, 13, 14, 26  (There could be 100 of them out there)

The second query "events" needs to meet both reqirements.  
 1 - cal_category='501' 
 2 - cal_id= a number from the "event_time" query


I think i need to do a loop inside of a loop

Thanks...

Matt 



Here is my code: 


 
$tstamp", $db);
  

This returns a mysql result set...not the actual data...
search php.net for the function mysql_fetch_array or others to actually 
*get* the data.

(Some good examples there will help you sort this out!)


$events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and 
cal_id='$event_time'\n", $db);



if ($event = mysql_fetch_array($events)) {

echo "\n";

echo "\n";

do {

echo "$event[cal_title]-   $event[cal_location]\n";

echo "\n";

echo "$event[cal_description]";

echo "\n";

echo "\n";

} while ($event = mysql_fetch_array($events));

} else {

echo "No Public Events Are Currently Scheduled...";

}

?>


  


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL Query - Using variable from another SQL Query

[Matthew Ferry]

Hello Everyone

Got a simple / stupid question.
Worked on this all night. I'm over looking something very basic here.

The query "event_time" brings back the calendar id for each event that is 
pending in the future.
ie 12, 13, 14, 26  (There could be 100 of them out there)

The second query "events" needs to meet both reqirements.  
 1 - cal_category='501' 
 2 - cal_id= a number from the "event_time" query

I think i need to do a loop inside of a loop

Thanks...

Matt 


Here is my code: 

 
$tstamp", $db);

$events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and 
cal_id='$event_time'\n", $db);



if ($event = mysql_fetch_array($events)) {

echo "\n";

echo "\n";

do {

echo "$event[cal_title]-   $event[cal_location]\n";

echo "\n";

echo "$event[cal_description]";

echo "\n";

echo "\n";

} while ($event = mysql_fetch_array($events));

} else {

echo "No Public Events Are Currently Scheduled...";

}

?>

Re: [PHP-DB] SQL Performance Help

[Chris]

Tony Grimes wrote:

I'm developing a course calendar for a client and I'm running into
performance problems with the admin site. For example, when I try to include
registration counts in the course list, the page really slows down for large
course lists (50 or so):

COURSEATTENDEES  CAPACITYSEATS LEFT
===  ==
Course 1 5  10   5
Course 2 6  15   9
Course 3 4  10   6

I've been using one query to retrieve the course list and then one for each
attendee count. Is there a more efficient way of doing this all in one
query? I was thinking something like this (I'm not a SQL expert, so I don't
know if this is even possible):

SELECT
course_name,
capacity,
count(query here) as attendee_count
FROM events AS e
LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id
WHERE start_time BETWEEN point_a AND point_b

Or should I just pull everything as a separate row like this and sort it all
out programmatically:

SELECT
e.course_name,
e.capacity,
a.user_id
FROM events AS e
LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id
WHERE start_time BETWEEN point_a AND point_b

Or should I just try caching the data in PHP? Would an index help?


Index your tables, make the database do the work. Much easier and less 
prone to bugs :)


Check you have an index on:

events(event_id)
event_attendees(event_id)

table(start_time) (whichever that table applies to - I assume it's events).

Maybe try a multi-column index if this query gets run a lot:

create index event_eventid_start_time on events(event_id, start_time);

Use 'explain' to see which one is being used and possibly get rid of the 
other one.



I have a guide about how to index databases here:

http://www.designmagick.com/article/16/

(Yes it's a postgresql site but the same rules apply to mysql and other 
databases as well).


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL Performance Help

[Bastien Koert]

1. yes indexes could help, if mysql uses them. The mysql optimiser may or 
may not use the index for the query depending on the statement...it sounds 
like you are doing a full table scan on the data


2. there are two schools of thought here:
a. run the whole thing as two statements (one outer loop to loop thru the 
course list, and an inner one to get the attendees)
b. examine the join query to see if you are starting out in the correct way 
(mysql can be sensitive to the way the tables are joined), examine the 
indexes and use the EXPLAIN statement to see how the optimiser attempts the 
query.


I would just test both ways and see which one performs better...

Bastien



From: Tony Grimes <[EMAIL PROTECTED]>
To: PHP-DB 
Subject: [PHP-DB] SQL Performance Help
Date: Wed, 27 Dec 2006 14:05:13 -0700

I'm developing a course calendar for a client and I'm running into
performance problems with the admin site. For example, when I try to 
include
registration counts in the course list, the page really slows down for 
large

course lists (50 or so):

COURSEATTENDEES  CAPACITYSEATS LEFT
===  ==
Course 1 5  10   5
Course 2 6  15   9
Course 3 4  10   6

I've been using one query to retrieve the course list and then one for each
attendee count. Is there a more efficient way of doing this all in one
query? I was thinking something like this (I'm not a SQL expert, so I don't
know if this is even possible):

SELECT
course_name,
capacity,
count(query here) as attendee_count
FROM events AS e
LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id
WHERE start_time BETWEEN point_a AND point_b

Or should I just pull everything as a separate row like this and sort it 
all

out programmatically:

SELECT
e.course_name,
e.capacity,
a.user_id
FROM events AS e
LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id
WHERE start_time BETWEEN point_a AND point_b

Or should I just try caching the data in PHP? Would an index help?

I realize any answers might be complicated, but if you could just point me
in the right direction, I can probably figure the rest out.

Thanks,
Tony

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



_
Enter the "Telus Mobility Xbox a Day" contest for your chance to WIN!  Telus 
Mobility is giving away an Microsoft Xbox® 360 every day from November 20 to 
December 31, 2006! Just download Windows Live (MSN) Messenger to your 
IM-capable TELUS mobile phone, and you could be a winner!  
http://www.telusmobility.com/msnxbox/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL Performance Help

[Tony Grimes]

I'm developing a course calendar for a client and I'm running into
performance problems with the admin site. For example, when I try to include
registration counts in the course list, the page really slows down for large
course lists (50 or so):

COURSEATTENDEES  CAPACITYSEATS LEFT
===  ==
Course 1 5  10   5
Course 2 6  15   9
Course 3 4  10   6

I've been using one query to retrieve the course list and then one for each
attendee count. Is there a more efficient way of doing this all in one
query? I was thinking something like this (I'm not a SQL expert, so I don't
know if this is even possible):

SELECT
course_name,
capacity,
count(query here) as attendee_count
FROM events AS e
LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id
WHERE start_time BETWEEN point_a AND point_b

Or should I just pull everything as a separate row like this and sort it all
out programmatically:

SELECT
e.course_name,
e.capacity,
a.user_id
FROM events AS e
LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id
WHERE start_time BETWEEN point_a AND point_b

Or should I just try caching the data in PHP? Would an index help?

I realize any answers might be complicated, but if you could just point me
in the right direction, I can probably figure the rest out.

Thanks,
Tony

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL query error

[Jeffrey]

Chris Carter wrote:

What wrong with this syntax, its not giving any error on runtime but I am
facing a blank page while paging.

$query=" SELECT * FROM gurgaonmalls WHERE mallname = '$mallname' limit $eu,
$limit ";


Have you tried...

echo " $query ";

...to unsure the variables have the values you expect them to have?

Jeffrey

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL query error

[Chris Carter]

What wrong with this syntax, its not giving any error on runtime but I am
facing a blank page while paging.

$query=" SELECT * FROM gurgaonmalls WHERE mallname = '$mallname' limit $eu,
$limit ";
-- 
View this message in context: 
http://www.nabble.com/SQL-query-error-tf2831052.html#a7903857
Sent from the Php - Database mailing list archive at Nabble.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL query

[Miguel Guirao]

OK, this makes my day clear!!
I have versión 3.23.49-3 of MySQL

Thanks Dwight!

-Original Message-
From: Dwight Altman [mailto:[EMAIL PROTECTED]
Sent: Jueves, 28 de Septiembre de 2006 11:32 a.m.
To: php-db@lists.php.net
Subject: RE: [PHP-DB] SQL query


Check your version.  Subselects were only added in MySQL Version 4.1.

Regards,
Dwight

> -Original Message-
> From: Edwin Cruz [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 28, 2006 10:53 AM
> To: 'Miguel Guirao'; php-db@lists.php.net
> Subject: RE: [PHP-DB] SQL query
>
> Make sure that your second query is returning only one row, if it dont
> help, try this:
> $query="select email from usuarios where userName in (select username
> from fussv where folio = 'FUSS-130-2006')"
>
>
> MySQL think that you second query returns more than 1 row, that's why
> mysql dont accept your query, is like trying to compare vs more than one
> scalar value
>
> Regards!
>
> ++
> | Ing Edwin Cruz <[EMAIL PROTECTED]>    | ++
> | Transportes Medel Rogero SA de CV  | |    |
> | Desk:  +52 (449) 910 30 90 x3054   | ++
> | MX Mobile: +52 (449) 111 29 03 |
> | Aguascalientes, Mexico |
> | http://www.medel.com.mx    |
> ++
>
>
>
> > -Mensaje original-
> > De: Miguel Guirao [mailto:[EMAIL PROTECTED]
> > Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m.
> > Para: php-db@lists.php.net
> > Asunto: [PHP-DB] SQL query
> >
> >
> >
> >
> > Hello list,
> >
> > Whats wrong with my SQL query:
> >
> > $query="select email from usuarios where userName = (select
> > username from fussv where folio = 'FUSS-130-2006')";
> >
> > I get an error!
> > I have tested the two individual sentences and they worked OK!
> >
> > ---
> > Miguel Guirao Aguilera
> > Logistica R8 TELCEL
> > Tel. (999) 960.7994
> >
> >
> > Este mensaje es exclusivamente para el uso de la persona o
> > entidad a quien esta dirigido; contiene informacion
> > estrictamente confidencial y legalmente protegida, cuya
> > divulgacion es sancionada por la ley. Si el lector de este
> > mensaje no es a quien esta dirigido, ni se trata del empleado
> > o agente responsable de esta informacion, se le notifica por
> > medio del presente, que su reproduccion y distribucion, esta
> > estrictamente prohibida. Si Usted recibio este comunicado por
> > error, favor de notificarlo inmediatamente al remitente y
> > destruir el mensaje. Todas las opiniones contenidas en este
> > mail son propias del autor del mensaje y no necesariamente
> > coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna
> > de sus empresas controladas, controladoras, afiliadas y
> > subsidiarias. Este mensaje intencionalmente no contiene acentos.
> >
> > This message is for the sole use of the person or entity to
> > whom it is being sent.  Therefore, it contains strictly
> > confidential and legally protected material whose disclosure
> > is subject to penalty by law.  If the person reading this
> > message is not the one to whom it is being sent and/or is not
> > an employee or the responsible agent for this information,
> > this person is herein notified that any unauthorized
> > dissemination, distribution or copying of the materials
> > included in this facsimile is strictly prohibited.  If you
> > received this document by mistake please notify  immediately
> > to the subscriber and destroy the message. Any opinions
> > contained in this e-mail are those of the author of the
> > message and do not necessarily coincide with those of
> > Radiomovil Dipsa, S.A. de C.V. or any of its control,
> > controlled, affiliates and subsidiaries companies. No part of
> > this message or attachments may be used or reproduced in any
> > manner whatsoever.
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
>
> --

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL query

[Dwight Altman]

Check your version.  Subselects were only added in MySQL Version 4.1.

Regards,
Dwight

> -Original Message-
> From: Edwin Cruz [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 28, 2006 10:53 AM
> To: 'Miguel Guirao'; php-db@lists.php.net
> Subject: RE: [PHP-DB] SQL query
> 
> Make sure that your second query is returning only one row, if it dont
> help, try this:
> $query="select email from usuarios where userName in (select username
> from fussv where folio = 'FUSS-130-2006')"
> 
> 
> MySQL think that you second query returns more than 1 row, that's why
> mysql dont accept your query, is like trying to compare vs more than one
> scalar value
> 
> Regards!
> 
> ++
> | Ing Edwin Cruz <[EMAIL PROTECTED]>    | ++
> | Transportes Medel Rogero SA de CV  | |    |
> | Desk:  +52 (449) 910 30 90 x3054   | ++
> | MX Mobile: +52 (449) 111 29 03 |
> | Aguascalientes, Mexico |
> | http://www.medel.com.mx    |
> ++
> 
> 
> 
> > -Mensaje original-
> > De: Miguel Guirao [mailto:[EMAIL PROTECTED]
> > Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m.
> > Para: php-db@lists.php.net
> > Asunto: [PHP-DB] SQL query
> >
> >
> >
> >
> > Hello list,
> >
> > Whats wrong with my SQL query:
> >
> > $query="select email from usuarios where userName = (select
> > username from fussv where folio = 'FUSS-130-2006')";
> >
> > I get an error!
> > I have tested the two individual sentences and they worked OK!
> >
> > ---
> > Miguel Guirao Aguilera
> > Logistica R8 TELCEL
> > Tel. (999) 960.7994
> >
> >
> > Este mensaje es exclusivamente para el uso de la persona o
> > entidad a quien esta dirigido; contiene informacion
> > estrictamente confidencial y legalmente protegida, cuya
> > divulgacion es sancionada por la ley. Si el lector de este
> > mensaje no es a quien esta dirigido, ni se trata del empleado
> > o agente responsable de esta informacion, se le notifica por
> > medio del presente, que su reproduccion y distribucion, esta
> > estrictamente prohibida. Si Usted recibio este comunicado por
> > error, favor de notificarlo inmediatamente al remitente y
> > destruir el mensaje. Todas las opiniones contenidas en este
> > mail son propias del autor del mensaje y no necesariamente
> > coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna
> > de sus empresas controladas, controladoras, afiliadas y
> > subsidiarias. Este mensaje intencionalmente no contiene acentos.
> >
> > This message is for the sole use of the person or entity to
> > whom it is being sent.  Therefore, it contains strictly
> > confidential and legally protected material whose disclosure
> > is subject to penalty by law.  If the person reading this
> > message is not the one to whom it is being sent and/or is not
> > an employee or the responsible agent for this information,
> > this person is herein notified that any unauthorized
> > dissemination, distribution or copying of the materials
> > included in this facsimile is strictly prohibited.  If you
> > received this document by mistake please notify  immediately
> > to the subscriber and destroy the message. Any opinions
> > contained in this e-mail are those of the author of the
> > message and do not necessarily coincide with those of
> > Radiomovil Dipsa, S.A. de C.V. or any of its control,
> > controlled, affiliates and subsidiaries companies. No part of
> > this message or attachments may be used or reproduced in any
> > manner whatsoever.
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> 
> --

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL query

[Edwin Cruz]

Make sure that your second query is returning only one row, if it dont
help, try this:
$query="select email from usuarios where userName in (select username
from fussv where folio = 'FUSS-130-2006')"


MySQL think that you second query returns more than 1 row, that's why
mysql dont accept your query, is like trying to compare vs more than one
scalar value

Regards!

++ 
| Ing Edwin Cruz <[EMAIL PROTECTED]>    | ++ 
| Transportes Medel Rogero SA de CV  | |    | 
| Desk:  +52 (449) 910 30 90 x3054   | ++ 
| MX Mobile: +52 (449) 111 29 03 | 
| Aguascalientes, Mexico | 
| http://www.medel.com.mx    | 
++



> -Mensaje original-
> De: Miguel Guirao [mailto:[EMAIL PROTECTED] 
> Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m.
> Para: php-db@lists.php.net
> Asunto: [PHP-DB] SQL query
> 
> 
> 
> 
> Hello list,
> 
> Whats wrong with my SQL query:
> 
> $query="select email from usuarios where userName = (select 
> username from fussv where folio = 'FUSS-130-2006')";
> 
> I get an error!
> I have tested the two individual sentences and they worked OK!
> 
> ---
> Miguel Guirao Aguilera
> Logistica R8 TELCEL
> Tel. (999) 960.7994
> 
> 
> Este mensaje es exclusivamente para el uso de la persona o 
> entidad a quien esta dirigido; contiene informacion 
> estrictamente confidencial y legalmente protegida, cuya 
> divulgacion es sancionada por la ley. Si el lector de este 
> mensaje no es a quien esta dirigido, ni se trata del empleado 
> o agente responsable de esta informacion, se le notifica por 
> medio del presente, que su reproduccion y distribucion, esta 
> estrictamente prohibida. Si Usted recibio este comunicado por 
> error, favor de notificarlo inmediatamente al remitente y 
> destruir el mensaje. Todas las opiniones contenidas en este 
> mail son propias del autor del mensaje y no necesariamente 
> coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna 
> de sus empresas controladas, controladoras, afiliadas y 
> subsidiarias. Este mensaje intencionalmente no contiene acentos.
> 
> This message is for the sole use of the person or entity to 
> whom it is being sent.  Therefore, it contains strictly 
> confidential and legally protected material whose disclosure 
> is subject to penalty by law.  If the person reading this 
> message is not the one to whom it is being sent and/or is not 
> an employee or the responsible agent for this information, 
> this person is herein notified that any unauthorized 
> dissemination, distribution or copying of the materials 
> included in this facsimile is strictly prohibited.  If you 
> received this document by mistake please notify  immediately 
> to the subscriber and destroy the message. Any opinions 
> contained in this e-mail are those of the author of the 
> message and do not necessarily coincide with those of 
> Radiomovil Dipsa, S.A. de C.V. or any of its control, 
> controlled, affiliates and subsidiaries companies. No part of 
> this message or attachments may be used or reproduced in any 
> manner whatsoever.
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL query

[Miguel Guirao]

Hello list,

Whats wrong with my SQL query:

$query="select email from usuarios where userName = (select username from
fussv where folio = 'FUSS-130-2006')";

I get an error!
I have tested the two individual sentences and they worked OK!

---
Miguel Guirao Aguilera
Logistica R8 TELCEL
Tel. (999) 960.7994


Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta 
dirigido; contiene informacion estrictamente confidencial y legalmente 
protegida, cuya divulgacion es sancionada por la ley. Si el lector de este 
mensaje no es a quien esta dirigido, ni se trata del empleado o agente 
responsable de esta informacion, se le notifica por medio del presente, que su 
reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio 
este comunicado por error, favor de notificarlo inmediatamente al remitente y 
destruir el mensaje. Todas las opiniones contenidas en este mail son propias 
del autor del mensaje y no necesariamente coinciden con las de Radiomovil 
Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, 
afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos.

This message is for the sole use of the person or entity to whom it is being 
sent.  Therefore, it contains strictly confidential and legally protected 
material whose disclosure is subject to penalty by law.  If the person reading 
this message is not the one to whom it is being sent and/or is not an employee 
or the responsible agent for this information, this person is herein notified 
that any unauthorized dissemination, distribution or copying of the materials 
included in this facsimile is strictly prohibited.  If you received this 
document by mistake please notify  immediately to the subscriber and destroy 
the message. Any opinions contained in this e-mail are those of the author of 
the message and do not necessarily coincide with those of Radiomovil Dipsa, 
S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries 
companies. No part of this message or attachments may be used or reproduced in 
any manner whatsoever.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] sql output to a multidimensional array

[K.A.Bouton]

> -Original Message-
> From: Chris [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, September 07, 2006 9:52 PM
> To: K.A.Bouton
> Cc: php-db@lists.php.net
> Subject: Re: [PHP-DB] sql output to a multidimensional array
> 
> 
> K.A.Bouton wrote:
> > I need the output of my sql to be a multidimensional array 
> as follows.
> > chart [ 'chart_data' ] =3D array ( array ( "",   "2001", 
> "2002", "2003",
> > "2004" ),
> >   array ( "AAA", 0, 
> 10, 30,
> > 63  ),
> >   array ( "BBB",   100, 
> 20, 65,
> > 55  ),
> >   array ( "CCC",56, 
> 21,  0,
> > 90  )
> > );
> > I have tried and am unable so far to get this out of my database.
> > Any suggestions? 
> > "SELECT count( publications.title) AS title_number, 
> publications.year,
> > publications.affiliation=20 FROM publications GROUP BY 
> affiliation, year
> > ORDER BY year"; 
> > 
> > There is some "for loop" I am not getting, and am also not 
> getting the 
> > = zero counts with the count(*).
> 
> 
> You won't be able to get an sql query to return in that 
> format (I know 
> what you're trying to do, I've used the same chart software).
> 
> You won't get zero counts for data that doesn't exist, you'll need to 
> generate your series before hand:
> 
>  for ($i = 2000; $i < 2006; $i++) {
>$data[$i] = 0;
> }
> 
> ?>
> 
> then later on override that value.

Thanks - but it's a dynamic dataset and I didn't want to hard code dates in.
I managed to do it by first doing a query on the years, then doing a count
query based on the year from the query above, and if no year was avaiable
count was 0.

Seems to work.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql output to a multidimensional array

[Mitch Miller]

Yeah ... it ain't purdy, and it sure doesn't scale so you have to be 
careful where you use it.


This is usually more of a presentation issue that I'd suggest be left up 
to the application doing the display of the data.


As an example, I believe Microsoft Excel has a crosstab function that 
can query right from a database and do the 3D effect automagically.


If I wanted to do this regularly, I think I'd be inclined to write a job 
that finds the number of columns (or rows) and then runs a separate 
query for each of the respective rows (or columns).  I was thinking 
through an HTML example, where it's difficult to add columns to a table 
on the fly, but having an "unknown" number of rows is no problem.  I 
think I'd query the db to find how many columns there are (and the 
respective headings, in the example given, year numbers).  Then, start a 
loop querying for each product and their respective annual sales and 
output each row.


Using this technique, you end up with a lot more queries back and forth 
to the server, but as an application, it could automatically scale in 
both width and height automatically.


-- Mitch


Chris wrote:

Mitch Miller wrote:


K.A.Bouton wrote:
 > I need the output of my sql to be a multidimensional array as follows.

then Chris wrote:
 > You won't be able to get an sql query to return in that format (I know
 > what you're trying to do, I've used the same chart software).


This is my MSSQL Server solution, and yep, it outputs EXACTLY like 
requested.  I'm not sufficiently fluent with MySQL, so this may have 
some "dialect" translation that needs done.


Also, this IS NOT a scalable solution at all.  It assumes you know the
years you want the information for, and requires that you specify the
years in two specific areas of the query.  Some SQL engines have a cross
tab function that will make this a little more automatic (MSSQL is one
such engine), but they are typically extensions to the SQL spec. and are
specific to each engine (i.e. the MSSQL implementation would not work
for MySQL).



I stand corrected :) mysql should support something like that but yeh 
it's not a great solution (ie it's a horrible query) ;)




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql output to a multidimensional array

[Chris]

Mitch Miller wrote:

K.A.Bouton wrote:
 > I need the output of my sql to be a multidimensional array as follows.

then Chris wrote:
 > You won't be able to get an sql query to return in that format (I know
 > what you're trying to do, I've used the same chart software).


This is my MSSQL Server solution, and yep, it outputs EXACTLY like 
requested.  I'm not sufficiently fluent with MySQL, so this may have 
some "dialect" translation that needs done.


Also, this IS NOT a scalable solution at all.  It assumes you know the
years you want the information for, and requires that you specify the
years in two specific areas of the query.  Some SQL engines have a cross
tab function that will make this a little more automatic (MSSQL is one
such engine), but they are typically extensions to the SQL spec. and are
specific to each engine (i.e. the MSSQL implementation would not work
for MySQL).


I stand corrected :) mysql should support something like that but yeh 
it's not a great solution (ie it's a horrible query) ;)


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql output to a multidimensional array

[Mitch Miller]

K.A.Bouton wrote:
> I need the output of my sql to be a multidimensional array as follows.

then Chris wrote:
> You won't be able to get an sql query to return in that format (I know
> what you're trying to do, I've used the same chart software).


This is my MSSQL Server solution, and yep, it outputs EXACTLY like 
requested.  I'm not sufficiently fluent with MySQL, so this may have 
some "dialect" translation that needs done.


Also, this IS NOT a scalable solution at all.  It assumes you know the
years you want the information for, and requires that you specify the
years in two specific areas of the query.  Some SQL engines have a cross
tab function that will make this a little more automatic (MSSQL is one
such engine), but they are typically extensions to the SQL spec. and are
specific to each engine (i.e. the MSSQL implementation would not work
for MySQL).

-- Mitch


--- SQL BEGINS HERE -

-- Table of products
CREATE TABLE #Products (
Product CHAR(3) NOT NULL
)
INSERT #Products(Product) VALUES('AAA')
INSERT #Products(Product) VALUES('BBB')
INSERT #Products(Product) VALUES('CCC')

-- Sales history table.
CREATE TABLE #Sales (
Product CHAR(3) NOT NULL ,
Quantity INT NOT NULL ,
SaleYear INT NOT NULL
)
-- Product AAA, Intentionally left out 2003
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 3, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 4, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 8, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 7, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 5, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('AAA', 6, 2004)
-- Product BBB, Intentionally left out 2002
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 3, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 5, 2001)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 2, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 7, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 1, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('BBB', 8, 2004)
-- Product CCC, Intentionally left out 2001
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2002)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 9, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 3, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 7, 2003)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 2, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 1, 2004)
INSERT #Sales(Product, Quantity, SaleYear) VALUES('CCC', 5, 2004)

-- Now for the totally UNscalable query.
SELECTp.Product,
COALESCE(y1.TotalQuan, 0) AS Quan2001,
COALESCE(y2.TotalQuan, 0) AS Quan2002,
COALESCE(y3.TotalQuan, 0) AS Quan2003,
COALESCE(y4.TotalQuan, 0) AS Quan2004
FROM #Products AS p
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2001
GROUP BY Product
) AS y1 ON y1.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2002
GROUP BY Product
) AS y2 ON y2.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2003
GROUP BY Product
) AS y3 ON y3.Product = p.Product
FULL OUTER JOIN (
SELECT Product, Sum(Quantity) AS TotalQuan
FROM #Sales
WHERE SaleYear = 2004
GROUP BY Product
) AS y4 ON y4.Product = p.Product

-- Cleanup
DROP TABLE #Sales
DROP TABLE #Products

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] sql output to a multidimensional array

[Chris]

K.A.Bouton wrote:

I need the output of my sql to be a multidimensional array as follows.
chart [ 'chart_data' ] =3D array ( array ( "",   "2001", "2002", "2003",
"2004" ),
  array ( "AAA", 0, 10, 30,
63  ),
  array ( "BBB",   100, 20, 65,
55  ),
  array ( "CCC",56, 21,  0,
90  )
);
I have tried and am unable so far to get this out of my database. 
Any suggestions? 
"SELECT count( publications.title) AS title_number, publications.year,

publications.affiliation=20 FROM publications GROUP BY affiliation, year
ORDER BY year"; 


There is some "for loop" I am not getting, and am also not getting the =
zero counts with the count(*). 



You won't be able to get an sql query to return in that format (I know 
what you're trying to do, I've used the same chart software).


You won't get zero counts for data that doesn't exist, you'll need to 
generate your series before hand:




then later on override that value.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] sql output to a multidimensional array

[K.A.Bouton]

I need the output of my sql to be a multidimensional array as follows.
chart [ 'chart_data' ] =3D array ( array ( "",   "2001", "2002", "2003",
"2004" ),
  array ( "AAA", 0, 10, 30,
63  ),
  array ( "BBB",   100, 20, 65,
55  ),
  array ( "CCC",56, 21,  0,
90  )
);
I have tried and am unable so far to get this out of my database. 
Any suggestions? 
"SELECT count( publications.title) AS title_number, publications.year,
publications.affiliation=20 FROM publications GROUP BY affiliation, year
ORDER BY year"; 

There is some "for loop" I am not getting, and am also not getting the =
zero counts with the count(*). 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Server COM question

[Frank M. Kromann]

Try the MSDN library:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjconnectionpme.asp

- Frank

> Chris wrote:
> > Todd Cary wrote:
> >> [Sorry - I misposted this in the General group]
> >>
> >> I am in the process of converting my clients PHP scripts that are 
> >> using Firebird so they will work with SQL Server (their request; not

> >> mine).
> >>
> >> Is there a reference where I can get the COM Methods and Properties?
> > 
> > The php website maybe? http://php.net/com
> > 
> >> Also, without loading the large AdoDb library, is there a Prepare() 
> >> method if I use the
> >>
> >> $db = new COM("ADODB.Connection")
> >>
> >> connection?
> > 
> > If you want to use adodb methods, you need to load it all up properly,

> > same as anything else.
> > 
> 
> I checked the http://php.net/com site initially and there is a 
> couple of examples, however there is not a list of all COM 
> methods and properties for SQL Server.  Does anyone know where 
> such a list might exist?
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Server COM question

[Chris]

Todd Cary wrote:

Chris wrote:

Todd Cary wrote:

[Sorry - I misposted this in the General group]

I am in the process of converting my clients PHP scripts that are 
using Firebird so they will work with SQL Server (their request; not 
mine).


Is there a reference where I can get the COM Methods and Properties?


The php website maybe? http://php.net/com

Also, without loading the large AdoDb library, is there a Prepare() 
method if I use the


$db = new COM("ADODB.Connection")

connection?


If you want to use adodb methods, you need to load it all up properly, 
same as anything else.




I checked the http://php.net/com site initially and there is a couple of 
examples, however there is not a list of all COM methods and properties 
for SQL Server.  Does anyone know where such a list might exist?




You could always get the freetds stuff working:

http://php.net/mssql

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Server COM question

[Todd Cary]

Chris wrote:

Todd Cary wrote:

[Sorry - I misposted this in the General group]

I am in the process of converting my clients PHP scripts that are 
using Firebird so they will work with SQL Server (their request; not 
mine).


Is there a reference where I can get the COM Methods and Properties?


The php website maybe? http://php.net/com

Also, without loading the large AdoDb library, is there a Prepare() 
method if I use the


$db = new COM("ADODB.Connection")

connection?


If you want to use adodb methods, you need to load it all up properly, 
same as anything else.




I checked the http://php.net/com site initially and there is a 
couple of examples, however there is not a list of all COM 
methods and properties for SQL Server.  Does anyone know where 
such a list might exist?


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Server COM question

[Chris]

Todd Cary wrote:

[Sorry - I misposted this in the General group]

I am in the process of converting my clients PHP scripts that are using 
Firebird so they will work with SQL Server (their request; not mine).


Is there a reference where I can get the COM Methods and Properties?


The php website maybe? http://php.net/com

Also, without loading the large AdoDb library, is there a Prepare() 
method if I use the


$db = new COM("ADODB.Connection")

connection?


If you want to use adodb methods, you need to load it all up properly, 
same as anything else.


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL Server COM question

[Todd Cary]

[Sorry - I misposted this in the General group]

I am in the process of converting my clients PHP scripts that are 
using Firebird so they will work with SQL Server (their request; 
not mine).


Is there a reference where I can get the COM Methods and Properties?

Also, without loading the large AdoDb library, is there a 
Prepare() method if I use the


$db = new COM("ADODB.Connection")

connection?

Any suggestions are greatly appreciated.

Todd

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL request on DBase file

[David BERCOT]

> Guess I misunderstodd the post, thought you were asking about how to access 
> oracle after importing the dbase files
> 
> http://ca.php.net/manual/en/ref.dbase.php
> 
> Not sure about your question on PDFs..can you clarify?

Of course. No problemo ;-)
I want to make SQL request on PDFs files like (syntax is fantaisist) :
Conn = OpenDatabase("PDF Driver","/var/www/repDBF");
SQLStmt = "Select * From DBFFile1 where Name = 'DUBOIS'";
RS = Conn.Execute(SQLStmt);

These is what I do in ASP on Windows...

David.

> Bastien
> 
> >From: David BERCOT <[EMAIL PROTECTED]>
> >To: php-db@lists.php.net
> >Subject: RE: [PHP-DB] SQL request on DBase file
> >Date: Sat, 11 Feb 2006 14:31:50 +0100
> >
> >Hi,
> >
> > > Whats wonrg with the manual?
> > >
> > > http://ca3.php.net/manual/en/ref.oracle.php
> > >
> > > http://www.zend.com/products/zend_core/zend_core_for_oracle
> >
> >I looked at these links and I found nothing about .pdf files !!!
> >I have no problem with Oracle, but only for requesting, in SQL, .dbf
> >files...
> >
> >David.
> >
> > > Bastien
> > >
> > > >Hi,
> > > >
> > > >I have to rewrite an application from ASP. In this application, I
> > > >receive DBase files which may be saved into Oracle.
> > > >In ASP, I used a generic driver and I made SQL requests on these files.
> > > >It is possible with PHP on Linux ?
> > > >Do you have some examples ?
> > > >
> > > >Thank you very much.
> > > >
> > > >David.
> 
> 
> ><< signature.asc >>
> 


signature.asc
Description: Ceci est une partie de message	numériquement signée

RE: [PHP-DB] SQL request on DBase file

[Bastien Koert]

Guess I misunderstodd the post, thought you were asking about how to access 
oracle after importing the dbase files


http://ca.php.net/manual/en/ref.dbase.php

Not sure about your question on PDFs..can you clarify?

Bastien


From: David BERCOT <[EMAIL PROTECTED]>
To: php-db@lists.php.net
Subject: RE: [PHP-DB] SQL request on DBase file
Date: Sat, 11 Feb 2006 14:31:50 +0100

Hi,

> Whats wonrg with the manual?
>
> http://ca3.php.net/manual/en/ref.oracle.php
>
> http://www.zend.com/products/zend_core/zend_core_for_oracle

I looked at these links and I found nothing about .pdf files !!!
I have no problem with Oracle, but only for requesting, in SQL, .dbf
files...

David.

> Bastien
>
> >Hi,
> >
> >I have to rewrite an application from ASP. In this application, I
> >receive DBase files which may be saved into Oracle.
> >In ASP, I used a generic driver and I made SQL requests on these files.
> >It is possible with PHP on Linux ?
> >Do you have some examples ?
> >
> >Thank you very much.
> >
> >David.




<< signature.asc >>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL request on DBase file

[David BERCOT]

Hi,

> Whats wonrg with the manual?
> 
> http://ca3.php.net/manual/en/ref.oracle.php
> 
> http://www.zend.com/products/zend_core/zend_core_for_oracle

I looked at these links and I found nothing about .pdf files !!!
I have no problem with Oracle, but only for requesting, in SQL, .dbf
files...

David.

> Bastien
> 
> >Hi,
> >
> >I have to rewrite an application from ASP. In this application, I
> >receive DBase files which may be saved into Oracle.
> >In ASP, I used a generic driver and I made SQL requests on these files.
> >It is possible with PHP on Linux ?
> >Do you have some examples ?
> >
> >Thank you very much.
> >
> >David.


signature.asc
Description: Ceci est une partie de message	numériquement signée

RE: [PHP-DB] SQL request on DBase file

[Bastien Koert]

Whats wonrg with the manual?

http://ca3.php.net/manual/en/ref.oracle.php

http://www.zend.com/products/zend_core/zend_core_for_oracle

Bastien




From: David BERCOT <[EMAIL PROTECTED]>
To: php-db@lists.php.net
Subject: [PHP-DB] SQL request on DBase file
Date: Sat, 11 Feb 2006 00:28:16 +0100

Hi,

I have to rewrite an application from ASP. In this application, I
receive DBase files which may be saved into Oracle.
In ASP, I used a generic driver and I made SQL requests on these files.
It is possible with PHP on Linux ?
Do you have some examples ?

Thank you very much.

David.




<< signature.asc >>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL request on DBase file

[David BERCOT]

Hi,

I have to rewrite an application from ASP. In this application, I
receive DBase files which may be saved into Oracle.
In ASP, I used a generic driver and I made SQL requests on these files.
It is possible with PHP on Linux ?
Do you have some examples ?

Thank you very much.

David.


signature.asc
Description: Ceci est une partie de message	numériquement signée

Re: [PHP-DB] SQL Server - do I need PHP to run COMMIT?

[Robert Twitty]

Do you have the "truncate log on checkpoint" option enabled? Also, if you
want to reduce the size of the log file if shrinking doesn't work, try
running the following against the database. Of course, you should backup
the DB first.


SET NOCOUNT ON
   DECLARE @LogicalFileName sysname,
   @MaxMinutes INT,
   @NewSize INT

   -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
   USE [CLEANUP]  -- This is the name of the database
  -- for which the log will be shrunk.
   SELECT  @LogicalFileName = 'FHMMSYS_Log',  -- Use sp_helpfile to
  -- identify the logical file
  -- name that you want to shrink.
   @MaxMinutes = 10,  -- Limit on time allowed to wrap log.
   @NewSize= 100   -- in MB

   -- Setup / initialize
   DECLARE @OriginalSize int
   SELECT @OriginalSize = size -- in 8K pages
 FROM sysfiles
 WHERE name = @LogicalFileName
   SELECT 'Original Size of ' + db_name() + ' LOG is ' +
   CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
   CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
 FROM sysfiles
 WHERE name = @LogicalFileName

   CREATE TABLE DummyTrans
 (DummyColumn char (8000) not null)

   -- Wrap log and truncate it.
   DECLARE @Counter   INT,
   @StartTime DATETIME,
   @TruncLog  VARCHAR(255)
   SELECT  @StartTime = GETDATE(),
   @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
   -- Try an initial shrink.
   DBCC SHRINKFILE (@LogicalFileName, @NewSize)

   EXEC (@TruncLog)

   -- Wrap the log if necessary.
   WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time
has not expired
 AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)  -- the log has not shrunk
 AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in
for new size is smaller than the current size.
 BEGIN -- Outer loop.
   SELECT @Counter = 0
   WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 5))
 BEGIN -- update
   INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char
field it inserts 8000 bytes.
   DELETE DummyTrans
   SELECT @Counter = @Counter + 1
 END   -- update
   EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
 END   -- outer loop
   SELECT 'Final Size of ' + db_name() + ' LOG is ' +
   CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
   CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
 FROM sysfiles
 WHERE name = @LogicalFileName
   DROP TABLE DummyTrans
   PRINT '*** Perform a full database backup ***'
   SET NOCOUNT OFF

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Server - do I need PHP to run COMMIT?

[Alex Gemmell]

Yes, the transaction logs are being backed up and shrunk regularly.  I 
am told while they are 400MB most of that is "empty space" and it's 
really about 50MB in size.  Apparently that is still a bit too big and 
indicates a possible problem.


I'm glad all transactions are auto-commited.  I presumed that was the 
case because we have 5 users all using this system at the same time and 
we have had no apparent problems with data being "saved" but not 
actually being stored in the database.  Everything actually seems to be 
working fine apart from the remarkably large transaction file.


Any ideas why the transaction log file would be so big?

Robert Twitty wrote:

Using COMMIT is only required if you issued a BEGIN TRANS before your
INSERTs, UPDATEs and / or DELETEs. All transactions are automatically
committed if you don't use BEGIN TRANS.  Are you backing up the
transaction logs regularly?

-- bob

On Fri, 16 Dec 2005, Alex Gemmell wrote:



Hello people,

My PHP application uses a SQL Server 2000 database.  I have previously
only ever used MySQL and so my knowledge of SQL Server comes just from
experimentation and trial and error experience.

My PHP application appears to be working fine but I have just discovered
that although the database itself is rather small on the disk (about
25MB) the transaction log file is huge (400MB).  I have had a quick look
at Microsoft's website about large transaction files and they suggest
many reasons, one of which is the application not COMMITing
transactions.  This is certainly true because I simply make INSERT and
UPDATE queries but don't include a COMMIT statement.

So my question is this:  should I be COMMITing?

How do I do that?  Do I simply run something like this after every
INSERT/UPDATE/DELETE:

mssql_query('COMMIT', $link_identifier);

Please help - I feel like I'm missing a trick here.

FYI:  I'm also now doubting my use of "mssql_pconnect()"  - should I
being using "mssql_connect()" with "mssql_close()" instead?

Thanks,

Alex

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Server - do I need PHP to run COMMIT?

[Robert Twitty]

Using COMMIT is only required if you issued a BEGIN TRANS before your
INSERTs, UPDATEs and / or DELETEs. All transactions are automatically
committed if you don't use BEGIN TRANS.  Are you backing up the
transaction logs regularly?

-- bob

On Fri, 16 Dec 2005, Alex Gemmell wrote:

> Hello people,
>
> My PHP application uses a SQL Server 2000 database.  I have previously
> only ever used MySQL and so my knowledge of SQL Server comes just from
> experimentation and trial and error experience.
>
> My PHP application appears to be working fine but I have just discovered
> that although the database itself is rather small on the disk (about
> 25MB) the transaction log file is huge (400MB).  I have had a quick look
> at Microsoft's website about large transaction files and they suggest
> many reasons, one of which is the application not COMMITing
> transactions.  This is certainly true because I simply make INSERT and
> UPDATE queries but don't include a COMMIT statement.
>
> So my question is this:  should I be COMMITing?
>
> How do I do that?  Do I simply run something like this after every
> INSERT/UPDATE/DELETE:
>
> mssql_query('COMMIT', $link_identifier);
>
> Please help - I feel like I'm missing a trick here.
>
> FYI:  I'm also now doubting my use of "mssql_pconnect()"  - should I
> being using "mssql_connect()" with "mssql_close()" instead?
>
> Thanks,
>
> Alex
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL Server - do I need PHP to run COMMIT?

[Alex Gemmell]

Hello people,

My PHP application uses a SQL Server 2000 database.  I have previously 
only ever used MySQL and so my knowledge of SQL Server comes just from 
experimentation and trial and error experience.


My PHP application appears to be working fine but I have just discovered 
that although the database itself is rather small on the disk (about 
25MB) the transaction log file is huge (400MB).  I have had a quick look 
at Microsoft's website about large transaction files and they suggest 
many reasons, one of which is the application not COMMITing 
transactions.  This is certainly true because I simply make INSERT and 
UPDATE queries but don't include a COMMIT statement.


So my question is this:  should I be COMMITing?

How do I do that?  Do I simply run something like this after every 
INSERT/UPDATE/DELETE:


mssql_query('COMMIT', $link_identifier);

Please help - I feel like I'm missing a trick here.

FYI:  I'm also now doubting my use of "mssql_pconnect()"  - should I 
being using "mssql_connect()" with "mssql_close()" instead?


Thanks,

Alex

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL Insert INTO question

[Bastien Koert]

Assuming a new record

"INSERT INTO reviews (review_id, review_txt)
VALUES
($id,'$_POST[review]')";

Bastien




From: geekgirl1 <[EMAIL PROTECTED]>
To: php-db@lists.php.net
Subject: [PHP-DB] SQL Insert INTO question
Date: Wed, 7 Dec 2005 11:59:26 -0500

First time poster.

This is the problem.  I want to add the value of $_POST[review] to the
reviews table where the unique id from the reviews table equals the
review id on my form.  The statement below does not work.  Should I
use UPDATE instead?

"INSERT INTO reviews (review_txt)
VALUES
('$_POST[review]') WHERE review_id = $id";

Marian

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL Insert INTO question

[geekgirl1]

First time poster.

This is the problem.  I want to add the value of $_POST[review] to the
reviews table where the unique id from the reviews table equals the
review id on my form.  The statement below does not work.  Should I
use UPDATE instead?

"INSERT INTO reviews (review_txt)
VALUES
('$_POST[review]') WHERE review_id = $id";

Marian

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection attack

[Vicente]

Estimado veditio,

you wrote:
> I've got a ton of forms that use the $_POST variable to send
> information into the database [...]
> Any suggestions on how to tighten up the form security, or does
> magic_quotes help enough? 

I'm not a security expert but after some attacks I have implemented
this simple thing. Until today it works for me.

You can put it before be connected to your database. I have one
only script to connect my database placed outside the /public_html.
It is and requested by means one include() in every oho script.
In this way, this security works in the whole site.

|%3c|%3e|SELECT|UNION|UPDATE|AND|exe|exec|INSERT|tmp/i';
  ...etc

//  detecting
if (preg_match($inyecc, $resto)) {

   // make something, in example sending an e-mail alert
   $ip = $HTTP_SERVER_VARS["HTTP_CLIENT_IP"];
   $forwarded = $HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"];
   $remoteaddress = $HTTP_SERVER_VARS["REMOTE_ADDR"];

   $message = "attack injection in $mi_url \n\nchain: $resto \n\n
   from: (ip-forw-RA):- $ip - $forwarded - $remoteaddress\n\n
   - end ";
   
   mail("[EMAIL PROTECTED]", "Attack injection", $message,
   "From: [EMAIL PROTECTED]'SERVER_NAME']}", "[EMAIL 
PROTECTED]'SERVER_NAME']}");

   // kill execution
   echo 'illegal url';
   die();
}   

// DB connection
$connection=mysql_connect(...etc.

?>


if you can encode this script with Zend Encoder or a similar thing.
It will be an additional measure to avoid the reading of this file.


hope it can be useful,



Vicente,

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection attack

[tg-php]

Haha.. what the hell?  Ok, I know this is an older copy of the script I wrote 
because I know I took out the "All this does is escape the data" comment and I 
KNOW I saw the thing about mysql_escape_string() being deprecated...  don't 
know why it's still in there. Hah

Thanks for pointing that out.  Now off to find my newer version and make sure I 
chaned it there too.

-TG

= = = Original message = = =

no !!!

mysql_real_escape_string()

anyhow.. good luck with your security endeavors!

On 8/25/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> I'm pretty amateur at this too, but have done a little reading on the 
> subject.  Here's some nuggets to ponder while the real experts write their 
> responses: :)
> 
> 1. Magic quotes + mysql_escape_string = double escaped stuff.  I think the 
> general opinion is the magic quotes is evil, but I'm sure some people like 
> it.  I prefer to use mysql_escape_string() since it escapes things more 
> specific to MySQL than magic quotes does.  Using mysql_escape_string should 
> be good enough by itself.
> 
> 2. Check data type.  If an item is supposed to be an integer, use intval() 
> before inserting into the database.
> 
> 3. What your SQL statements for variables that can turn your statement into a 
> "WHERE 1 = 1" situation that will always return TRUE.
> 
> Here's something I've been playing with.. a generic function to sanitize data 
> before inserting into the database.  You pass it the data and the type of 
> data and it'll clean it up.  Nice thing about this is I designed it so if you 
> say type = "phone" and you process it the same as type = "numeric".. then 
> later you decide you want to process "phone" and "numeric" types separately, 
> you only have to check this function, not all your lines of code.
> 
> If someone has better ways of doing this, I'm all for hearing it.  Please 
> opine or criticize what I've posted above too.  I want to learn as well.
> 
> -TG
> 
> Code:
> 
>  /**
> *~DBSanitizeData() prepares data for inserting/updating into or selecting from
> * MySQL by making sure that string data is properly escaped so as not to allow
> * 'SQL injection' type security issues from happening. No direct $_POST or 
> $_GET
> * data should ever be used in a SQL string.
> *
> * Returns sanitized copy of data sent to it.
> *
> * Current sanitization only performs a mysql_escape_string() function but 
> could do
> * more later.
> *
> * Example: $result = mysql_query('INSERT INTO TableName (SomeColumn) VALUES 
> (' . DBSanitizeData($_POST['somevar']) . ')');
> *
> * 
> * Modification Log:
> * --
> * Created: ~~Trevor Gryffyn - 03/28/2005
> *
> * 
> *
> * @author Trevor Gryffyn <[EMAIL PROTECTED]>
> * @category Database Functions
> *
> */
>  function DBSanitizeData($dbdata, $datatype = "alpha") 
>switch ($datatype) 
>  case "binary":
>  case "truefalse":
>$trues = array("YES", "Y", "1", "ON", "TRUE", "T");
>$falses = array("NO", "N", "0", "OFF", "FALSE", "F");
>if (in_array(trim(strtoupper($dbdata)), $trues)) 
>  $dbdata = "Y";
> else 
>  $dbdata = "N";
>
>break;
>  case "phone":
>  case "numeric":
>  case "ssn":
>$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata);
>break;
>  case "float":
>  case "money":
>  case "percent":
>// TODO: Should this be handled with floatval() or something else?
>//   Yes.. it probably should. Maybe this is better.
>if (strstr($dbdata, ".") AND trim($dbdata) <> "") 
>  #$dbdata = (preg_replace ('/[^\d]+/s', '', $dbdata) / 100) . ".00";
>  $dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata) / 100);
> else 
>  #$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata) . ".00";
>  $dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata));
>
>break;
> 
>  case "name":
>  case "address":
>$dbdata = ucwords($dbdata);
>break;
>  case "state":
>$dbdata = strtoupper($dbdata);
>break;
>  case "date":
>$dbdata = date("Y-m-d", strtotime($dbdata));
>if ($dbdata == "1969-12-31") $dbdata = "";
>break;
>  case "alpha":
>  default:
>// Nothing special, just jump down to the trim/escape
>break;
>
>return trim(mysql_escape_string($dbdata));
>  
> ?>
> 
> = = = Original message = = =
> 
> Greetings all:
> 
> Using PHP 4.3.xx and MySQL 4.1 (and 3.xxx sometimes).
> 
> I've got a ton of forms that use the $_POST variable to send information into 
> the database, and I'm worried about injection attacks.
> 
> My server has magic_quotes enabled, which I thought would handle most things, 
> but am wondering now if I need to use mysql_escape_string on everything, 
> which would mean, of  course, a lot of find-and-replace and rewriting.
> 
> Also, REGISTER_GLOBALS is turned off, and errors are not shown t

Re: [PHP-DB] SQL Injection attack

[Jordan Miller]

NOTE:
http://www.php.net/mysql_escape_string
"Version: 4.3.0
Description: This function became deprecated, do not use this  
function. Instead, use mysql_real_escape_string()."


Jordan


On Aug 25, 2005, at 2:15 PM, <[EMAIL PROTECTED]> [EMAIL PROTECTED]> wrote:




Using mysql_escape_string should be good enough by itself.



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL Injection attack

[tg-php]

I'm pretty amateur at this too, but have done a little reading on the subject.  
Here's some nuggets to ponder while the real experts write their responses: :)

1. Magic quotes + mysql_escape_string = double escaped stuff.  I think the 
general opinion is the magic quotes is evil, but I'm sure some people like it.  
I prefer to use mysql_escape_string() since it escapes things more specific to 
MySQL than magic quotes does.  Using mysql_escape_string should be good enough 
by itself.

2. Check data type.  If an item is supposed to be an integer, use intval() 
before inserting into the database.

3. What your SQL statements for variables that can turn your statement into a 
"WHERE 1 = 1" situation that will always return TRUE.

Here's something I've been playing with.. a generic function to sanitize data 
before inserting into the database.  You pass it the data and the type of data 
and it'll clean it up.  Nice thing about this is I designed it so if you say 
type = "phone" and you process it the same as type = "numeric".. then later you 
decide you want to process "phone" and "numeric" types separately, you only 
have to check this function, not all your lines of code.

If someone has better ways of doing this, I'm all for hearing it.  Please opine 
or criticize what I've posted above too.  I want to learn as well.

-TG

Code:


* Modification Log:
* --
* Created: ~~Trevor Gryffyn - 03/28/2005
*
* 
*
* @author Trevor Gryffyn <[EMAIL PROTECTED]>
* @category Database Functions
*
*/
  function DBSanitizeData($dbdata, $datatype = "alpha") {
switch ($datatype) {
  case "binary":
  case "truefalse":
$trues = array("YES", "Y", "1", "ON", "TRUE", "T");
$falses = array("NO", "N", "0", "OFF", "FALSE", "F");
if (in_array(trim(strtoupper($dbdata)), $trues)) {
  $dbdata = "Y";
} else {
  $dbdata = "N";
}
break;
  case "phone":
  case "numeric":
  case "ssn":
$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata);
break;
  case "float":
  case "money":
  case "percent":
// TODO: Should this be handled with floatval() or something else?
//   Yes.. it probably should. Maybe this is better.
if (strstr($dbdata, ".") AND trim($dbdata) <> "") {
  #$dbdata = (preg_replace ('/[^\d]+/s', '', $dbdata) / 100) . ".00";
  $dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata) / 100);
} else {
  #$dbdata = preg_replace ('/[^\d]+/s', '', $dbdata) . ".00";
  $dbdata = floatval(preg_replace ('/[^\d]+/s', '', $dbdata));
}
break;
  
  case "name":
  case "address":
$dbdata = ucwords($dbdata);
break;
  case "state":
$dbdata = strtoupper($dbdata);
break;
  case "date":
$dbdata = date("Y-m-d", strtotime($dbdata));
if ($dbdata == "1969-12-31") $dbdata = "";
break;
  case "alpha":
  default:
// Nothing special, just jump down to the trim/escape
break;
}
return trim(mysql_escape_string($dbdata));
  }
?>

= = = Original message = = =

Greetings all:

Using PHP 4.3.xx and MySQL 4.1 (and 3.xxx sometimes).

I've got a ton of forms that use the $_POST variable to send information into 
the database, and I'm worried about injection attacks.

My server has magic_quotes enabled, which I thought would handle most things, 
but am wondering now if I need to use mysql_escape_string on everything, which 
would mean, of  course, a lot of find-and-replace and rewriting.

Also, REGISTER_GLOBALS is turned off, and errors are not shown to the user when 
the site is live.

Any suggestions on how to tighten up the form security, or does magic_quotes 
help enough?

For what it's worth, I've tried to enter things like "pw=''" and other 
simulated attackes using the $_GET method, but haven't been able to crack the 
site. But I'm a noob at that kind of thing, so I try not to get too carried 
away with myself.

Thanks,
V


___
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL Injection attack

[veditio]

Greetings all:

Using PHP 4.3.xx and MySQL 4.1 (and 3.xxx sometimes).

I've got a ton of forms that use the $_POST variable to send information into 
the database, and I'm worried about injection attacks.

My server has magic_quotes enabled, which I thought would handle most things, 
but am wondering now if I need to use mysql_escape_string on everything, which 
would mean, of  course, a lot of find-and-replace and rewriting.

Also, REGISTER_GLOBALS is turned off, and errors are not shown to the user when 
the site is live.

Any suggestions on how to tighten up the form security, or does magic_quotes 
help enough?

For what it's worth, I've tried to enter things like "pw=''" and other 
simulated attackes using the $_GET method, but haven't been able to crack the 
site. But I'm a noob at that kind of thing, so I try not to get too carried 
away with myself.

Thanks,
V

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] sql injection attack, protection from

[Juffermans, Jos]

Personally, I always check variables that I'm using in a query. If I'm
expecting eg a session id (32 hex characters) I check that the session id is
a valid one - ie "!$[0-9a-f]{32}$!" (I use ! as delimiter in regexps).

Allthough mysql_escape_string will probably protects me from injections, I
still verify the data.

Jos

-Original Message-
From: mayo [mailto:[EMAIL PROTECTED]
Sent: 16 May 2005 23:55
To: php-db@lists.php.net
Subject: [PHP-DB] sql injection attack, protection from


I'm new to PHP and would like to make certain that I have the basic
protection for the site:
 
Use double quotes to contain variable
Use mysql_escape_string so that query is considered part of the WHERE
clause.
 
$result=mysql_query('SELECT * FROM users WHERE
username="'.mysql_escape_string($_GET['username']).'"');
 
I'm pulling prices from a database and sending the item ID which has 4
characters (1001, 1002, etc.)
 
Is the following unnecessary with mysql_escape_string?
 
if (preg_match("/^\w{4,4}$/", $_GET['username'], $matches))
   $result = mysql_query("SELECT * FROM items WHERE
itemID=$matches[0]");
 else // we don't bother querying the database
   echo "itemID not accepted";
 
Thanks
 
 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] sql injection attack, protection from

[mayo]

I'm new to PHP and would like to make certain that I have the basic
protection for the site:
 
Use double quotes to contain variable
Use mysql_escape_string so that query is considered part of the WHERE
clause.
 
$result=mysql_query('SELECT * FROM users WHERE
username="'.mysql_escape_string($_GET['username']).'"');
 
I'm pulling prices from a database and sending the item ID which has 4
characters (1001, 1002, etc.)
 
Is the following unnecessary with mysql_escape_string?
 
if (preg_match("/^\w{4,4}$/", $_GET['username'], $matches))
   $result = mysql_query("SELECT * FROM items WHERE
itemID=$matches[0]");
 else // we don't bother querying the database
   echo "itemID not accepted";
 
Thanks
 
 

Re: [PHP-DB] SQL or array ?

[M Saleh EG]

Only? That aint too big, but now i'm confused what's ur hardware and
O.Sspecs? 300 to 500 would be a peice of cake to load. However, it
depends on
ur system.

I would rather use the array than hitting the db for the query. Because the 
bottom line is that you will receive your data in an array no matter what 
even if its in the db so doesnt realy matter unless you wanna paginate it. 
In that case I'd use the db rather to array alone. 

Wow Hold on. That's a catch 22. If ur loading ur array in the beginning of 
the execution then just do it on the DB once n just load it the next time. 
That means the next time you dont have to take a trip to ur file system n 
then another trip to display the array. And plus if u needed to paginate 
which of corse ull need to for such a number db helps, however paginating 
arrays is easy as well.

HTH

Benchmark it! And find out for yourself.

On 4/24/05, Paul Reilly <[EMAIL PROTECTED]> wrote:
> 
> 
> > explain the phrase "big array."
> 
> I guess everything is relative!
> We're talking about 300-500 items here.
> 
> Paul
> 
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 


-- 
M.Saleh.E.G
97150-4779817

Re: [PHP-DB] SQL or array ?

[Paul Reilly]

> explain the phrase "big array."

I guess everything is relative!
We're talking about 300-500 items here.

Paul

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL or array ?

[Mark Cain]

explain the phrase "big array."

What is big to you might not be big to the server nor to me but then again
maybe it's bigger.  Are you talking dozens, hundreds, thousands, millions??

- Original Message -
From: "Paul Reilly" <[EMAIL PROTECTED]>
To: 
Sent: Saturday, April 23, 2005 5:05 PM
Subject: [PHP-DB] SQL or array ?


>
> I have a quick question about which would the best way to implement
> something in terms of performance. Using a database, or just creating
> a big array in memory?
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] SQL or array ?

[Paul Reilly]

I have a quick question about which would the best way to implement
something in terms of performance. Using a database, or just creating
a big array in memory?

I have a PHP file manager script which creates an array of all
files in a directory, and get's their mime types. It then prints
these files to the browser with a little image or a word icon etc
for word documents.

So the data looks a bit like this:

+--++---+--+
| id   | mime_type  | description   | icon |
+--++---+--+
|0 | text/html  | HTML document | html.png |
|1 | application/msword | Word document | word.png |
+--++---+--+

But this means for every file it comes across it needs
to do an SQL query to find the description and icon name.
Which doesn't seem very efficient.

Would it be better to just create a big array at the start
of my script with all this data in? That would seem more
efficient, and hence faster way of doing it?

Any feedback appreciated.

Paul

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL prob

[Bobo Wieland]

Thanks, but DISTINCT doesn't work... But I managed to get it to work 
anyway by including

"namn_1 NOT LIKE 'H%'"
in the second WHERE-clause...
Micah Stevens wrote:
use DISTINCT?
On Sunday 30 January 2005 12:51 pm, Bobo Wieland wrote:
 

Anyone that can help me with this one? I want this SQL-statement to
retrive only distinct values from the original table column named (not
the AS stuff)
(
SELECT * , namn_2 AS sec_namn, namn_1 AS one
FROM sortiment
WHERE namn_1 LIKE 'H%'
)
UNION (
SELECT * , UPPER( namn_2 ) AS sec_namn, namn_2 AS one
FROM sortiment
WHERE namn_2 LIKE 'H%'
AND SUBSTRING( namn_2, 1, 1 ) LIKE BINARY 'H'
)
ORDER BY one
LIMIT 0 , 10
this will return, for example, the following row twice where (in the
original table)
namn_1 = "Humulus lupulus"
namn_2 =  "Humle"
the two rows are identical except for the "sec_namn" and "one" created
by the query. They are set to:
sec_namn  one
HUMLEHumle
HumleHumulus lupulus

_bobo wieland _ [EMAIL PROTECTED] _
winamp >> Not playing anything right now...
   

 

--
_bobo wieland _ [EMAIL PROTECTED] _
winamp >> Not playing anything right now...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] SQL prob

[Micah Stevens]

use DISTINCT?

On Sunday 30 January 2005 12:51 pm, Bobo Wieland wrote:
> Anyone that can help me with this one? I want this SQL-statement to
> retrive only distinct values from the original table column named (not
> the AS stuff)
>
> (
> SELECT * , namn_2 AS sec_namn, namn_1 AS one
> FROM sortiment
> WHERE namn_1 LIKE 'H%'
> )
> UNION (
>
> SELECT * , UPPER( namn_2 ) AS sec_namn, namn_2 AS one
> FROM sortiment
> WHERE namn_2 LIKE 'H%'
> AND SUBSTRING( namn_2, 1, 1 ) LIKE BINARY 'H'
> )
> ORDER BY one
> LIMIT 0 , 10
>
> this will return, for example, the following row twice where (in the
> original table)
> namn_1 = "Humulus lupulus"
> namn_2 =  "Humle"
>
> the two rows are identical except for the "sec_namn" and "one" created
> by the query. They are set to:
> sec_namn  one
> HUMLEHumle
> HumleHumulus lupulus
>
>
>
>
> _bobo wieland _ [EMAIL PROTECTED] _
> winamp >> Not playing anything right now...

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php