MySQL

[Cedars]

OK I read up a bit on the htmlentities stuff. I dont know if I have really got it.

 

you can use the [noparse]

[/noparse] tags for php code, makes the code prettier :steering:
[/quote]

OK so for the htmlentities it would go something like this:

if (isset(_POST['check_mark']));
{
 foreach (_POST['check_mark'] as checkmark)
 {
marked = htmlentities(check_mark);
marked = htmlentities(check_mark, ENT_QUOTES);

query = "SELECT * FROM MyTable 
WHERE (`Type` LIKE 'marked') 
ORDER BY Family,Genus,Name ASC";
}
}

So I have run check_mark through the htmlentities then passed the new variable 'marked' to the query. I would also need a return false escape right? (and if so, here is where I begin to get lost on where { these things } go)

 

Then part two you make a good suggestion. Part of the code I use was snippets from other sites modified to work as I need it and learn from it. Its a learning experience for me and having it wrote out the long way helps me understand how these things all work together.

 

So you are suggesting this for the query:

 

marked = htmlentities(check_mark);
marked = htmlentities(check_mark, ENT_QUOTES);
result = mysql_query("SELECT * FROM MyTable
WHERE (`Type` LIKE 'marked')
ORDER BY Family,Genus,Name ASC";) or die ("Error in query: . ".mysql_error());

The (type like marked) was the only way I was getting the variable to pass through. Before that I got a blank page or something like that.

 

This is what you mean for suggestion 3 right (Error in query: stuff)?

I had snipped out that part for the original post just to lessen my post some, but its there in my script :evil:

 

Thanks for your help with this!

[alexander]

here's what i mean... this would be the real life application of something like what you have there

 

$b_debugmode = 1; // 0 || 1

$system_operator_mail = "your_email@your_website.com"; //who do we send this magicness to
$system_from_mail = "server_application@your_website.com"; //this will remind us where this error came from

if (isset(_POST['check_mark']));
{
 foreach (_POST['check_mark'] as checkmark)
 {
   $marked = htmlentities(check_mark, ENT_QUOTES); //just need to do this once :evil:

   db_query("SELECT * FROM MyTable WHERE (`Type` LIKE '$marked') ORDER BY amily,Genus,Name ASC"))
 }
} 

function db_query( $query )
{
 global $b_debugmode;

 // Perform Query
 $result = mysql_query($query);

 // Check result
 // This shows the actual query sent to MySQL, and the error. Useful for debugging.
 if (!$result) 
 {
   if($b_debugmode)
   {
     $message  = '<b>Invalid query:</b><br>' . mysql_error() . '<br><br>';
     $message .= '<b>Whole query:</b><br>' . $query . '<br><br>';
     die($message);
   }

   raise_error('db_query_error: ' . $message); //this way if we are not debugging, we dont tell the customer what the info is, but rather email the admin :lol:
 }
 return $result;
}

function raise_error( $message )
{
 global $system_operator_mail, $system_from_mail;

 $bad_error=
 "Env:       " . $_SERVER['SERVER_NAME'] . "rn" .
 "timestamp: " . Date('m/d/Y H:i:s') . "rn" .
 "script:    " . $_SERVER['PHP_SELF'] . "rn" .
 "error:     " . $message ."rnrn";

 // open a log file and write error
 $fhandle = fopen( '/logs/errors'.date('Ymd').'.txt', 'a' );
 if($fhandle)
 {
   fwrite( $fhandle, $bad_error );
   fclose(( $fhandle ));
 }

 // e-mail error to system operator
 if(!$b_debugmode)
 {
   mail($system_operator_mail, 'error: '.$message, $serror, 'From: ' . $system_from_mail );
 }
}

[alexander]

oh i say real life application, i just wrote it, not from any of my previous projects or anything...

[Cedars]

here's what i mean... this would be the real life application of something like what you have there

 

 

 

 

Thats pretty great Alex! I am understanding most of it pretty well I think. One question. The ending row count follows this whole scheme or do I put that in below the query, and before the ending curly brackets }

 

Today I worked on adding radio buttons and more check boxes. Had a time of it with the second group of check boxes so I switched them to radios and got it to work. Discovered an error in db design that I can fix with the final product. Also forgot to add a select all radio in the last group (realized that on my way to town for dinner and groceries). Things around here really suffer when I get into a project.

 

Once I get this done I will post the whole thing and you can show me all kinds of shortcuts I missed in my attempt. That way others might benefit in their php mySQL learning experiences too!

 

OK when I hit preview, it gave me fits again even though I had replied via the button and it had the php start and end tags. So I dont know if I will be able to post it when I am done.

 

Thanks again for contributing to my learning!

[alexander]

just change the main part to something along these lines:

 

if (isset(_POST['check_mark']));
{
 foreach (_POST['check_mark'] as checkmark)
 {
   $marked = htmlentities(check_mark, ENT_QUOTES); //just need to do this once ;)

   $results = db_query("SELECT * FROM MyTable WHERE (`Type` LIKE '$marked') ORDER BY amily,Genus,Name ASC")); //run query, get results

   $result_rows = mysql_num_rows($results); //get number of rows

   echo "There were a total of $result_rows values found"; //display number of rows

   $results = preg_split('/[rn]+/', $results, -1, PREG_SPLIT_NO_EMPTY); //split the result string by new line

   for($i=0;$i<=count($results);$i++)
   {
     echo $i+1.". ".$results[$i]."n"; //display each result and its appropriate count
   }
 }
}

[Cedars]

OK I looked over your most recent post and I think I already know the answer to this:

 

Each _POST variable would need its own unique markerName run thru htmlentities right?

 

Then I just dont understand the preg split thingy. I read a bit about it and for this particular form I dont know that its needed and may be a bit above my skills at this point. I still cant get a second row of checkboxes to query right :lol:

 

Two sets of radio buttons (one set with three options, one set with 6 options) and thats working fine... but try to add one more checkbox to select all and all hell breaks loose. Radio select all works on both but not checkbox select all... bah...

[alexander]

well, you would use it on any variable that you did not set explicitly in code, but are planning to use in a query.

 

the reason for preg split is to take the information returned in the return variable from mysql, and split it by newline, so it can then be used as an array to display the results line-by-line.

 

i thought i commented that part pretty well...?

 

Each _POST variable would need its own unique markerName run thru htmlentities right?

technically not exactly, it does not need another temporary variable for it, you could, for example use the post var, sort of directly, as an array in your case

 

$post_arr=$_POST['check_mark'];

for($i=0;i<=count($post_arr);$i++)
{
 $results = db_query("SELECT * FROM MyTable WHERE (`Type` LIKE 'htmentities($post_arr[$i])') ORDER BY amily,Genus,Name ASC")); 

 $result_rows = mysql_num_rows($results); //get number of rows

 echo "There were a total of $result_rows values found"; //display number of rows

 $results = preg_split('/[rn]+/', $results, -1, PREG_SPLIT_NO_EMPTY); //split the result string by new line

 for($i=0;$i<=count($results);$i++)
 {
   echo $i+1.". ".$results[$i]."n"; //display each result and its appropriate count
 }
}

[Cedars]

I am wondering.

 

If I am connecting to a mySQL DB via a search form, wouldnt I be able to insure no injection attacks via mySQL DB User Privs? And if so, being as this is a search only DB, would I only allow SELECT privs for the connecting user or are there other privs needed to ensure search displays, such as CREATE TEMPORARY TABLES, CREATE ROUTINE, etc.

 

It is a simple search, no math needed, no creating graphs, etc.

 

Thanks again!

[alexander]

You never want to be able to run direct queries from the search functions, there are many reasons for that, main one being "Hello SQL injection vulnerability!"

 

You can not ensure sql injection protection by only using mysql privileges. one layer of protection is never a good idea, letting people do direct sql querying is an even worse one, no matter what your user permissions are. Select lets me query the user database to find out other users, and get hash dumps of their passwords.... if i'm not mistaken

[Tormod]

