Home > Archive > PHP Language > January 2006 > before I have a break down
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
before I have a break down
|
|
| Jean Pierre Daviau 2006-01-10, 4:00 am |
| Hi to everyone,
Somebody can help me on this?
here is what the html page shows:
There was a database error when executing <PRE>UPDATE stories SET
writer='JPD' , page = 'news', headline = 'Test', story_text = 'Na Na',
picture = 'pictures/one.jpg', modified = '1135893089', WHERE id = '7'
</PRE>Syntax error near 'WHERE id = '7'' at line 1
------I went in my SQL with no more information
UPDATE stories SET writer = 'JPD',
page = 'news',
headline = 'Test',
story_text = 'Na Na ',
picture = 'pictures/one.jpg',
modified = '1135893089',
WHERE id = '7'
#1064 - Syntax error near 'WHERE id = '7'' ?at line 1
------
here is my php:
$sql = "UPDATE stories SET writer='$auth_user' , page = '$page', headline
= '$headline', story_text = '$story_text', picture = '$filename', modified
= '$time', WHERE id = '$story' ";
--
Jean Pierre Daviau
--
phpMyAdmin 2.6.1 MySQL 4.1.9-max
Easyphp1.8
Apache1.3.24
DEVC++, borland 5.5
windows Xp
asus p4 s533/333/133
Intel(R) Celeron (R) CPU 2.00 GHz
http://www.jeanpierredaviau.com
| |
| Connector5 2006-01-10, 4:00 am |
| Get rid of the comma before the word "WHERE"
"Jean Pierre Daviau" <Once@WasEno.ugh> wrote in message
news:r1Zsf.11373$vT1.383504@wagner.videotron.net...
> Hi to everyone,
>
>
>
> Somebody can help me on this?
>
>
>
> here is what the html page shows:
>
> There was a database error when executing <PRE>UPDATE stories SET
> writer='JPD' , page = 'news', headline = 'Test', story_text = 'Na Na',
> picture = 'pictures/one.jpg', modified = '1135893089', WHERE id = '7'
> </PRE>Syntax error near 'WHERE id = '7'' at line 1
> ------I went in my SQL with no more information
>
> UPDATE stories SET writer = 'JPD',
> page = 'news',
> headline = 'Test',
> story_text = 'Na Na ',
> picture = 'pictures/one.jpg',
> modified = '1135893089',
> WHERE id = '7'
>
> #1064 - Syntax error near 'WHERE id = '7'' ?at line 1
>
> ------
>
> here is my php:
> $sql = "UPDATE stories SET writer='$auth_user' , page = '$page',
headline
> = '$headline', story_text = '$story_text', picture = '$filename',
modified
> = '$time', WHERE id = '$story' ";
>
> --
> Jean Pierre Daviau
> --
> phpMyAdmin 2.6.1 MySQL 4.1.9-max
>
> Easyphp1.8
> Apache1.3.24
> DEVC++, borland 5.5
> windows Xp
> asus p4 s533/333/133
> Intel(R) Celeron (R) CPU 2.00 GHz
> http://www.jeanpierredaviau.com
>
>
| |
| David Haynes 2006-01-10, 4:00 am |
| Jean Pierre Daviau wrote:
> Hi to everyone,
>
>
>
> Somebody can help me on this?
>
>
>
> here is what the html page shows:
>
> There was a database error when executing <PRE>UPDATE stories SET
> writer='JPD' , page = 'news', headline = 'Test', story_text = 'Na Na',
> picture = 'pictures/one.jpg', modified = '1135893089', WHERE id = '7'
> </PRE>Syntax error near 'WHERE id = '7'' at line 1
> ------I went in my SQL with no more information
>
> UPDATE stories SET writer = 'JPD',
> page = 'news',
> headline = 'Test',
> story_text = 'Na Na ',
> picture = 'pictures/one.jpg',
> modified = '1135893089',
> WHERE id = '7'
>
> #1064 - Syntax error near 'WHERE id = '7'' ?at line 1
>
> ------
>
> here is my php:
> $sql = "UPDATE stories SET writer='$auth_user' , page = '$page', headline
> = '$headline', story_text = '$story_text', picture = '$filename', modified
> = '$time', WHERE id = '$story' ";
>
Get rid of the comma before WHERE.
-david-
| |
| Jean Pierre Daviau 2006-01-10, 4:00 am |
| Thank you very much.
Happy New Year
| |
| Balazs Wellisch 2006-01-10, 4:00 am |
|
> Get rid of the comma before WHERE.
>
Also, if id is a numeric type than you don't really need the single quotes
around $story. I'm not sure what the implications of assigning a char value
to a numeric type is.
| |
|
| UPDATE stories SET writer = 'JPD',
page = 'news',
headline = 'Test',
story_text = 'Na Na ',
picture = 'pictures/one.jpg',
modified = '1135893089',
WHERE id = '7'
comma should not come before WHERE.
| |
| Connector5 2006-01-10, 4:00 am |
|
"Balazs Wellisch" <balazs@neusolutions.com> wrote in message
news:892tf.8619$hI1.6865@tornado.socal.rr.com...
>
>
> Also, if id is a numeric type than you don't really need the single quotes
> around $story. I'm not sure what the implications of assigning a char
value
> to a numeric type is.
>
>
argghh! Don't teach him bad habits! Any time a non-constant value (read:
variable) is used inside a query string, the single quotes should be there.
Both PostgreSQL and MySQL gracefully convert numbers to text and vice-versa
when quoted.
| |
| Hilarion 2006-01-10, 4:00 am |
| >> >> $sql = "UPDATE stories SET writer='$auth_user' , page = '$page',
> value
>
> argghh! Don't teach him bad habits! Any time a non-constant value (read:
> variable) is used inside a query string, the single quotes should be there.
No, they should not. One should avoid using dynamic SQL statements if possible
and use statements with value placeholders and provided value binding functions.
If it's not possible and the value has to be placed in the SQL statemet text,
then it should use the DB type of the value when possible. This means that
numeric values should NOT use single quotes (or any other quotes). To avoid
SQL injection all values (not only those without quotes) should be properly
validated and/or escaped.
No single quote is going to protect anyone from SQL injection attacks.
> Both PostgreSQL and MySQL gracefully convert numbers to text and vice-versa
> when quoted.
Yes, when they know they should (which is not always true), but the sonversion
can be environment dependant (eg. decimal point), so one should not rely
on such implicit conversions.
Hilarion
| |
| Connector5 2006-01-10, 4:00 am |
| Well, touche.
It goes without saying that escaping, typecasting, and validating are far
better than simply quoting the data. Hilarion is correct.
"Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote in message
news:dp4p6j$hd1$1@news.onet.pl...
quotes[color=darkred]
(read:[color=darkred]
there.[color=darkred]
>
> No, they should not. One should avoid using dynamic SQL statements if
possible
> and use statements with value placeholders and provided value binding
functions.
> If it's not possible and the value has to be placed in the SQL statemet
text,
> then it should use the DB type of the value when possible. This means that
> numeric values should NOT use single quotes (or any other quotes). To
avoid
> SQL injection all values (not only those without quotes) should be
properly
> validated and/or escaped.
> No single quote is going to protect anyone from SQL injection attacks.
>
>
vice-versa[color=darkred]
>
> Yes, when they know they should (which is not always true), but the
sonversion
> can be environment dependant (eg. decimal point), so one should not rely
> on such implicit conversions.
>
>
> Hilarion
| |
| Jim Michaels 2006-01-16, 3:55 am |
| I've not seen this ("statements with value placeholders and provided value
binding functions") using the mysql functions in PHP before. what does it
look like?
"Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote in message
news:dp4p6j$hd1$1@news.onet.pl...
>
> No, they should not. One should avoid using dynamic SQL statements if
> possible
> and use statements with value placeholders and provided value binding
> functions.
> If it's not possible and the value has to be placed in the SQL statemet
> text,
> then it should use the DB type of the value when possible. This means that
> numeric values should NOT use single quotes (or any other quotes). To
> avoid
> SQL injection all values (not only those without quotes) should be
> properly
> validated and/or escaped.
> No single quote is going to protect anyone from SQL injection attacks.
>
>
>
> Yes, when they know they should (which is not always true), but the
> sonversion
> can be environment dependant (eg. decimal point), so one should not rely
> on such implicit conversions.
>
>
> Hilarion
| |
| Connector5 2006-01-16, 9:55 pm |
| sprintf is one example.
"Jim Michaels" <jmichae3@yahoo.com> wrote in message
news:Kr6dnYAdVJDN1lbenZ2dnUVZ_tudnZ2d@co
mcast.com...
> I've not seen this ("statements with value placeholders and provided value
> binding functions") using the mysql functions in PHP before. what does it
> look like?
>
> "Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote in message
> news:dp4p6j$hd1$1@news.onet.pl...
that[color=darkred]
>
>
| |
| Hilarion 2006-01-16, 9:55 pm |
| > I've not seen this ("statements with value placeholders and provided value
> binding functions") using the mysql functions in PHP before. what does it
> look like?
Check "mysqli" functions in PHP manual. As far as I know (I do not use
MySQL) it uses "?" as a value placeholder. You first prepare the query,
then bind the values to the placeholders (you do not have to - or rather
you should not - escape the values in this case) and execute the
statement. You can then rebind the values and execute the statement
again with new values without doing the "prepare" part, which saves
some time and/or resources. At the very ent you close the statement
which releases the resources reserved when the query got prepared.
The syntax of "mysqli" binding could be better (it could use named
parameters and a better way of passing value types), but it sure is
safer than dynamic queries.
In case of Oracle all PHP functions for it were made to support
parameters from the very start (in case of MySQL the parameters are
supported for quite short time). Oracle uses named parameters (you
bind values to parameters using parameter names, not parameter
positions in query). In oracle you "parse" the statement instead of
"prepare", and you "free" the statement instead of using "close".
Sometimes you also have to free values (if they are of some complexe
type like BLOB or CLOB).
Hilarion
| |
| Hilarion 2006-01-16, 9:55 pm |
| >> I've not seen this ("statements with value placeholders and provided value
[color=darkred]
> sprintf is one example.
If it's an example for strings then yes, but not for SQL. If you use sprintf
to create SQL statements, then they are still just unparameterized dynamic SQL
statements which require value escaping (so they are not safer than those
build using concatenation or variable evaluation) and provide no execution
optimization (for example when you use parameterized Oracle queries then
reexecuting such query makes Oracle reuse the parse results and execution
plans from previous execution; in case of dynamic queries it works only if
you use same parameter values).
Hilarion
| |
| Jim Michaels 2006-01-18, 3:55 am |
| AHA! I was wondering what all these new mysqli functions brought to the
table... I haven't completely gone over those functions yet.
this sounds like using SQL Server's dblib for doing embedded SQL.
and I'm glad to see the latest CHM manual acually has content for these
functions now (just upgraded my manual).
what type is a CLOB (couldn't find it in MySQL 5.0 manual)? an Oracle type?
BTW, what would drive the decision to choose between BLOB, LONGBLOB, and
BINARY? I know between BLOB and LONGBLOB it's data size. but I don't
know much about BINARY and its difference from BLOB. Isn't BLOB and
LONGBLOB just a type of BINARY, where BINARY is the internal representation
type?
"Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote in message
news:dqghi7$n65$1@news.onet.pl...
>
>
> Check "mysqli" functions in PHP manual. As far as I know (I do not use
> MySQL) it uses "?" as a value placeholder. You first prepare the query,
> then bind the values to the placeholders (you do not have to - or rather
> you should not - escape the values in this case) and execute the
> statement. You can then rebind the values and execute the statement
> again with new values without doing the "prepare" part, which saves
> some time and/or resources. At the very ent you close the statement
> which releases the resources reserved when the query got prepared.
> The syntax of "mysqli" binding could be better (it could use named
> parameters and a better way of passing value types), but it sure is
> safer than dynamic queries.
> In case of Oracle all PHP functions for it were made to support
> parameters from the very start (in case of MySQL the parameters are
> supported for quite short time). Oracle uses named parameters (you
> bind values to parameters using parameter names, not parameter
> positions in query). In oracle you "parse" the statement instead of
> "prepare", and you "free" the statement instead of using "close".
> Sometimes you also have to free values (if they are of some complexe
> type like BLOB or CLOB).
>
>
> Hilarion
| |
| Hilarion 2006-01-18, 7:56 am |
| > what type is a CLOB (couldn't find it in MySQL 5.0 manual)? an Oracle type?
I know this type from Oracle. I do not use MySQL, so I'm not sure how this
type translates to MySQL types. CLOB in Oracle is a character type for long texts.
VARCHAR / CHAR types in Oracle (at least in the versions I used) are
limited to (AFAIR) 4000 characters. CLOB has only a filesystem limit
(or 2GB on older Oracles). The flaw of CLOB type is that normal text
operators and functions do not work with it.
> BTW, what would drive the decision to choose between BLOB, LONGBLOB, and
> BINARY? I know between BLOB and LONGBLOB it's data size. but I don't
> know much about BINARY and its difference from BLOB. Isn't BLOB and
> LONGBLOB just a type of BINARY, where BINARY is the internal representation
> type?
As I said I do not use MySQL, so I can't help in this case.
Hilarion
| |
| Jim Michaels 2006-01-19, 6:58 pm |
| Ah. in MySQL the equivelant to CLOB would be LONGTEXT.
"Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote in message
news:dqlfbk$9vk$1@news.onet.pl...
>
> I know this type from Oracle. I do not use MySQL, so I'm not sure how this
> type translates to MySQL types. CLOB in Oracle is a character type for
> long texts.
> VARCHAR / CHAR types in Oracle (at least in the versions I used) are
> limited to (AFAIR) 4000 characters. CLOB has only a filesystem limit
> (or 2GB on older Oracles). The flaw of CLOB type is that normal text
> operators and functions do not work with it.
> Hilarion
|
|
|
|
|