Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts

    Mysql query not returning

    all,

    I'm sorry to post about this again, but I'm afraid I've corrupted my database. I have this on my first page:
    PHP Code:
    <?php

    mysql_select_db
    ($db$visitors) or die("Cannot find database!");

    $query "SELECT * FROM functions ORDER BY fname";
    $result mysql_query($query);
    $numRows mysql_numrows($result);
    $i 0;

    while (
    $i $numRows) {
        
    $fname mysql_result($result$i"fname");
        
    $fcode mysql_result($result$i"fcode");
        
    $ftype mysql_result($result$i"ftype");
        
    $fdesc mysql_result($result$i"fdesc"); ?>
          <tr>
            <td><a href="showfunction.php?fname=<?php echo "$fname"?>" target="_self"> <?php echo "$fname"?></a></td>
            <td> <?php echo "$fdesc"?> </td>
            <td><div align="center"> <?php echo "$ftype"?> </div></td>
          </tr>
        <?php $i++;
                          }

    mysql_close($visitors);
    and on my section page I have this:
    PHP Code:
    <?php

    //get function code here
    $query "SELECT fcode FROM functions WHERE fname = '" $_GET['fname'] . "'";
    $result mysql_query($query); //NOT RETURNING ANYTHING
    $numRows mysql_numrows($result);
    $fcode mysql_result($result0"fcode");

    mysql_close($visitors);
    ?>

    <body><div class="codeblock"> 
    <div class="title">Code:<br /> 
    </div><code><pre><?php echo "$fcode"?>
    </pre></code></div>
    <br /><pre><hr />
    </pre>
    I marked what is going on with the second query on the second page. I am not getting anything from the database. I have checked the records and I know they are in there. when I print $_Get['fname'] on the screen I see exactly what is in the field, so I'm not sure if I've corrupted something or not.

    can someone help me out? I have copied these 2 scripts over into my current scripts from backups I made yesterday, when the pages were running fine. Is it possible the scripts may be corrupts?

    and on a side note, in terms of uploading, downloading, and testing are concerned, what methods should I be using to ensure that I don't corrupt files, dbs, etc...

    I am constantly overwriting my files as I upload them. is that good practice? thank you!

  • #2
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Okay first things first, I know you have been told before, but DO NOT put user input directly into your queries and you should also be doing some kind of error checking. How do you even know if your query failed or not? You don't. Change this
    PHP Code:
    $result mysql_query($query); //NOT RETURNING ANYTHING 
    to this
    PHP Code:
    $result mysql_query($query) or die(mysql_error().'<br>'.$query); //NOT RETURNING ANYTHING 
    Post your results and please, please, sanitize your data, look up mysql injection. Echo the query and run it in something like phpmyadmin, does it return what you want? Are you sure the fname on the screen IS in the database? As for uploading,downloading, etc.. yes it is fine but php is not binary so you need to be using ascii mode when you upload. Make backups to avoid things like this in the future.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #3
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts
    here's what I get:
    Code:
    SELECT fcode FROM functions WHERE fname = 'CountVbaLines()'No database selected
    I guess that makes sense.

    here is another part of the page's script, at the beginning:
    PHP Code:
    <?php
    include('../logs/logscript.php');
    ?>
    and the logscript code's first line is:
    PHP Code:
    <?php require_once('logfunctions.php'); ?>
    and then part of the function script is:
    PHP Code:
    $db "mydb";
    $dbaddress "address";
    $username "un";
    $password "pw";
    $visitors mysql_connect($dbaddress,$username,$password); 
    I guess I might not be understanding the difference between 'include' and 'require_once'. I got these two function related scripts from the net.

    I added this line in and of course it works:
    PHP Code:
    mysql_select_db($db$visitors) or die("Cannot find database!"); 
    obviously my error. Pretty bad one too.

    But...with that aside, is my file structure too complex? I am using the same script (logscript) to write visitor logs on each as well as query my database. this line:
    PHP Code:
    <?php require_once('logfunctions.php'); ?>
    allows me to create the connection to my one database where I keep the functions in one table and the visitor records in another table.

    That's probably not the best I know, but it's what I have right now. Do you have any suggestions for me to clean this up? And yes, I have to experiment with 'urlencode()', as I have never used it.

    A lot of my problems stem from the fact that PHP is much more strict than visual basic and that is my expertise. I really should switch to learning asp and .net instead of PHP, as vb and PHP really have nothing in common...

    Once again, I apologize for the simple oversight of not having a selection variable in my code.

  • #4
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts
    if you're still here Eng, I tried this:
    PHP Code:
    <a href="showfunction.php?fname=<?php echo urlencode("$fname"); ?>"
    but it encodes only the '()' characters of the query string. the function still appears. does urlencode only encode special characters?

    I may need to just study php more...if that is the case, don't be afraid to say so!

  • #5
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    No, no, not urlencode. That isn't what is used for preventing sql injection. Your structure is okay. include_once vs require_once: include_once will give you and error/warning and the page will keep processing if it can't find the include however require gives a fatal error and stops processing. The _once part is if it has already be included before it will try to use the save file rather than reload it if it hasn't changed.

    What urlencode does, is encodes anything that the browser might see as part of the url rather and just a string, so yes urlencode is doing what it should but to get it to it's true form when you use it, you can use urldecode which will get it back to the way it is in your database.

    To prevent mysql injection you need to use mysql_real_esacpe_string or prepared statements. Go to www.tizag.com and look up mysql injection, they tell you what it is and how to prevent it. Then come back here saying you read and that you understand why it is important. As for needing to learn more php, yes. I think you should study php more. A lot of your questions are simple and would be solved on your own if you read the manual or did any kind of error checking. There is a lot to learn in php.

    A pitfall that most beginners make when learning a language is they just copy and paste code that they found online and use it but they have no idea what the code is doing and how it is doing it. I'm referring to the include vs require. The php manual if you had read states it pretty clearly.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • Users who have thanked _Aerospace_Eng_ for this post:

    ajetrumpet (10-16-2010)

  • #6
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by _Aerospace_Eng_ View Post
    What urlencode does, is encodes anything that the browser might see as part of the url rather and just a string, so yes urlencode is doing what it should but to get it to it's true form when you use it, you can use urldecode which will get it back to the way it is in your database.
    I understand this completely, but the question about using that function is...what's the difference? I've already found a list of encoding characters that are used in PHP, so what's to stop somebody from using those to get to the true query string?

    and not only that Eng, but what's the difference between:
    PHP Code:
    <a href="showfunction.php?fname=AccWinPosSize()" target="_self"AccWinPosSize()</a
    and this:
    PHP Code:
    <a href="showfunction.php?fname=AccWinPosSize%28%29" target="_self"AccWinPosSize()</a
    There isn't much difference there! Why would I use 'urlencode' in that situation anyway? What good does it do me? Are you referring to automation that's going around the net like robots? As in, not giving the q string directly to programs like that? I guess what I'm saying is that the difference between those two lines of code is so marginal, why bother?

    Quote Originally Posted by _Aerospace_Eng_ View Post
    To prevent mysql injection you need to use mysql_real_esacpe_string or prepared statements. Go to www.tizag.com and look up mysql injection, they tell you what it is and how to prevent it. Then come back here saying you read and that you understand why it is important.
    I READ IT.

    I guess the thing I didn't understand is the SQL syntax for Mysql. For example, Tizag says the following will cause damage:
    PHP Code:
    // user input that uses SQL Injection
    $name_bad "' OR 1'"
    and then he goes on to say that it results in this:
    PHP Code:
    SELECT FROM customers WHERE username '' OR 1'' 
    Yes, the string is sensible, but the portion after the "=" sign does not, from what I understand about MS languages anyway. That is just something I will have to figure out and understand myself.

    I do understand the 'escape' concept in PHP, as when I insert records into my DB, I currently replace all "\" characters in a snippet of code with "\\\\". Also for single and double quotes. I'm not really interested in understanding WHY, but rather just memorize the rule of what works in terms of these sort of things.

    For example, in VB, why in the world does the "." qualifier work in some situations and the "!" qualifier doesn't? Again, that's something I do care about, but understanding why doesn't necessarily make you any smarter, because at the end of the day the program only works if it can be compiled. Does that make sense?
    Last edited by ajetrumpet; 10-16-2010 at 11:07 PM.

  • #7
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    urlencode may not be what you need, it is just good practice so the browser understands the urls.
    This basically says select everything from customers where username is blank or TRUE. It is very likely there won't be someone with a username of '' but WHERE 1 is essentially WHERE TRUE which is the same in this case as SELECT * FROM customers
    PHP Code:
    SELECT FROM customers WHERE username '' OR 1'' 
    Now that user was able to return everything from your DB for that table. We both know that a customers table will likely have addresses, phone numbers, amongst other things.

    Yes I agree it only works if it can be compiled but if it can't be compiled and you know why, then you know how to fix it.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #8
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts
    Eng,

    so are my two examples below correct? when I used urlencode(), it only changed the two special characters. is that what's supposed to happen?

    I do know, for example, that '%20' represents a space in a URL. If that is all it does, I'm not too worried about I guess, but I will start doing it.

    so in my db example, could someone type in the following and inject my db:
    PHP Code:
    www.mydomain.com/functions/showfunction.php?fname=AccWinPosSize()+or+
    when I type that into the browser I get an error on the $result variable when it fetches the array.

    so is it subject to injection? as in, could the table be deleted as in the Tizag example?

    I really appreciate your insight too. It's helped me a great deal.

  • #9
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Tizag does exaggerate a little as php won't allow you to run more than one query at a time. Yes you are right in your examples. As I said doesn't pertain to you but say you wanted to pass a path in your url, you would need to use urlencode because the browser might think it is just another url with a path in as opposed to a url with a query string in it. What is the error it gives you? They don't have to put a plus sign or they could so something like
    PHP Code:
    www.mydomain.com/functions/showfunction.php?fname= OR 
    PHP will see ' OR 1' defined as the $_GET['fname'] and your query would end up being
    PHP Code:
    SELECT fcode FROM functions WHERE fname '' OR 
    So yes your code is still open to injection. With your functions it isn't a big deal but the point I am trying to make is if you have other tables and you aren't taking precautions to prevent sql injection then sensitive data could be viewed. I'm not so sure why you are trying to argue against preventing sql injection. Just prevent it.

    Note: in this thread if I try doing ?fname=' OR 1 the ' is stripped out by the forum because it is trying to prevent bad input. One thing you have to learn and you have to learn now is you can never trust the input from a user. You must treat the user as if they have bad intentions. In doing so you actually do prevent the ones with bad intentions from causing harm to your site.
    Last edited by _Aerospace_Eng_; 10-17-2010 at 02:33 AM.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #10
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts
    the error I get what I do what I mentioned is:
    Code:
    Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 5 in .....(directory)
    I also tried what you had mentioned:
    PHP Code:
    ?fname= OR 
    I typed that directly into the browser URL and it gave me the same error. the GET variable was read by the script as:
    PHP Code:
    OR 
    and the URL actually became:
    PHP Code:
    ?fname=%20OR%201 
    so it doesn't look like the injection test that I'm doing here is working.

    I also get the same error if I type this in:
    PHP Code:
    showfunction.php?1=
    Last edited by ajetrumpet; 10-17-2010 at 04:30 AM.

  • #11
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    Well in URLs it isn't so bad but it is more of an issue when you are using forms with either $_GET or $_POST. The browser in this case is encoding the url for you but when you use a form nothing is encoded and the things you have tried would create a valid query. As for the warning again if you had done any kind of error checking it would have never gotten that far. Learn to use or die(mysql_error()) when you run your queries if your site is still in production. Remove them once you have things working.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #12
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts
    one more question for you Eng.

    If I open a connection to a database and my code errors out using 'die()' before it gets to the 'mysql_close()' command, does that connection hang out there? and what happens if I do this 100 times? are those connections ever killed, and would GoDaddy possibly have some sort of 'cleanup' process on their servers to make sure that all connections are timed out properly? either that, or killed?

  • #13
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    They will likely close eventually but you might get something like too many users connected. Your script really shouldn't ever die in production because you know things work. If something goes wrong in a production script, log the error, check your logs continue the script.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #14
    Regular Coder ajetrumpet's Avatar
    Join Date
    Jul 2009
    Location
    Iowa City, IA
    Posts
    407
    Thanks
    44
    Thanked 5 Times in 5 Posts
    thanks much Eng! I'm sure I'll be back another day to bug you even more.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •