Re: [PHP] Sanitizing mysql inserts of user data
> Thanks Paul, that was a much better explanation than the one I was > attempting. I'm guessing the OP was being thrown off by the colons in > the SELECT statement above. I can see how those could look like > comments to someone not familiar with PDO and named parameters. > It wasn't the colons being mistaken for comments that threw me off, I realized that they were not comments. But being unfamiliar with PDO and named parameters I did not understand exactly what was happening. I will try to google some more information on PDO now. Thanks. -- Dotan Cohen http://what-is-what.com http://gibberish.co.il -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
>> > That said, I would recommend binding parameters if you can. It's a
>> > cleaner way of separating the logic of a query from its data, and
>> > theoretically more reliable than mysql_real_escape_string():
>> >
>> > http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements
>> >
>>
>> I fail to understand what is happening here. For the sake of context,
>> here is the PHP code in TFA:
>> $db = new PDO('pgsql:dbname=database');
>> $stmt = $db->prepare("SELECT priv FROM testUsers WHERE
>> username=:username AND password=:password");
>> $stmt->bindParam(':username', $user);
>> $stmt->bindParam(':password', $pass);
>> $stmt->execute();
>>
>> What exactly does bindParam do? I read these pages in TFM but I still
>> do not understand what exactly is being sent to the database:
>> http://il2.php.net/manual/en/function.db2-bind-param.php
>> http://il2.php.net/manual/en/function.maxdb-stmt-bind-param.php
>> http://il2.php.net/manual/en/mysqli-stmt.bind-param.php
>>
>> I do not see how there could possibly be a prepared statement for a
>> user comment. I am not a programmer by trade, so I may be missing
>> something obvious. If so, a link and a friendly RTFM would be great.
>
> Typically, prepared statements do a couple of things. First, they ensure
> that values sent to the DBMS are properly "quoted". You'd be surprised
> how difficult a problem that is. Date and string values must be
> surrounded by quotes, but numerics shouldn't be. And how they're quoted
> depends on the DBMS you're using. So prepared statements take care of
> this for you.
>
Apparently, I missunderstood the meaning of the term "prepared
statements". I thought that it meant using values that only came from
my code, and no user-entered values. For instance, if the user had to
choose between the windows, linux, and mac forums, the code would look
like this:
switch ($userForum){
case (linux):
$sqlForum="linux";
break;
case (windows):
$sqlForum="windows";
break;
case (mac):
$sqlForum="mac";
break;
This is what I currently do. However, that approach would not work for comments.
> The second thing they do is examine the values you're attempting to pass
> into the database, and ensure they don't contain SQL injection type
> code. This is hard to explain, but it's relatively simple to insert
> "code" in place of an actual value, and do malicious things to your
> database, or obtain information you don't want users to see (like credit
> card numbers). If you're curious, search for "SQL injection" to get more
> information and see examples.
>
I am familiar with SQL injection, I wrote about it here:
http://what-is-what.com/what_is/sql_injection.html
--
Dotan Cohen
http://what-is-what.com
http://gibberish.co.il
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
>> $stmt = $db->prepare("SELECT priv FROM testUsers WHERE
>> username=:username AND password=:password");
>> $stmt->bindParam(':username', $user);
>> $stmt->bindParam(':password', $pass);
>> $stmt->execute();
[8<]
> I haven't followed this thread, so I don't know what you mean by, "I
> do not see how there could possibly be a prepared statement for a user
> comment." Maybe someone else can answer that part of your query.
Thanks Paul, that was a much better explanation than the one I was
attempting. I'm guessing the OP was being thrown off by the colons in
the SELECT statement above. I can see how those could look like
comments to someone not familiar with PDO and named parameters.
Ben
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
On Mon, Aug 17, 2009 at 10:10:47PM +0300, Dotan Cohen wrote:
> >> Logically, it does _not_ mean the same thing.
> >
> > Definitely not -- it would be a bit presumptuous to claim "If you do
> > X, the query is not vulnerable to SQL injection attacks" for just
> > about any value of X.
> >
>
> That is what I though: no magic bullet.
>
>
> > That said, I would recommend binding parameters if you can. It's a
> > cleaner way of separating the logic of a query from its data, and
> > theoretically more reliable than mysql_real_escape_string():
> >
> > http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements
> >
>
> I fail to understand what is happening here. For the sake of context,
> here is the PHP code in TFA:
> $db = new PDO('pgsql:dbname=database');
> $stmt = $db->prepare("SELECT priv FROM testUsers WHERE
> username=:username AND password=:password");
> $stmt->bindParam(':username', $user);
> $stmt->bindParam(':password', $pass);
> $stmt->execute();
>
> What exactly does bindParam do? I read these pages in TFM but I still
> do not understand what exactly is being sent to the database:
> http://il2.php.net/manual/en/function.db2-bind-param.php
> http://il2.php.net/manual/en/function.maxdb-stmt-bind-param.php
> http://il2.php.net/manual/en/mysqli-stmt.bind-param.php
>
> I do not see how there could possibly be a prepared statement for a
> user comment. I am not a programmer by trade, so I may be missing
> something obvious. If so, a link and a friendly RTFM would be great.
Typically, prepared statements do a couple of things. First, they ensure
that values sent to the DBMS are properly "quoted". You'd be surprised
how difficult a problem that is. Date and string values must be
surrounded by quotes, but numerics shouldn't be. And how they're quoted
depends on the DBMS you're using. So prepared statements take care of
this for you.
The second thing they do is examine the values you're attempting to pass
into the database, and ensure they don't contain SQL injection type
code. This is hard to explain, but it's relatively simple to insert
"code" in place of an actual value, and do malicious things to your
database, or obtain information you don't want users to see (like credit
card numbers). If you're curious, search for "SQL injection" to get more
information and see examples.
When you put something like "username = :username" in the arguments for
the prepare() function, the second parameter (:username) is really just
a placeholder for a value. It tells MySQL that this is where you want a
username to go in the final statement. The bindParam() function tells
MySQL the actual value you want to substitute for that placeholder. In
your case, it's a PHP variable named $user. When you call the execute()
function, it puts the values together with their placeholders, forms a
complete statement, and sends that off to the MySQL database engine.
I haven't followed this thread, so I don't know what you mean by, "I
do not see how there could possibly be a prepared statement for a user
comment." Maybe someone else can answer that part of your query.
Paul
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
>> Logically, it does _not_ mean the same thing.
>
> Definitely not -- it would be a bit presumptuous to claim "If you do
> X, the query is not vulnerable to SQL injection attacks" for just
> about any value of X.
>
That is what I though: no magic bullet.
> That said, I would recommend binding parameters if you can. It's a
> cleaner way of separating the logic of a query from its data, and
> theoretically more reliable than mysql_real_escape_string():
>
> http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements
>
I fail to understand what is happening here. For the sake of context,
here is the PHP code in TFA:
$db = new PDO('pgsql:dbname=database');
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE
username=:username AND password=:password");
$stmt->bindParam(':username', $user);
$stmt->bindParam(':password', $pass);
$stmt->execute();
What exactly does bindParam do? I read these pages in TFM but I still
do not understand what exactly is being sent to the database:
http://il2.php.net/manual/en/function.db2-bind-param.php
http://il2.php.net/manual/en/function.maxdb-stmt-bind-param.php
http://il2.php.net/manual/en/mysqli-stmt.bind-param.php
I do not see how there could possibly be a prepared statement for a
user comment. I am not a programmer by trade, so I may be missing
something obvious. If so, a link and a friendly RTFM would be great.
--
Dotan Cohen
http://what-is-what.com
http://gibberish.co.il
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
> "Note: If this function is not used to escape data, the query is > vulnerable to SQL Injection Attacks." > > Does that necessarily imply this: > "If this function is used to escape data, the query is not vulnerable > to SQL Injection Attacks."? > > Logically, it does _not_ mean the same thing. Definitely not -- it would be a bit presumptuous to claim "If you do X, the query is not vulnerable to SQL injection attacks" for just about any value of X. That said, I would recommend binding parameters if you can. It's a cleaner way of separating the logic of a query from its data, and theoretically more reliable than mysql_real_escape_string(): http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements Ben -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
2009/8/16 Caner Bulut :
>
> Hi Dotan,
>
> You can use htmlentities(), htmlspecialchars() and strip_tags() functions
> when you show your saved data on your web pages. mysql_real_escape_string
> function saved data into mysql DB with a secure way. But when you try to
> show data you still have to control it.
>
Thank you Caner. This is the function that I use to escape HTML after
it has been pulled out of the database:
function clean_html ($dirty, $noNewlines=0) {
$dirty = strip_tags($dirty);
$dirty = str_replace("\r\n", "\n", $dirty);
$dirty = str_replace("\r", "\n", $dirty);
if ($noNewlines==1) { $dirty = str_replace("\n", " ", $dirty); }
$dirty = ereg_replace( ' +', ' ', $dirty);
$dirty=trim($dirty);
$dirty = str_replace("&", "&", $dirty);
$dirty = str_replace("&", "&", $dirty);
$clean=htmlentities($dirty);
return $clean;
}
It is rather convoluted but straightforward in my opinion. In addition
to preventing XSS attacks, it converts newlines to *nix-style and
limits them to just two newlines in a row (or none, depending on
application). It also limits spaces to a single space and ensures that
all ampersands are escaped properly before sanitation with
htmlentities.
Dotan Cohen
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
> You should in pretty much all cases be safe with just using the > mysql_real_escape_string, which takes care of the - for you as well. > If I remember correctly, TFM once stated that mysql_real_escape_string does not prevent SQL injection attacks, though I am hard pressed to think of what it _is_ for, then. I now see that the manual has this note: "Note: If this function is not used to escape data, the query is vulnerable to SQL Injection Attacks." Does that necessarily imply this: "If this function is used to escape data, the query is not vulnerable to SQL Injection Attacks."? Logically, it does _not_ mean the same thing. -- Dotan Cohen http://what-is-what.com http://gibberish.co.il -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Sanitizing mysql inserts of user data
What you are doing here is potentially altering valid user information
coming into MySQL. For example, if someone legitimately enters in --
or ; into some string that is going to be put into MySQL, some comment
or such, then what is put in, and then put out if you display it,
won't be the same.
You should in pretty much all cases be safe with just using the
mysql_real_escape_string, which takes care of the - for you as well.
Adam.
On Sun, Aug 16, 2009 at 11:42 AM, Dotan Cohen wrote:
> I am sanitizing user-entered data before storing in mysql with this function:
>
> function clean_mysql ($dirty) {
> $dirty=trim($dirty);
> $dirty=str_replace ("--", "", $dirty);
> $dirty=str_replace (";", "", $dirty);
> $clean=mysql_real_escape_string($dirty);
> return $clean;
> }
>
> Is this good enough to prevent SQL injection attacks? Should I add
> anything else? Thanks!
>
> Dotan Cohen
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
Adam Randall
http://www.xaren.net
AIM: blitz574
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Sanitizing mysql inserts of user data
Hi Dotan,
You can use htmlentities(), htmlspecialchars() and strip_tags() functions
when you show your saved data on your web pages. mysql_real_escape_string
function saved data into mysql DB with a secure way. But when you try to
show data you still have to control it.
Thanks.
Caner.
-Original Message-
From: Dotan Cohen [mailto:dotanco...@gmail.com]
Sent: Sunday, August 16, 2009 9:43 PM
To: php-general.
Subject: [PHP] Sanitizing mysql inserts of user data
I am sanitizing user-entered data before storing in mysql with this
function:
function clean_mysql ($dirty) {
$dirty=trim($dirty);
$dirty=str_replace ("--", "", $dirty);
$dirty=str_replace (";", "", $dirty);
$clean=mysql_real_escape_string($dirty);
return $clean;
}
Is this good enough to prevent SQL injection attacks? Should I add
anything else? Thanks!
Dotan Cohen
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Sanitizing mysql inserts of user data
I am sanitizing user-entered data before storing in mysql with this function:
function clean_mysql ($dirty) {
$dirty=trim($dirty);
$dirty=str_replace ("--", "", $dirty);
$dirty=str_replace (";", "", $dirty);
$clean=mysql_real_escape_string($dirty);
return $clean;
}
Is this good enough to prevent SQL injection attacks? Should I add
anything else? Thanks!
Dotan Cohen
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