Yeah you can get hash dumps but you'd still need to know how those passwords are encrypted. I have been able to write scripts which validates encrypted (and salted) passwords against the database, but I've never seen a way to decrypt the passwords. (Well, you're the hacker :friday: ).

 

It is probably still a good idea to set up a user with limited access. That's what I do in all cases. And don't use the username and password in the code but set it in a separate file and hide that file well...outside the web root if you can.

[Cedars]

You never want to be able to run direct queries from the search functions, there are many reasons for that, main one being "Hello SQL injection vulnerability!"

 

You can not ensure sql injection protection by only using mysql privileges. one layer of protection is never a good idea, letting people do direct sql querying is an even worse one, no matter what your user permissions are. Select lets me query the user database to find out other users, and get hash dumps of their passwords.... if i'm not mistaken

 

Good points, but this DB is just a birds of Crex database. No passwords or vital info. Just something I wouldnt want accidently messed up and the search form is php.

 

So yes an additional layer of protection via mySQL user privs is a good idea. Along with the stripping stuff you gave me earlier in the thread of course.

 

So the remaining question is, when setting up mySQL user privs, just to query the bird DB and return the info via the php script, should I just set the privs to SELECT or are other privs needed to ensure the script runs right?

 

Its just check boxes and radio buttons that define the query.

 

Tormod:

 

I do use a separate file for access to the DB that the script pulls on load. Good reminder though.

[Tormod]

I think Alex means that you can query the MySQL database for the database users, not the info you create about eventual other kinds of users.

[Cedars]

I think Alex means that you can query the MySQL database for the database users, not the info you create about eventual other kinds of users.

 

OK, I see what you mean. The query is not joined to the users in any way except the connect info needed. It is this connect user which I should make sure has no permissions other than to run the search query via the form, right?

 

Which brings me back to the original Q about this connection user only needing SELECT permissions right? Or do they not need any specific permissions other than whats in a basic connect?

 

Its a new host and quite different from my previous host. Lots of this stuff was handled by the provider and I dont know what the settings were. There was a method used to create permissions, but the thing worked fine so I didnt need to change anything from the default rules used by the host.

[Tormod]

I think you got it right. The user only needs to be granted SELECT access on the specific database in question.

[Cedars]

Thanks Alex and Tormod for all your help!

[alexander]

well you actually dont need select permissions to connect, you just need to create a user, but what's a user that can not do anything in your database. So you can grant them select permission on the search database, which will make that end, decently secure. But what i was merely saying is that you always want to layer security. Nobody should be able to pass data directly to your mysql query without some form of checking the data, because that is how vulnerabilities are born. For example, what's stops me from stopping your query, and injecting arbitrary user-side code into your search field. You see where simple checking for what is entered in the field is a key? Don't make another page that is vulnerable to XSS, or some form of sql injection, check your search data more then onece to ensure that it is search data, not a way to make you, the coder, look like a noob :)

[Cedars]

well you actually dont need select permissions to connect, you just need to create a user, but what's a user that can not do anything in your database. So you can grant them select permission on the search database, which will make that end, decently secure. But what i was merely saying is that you always want to layer security. Nobody should be able to pass data directly to your mysql query without some form of checking the data, because that is how vulnerabilities are born. For example, what's stops me from stopping your query, and injecting arbitrary user-side code into your search field. You see where simple checking for what is entered in the field is a key? Don't make another page that is vulnerable to XSS, or some form of sql injection, check your search data more then onece to ensure that it is search data, not a way to make you, the coder, look like a noob :)

 

But I am a clueless noob! :shrug:

 

Keep in mind I'm an ol' lady so its like trying to teach your buddies birdwatching/butterfly collecting mom this stuff. I am really trying not to drive you skilled coders nuts with questions but I just dont think like a hacker/kiddie scripter.

 

So stopping the script and injecting brings up a question. For this to work, a person would have to know how a query is sent (via the form fields) and rewrite the url right? And that would involve knowing how to put it into the URL address correctly formatted (easy enough to figure out)?

 

So if the above is true. Would field size limits also help limit the ability to inject? If so, what is the minimum field size for a malicious injection to work?