bakertaylor28
-
Posts
21 -
Joined
-
Last visited
Posts posted by bakertaylor28
-
-
On 9/5/2020 at 5:27 PM, maxxd said:
MySQL queries are MySQL queries, regardless whether they're run in PHP, Ruby, MySQLWorkbench, or the command line. Unless you're using an ORM like Doctrine or Eloquent, it's all the same keywords and operations.
But yes, you can have multiple criteria in a WHERE clause rather easily. Also, please keep in mind that doing a SELECT * is less efficient than selecting the fields you need specifically.
I get that part, but the syntax is different in how it's deployed depending upon the language you're using.
-
17 hours ago, kicken said:
If you have a SQL injection vulnerability, separate tables will not necessarily help you. Depending on the kind of vulnerability / setup you have the attacker may be able to run queries against arbitrary tables, or just drop the entire database.
Aside from that, separate tables will become a maintenance nightmare when next year you need to add a few extra fields to the schema for all 10,000+ tables or whatever. Or you decide you want to be able to aggregate the data for a report and have to query every table and combine the results.
Multiple tables with the same schema is the wrong solution 99.999% of the time, and you're not in that small 0.001%.
Your wrong. Databases are designed to process data. They are designed to deal with tables that have many rows quickly and efficiently so long as you set them up properly. As an example, I have a table that records every login attempt to a website. It currently has about 2.2 million rows in it. I just queried it to find all my login attempts. It came back with 2976 rows. Guess how long it took the database to find those records out of the 2.2 million total records? 0.271 seconds. Yes, less than a second.
If they can inject a DROP TABLE command, they can probably inject a SHOW TABLES command to get a list of all your tables then loop that list and drop each one. Your approach buys you nothing.
You need to make sure you're not vulnerable to an injection attack in the first place, and then have solid backups to restore from just in case something does happen.
That helps my understanding of it quite a bit. (I'm quite new to SQL and have only done a modest bit of PHP- This is my first project from scratch.) . So the best structure, If I'm understanding it, is to combine everyone's data into the same table and use a WHERE statement. I'm also guessing that you can use WHERE column A=something AND column B = something else AND column C= something else (going as many columns as need be) if necessary? I'm having a hard time finding examples of SQL queries within the context of PHP that actually work.
-
1 hour ago, requinix said:
Is it the same set of information for everyone?
Definitely do not do this. No offense, but it doesn't sound like you understand what databases do or how they work.
Thing is, you would never actually do that. You would run a SELECT query with a WHERE clause. And the system will be able to pull up the information far more efficiently than you could if you tried to do it yourself.
To clarify, it would be the same table structure for everyone, but would be different user-supplied data for each account, in a setting where data integrity matters, forensically speaking. It would seem that by providing the account with it's own database table, it then, worst case, minimizes the impact of SQL injection- because the injector then only theoretically would effect only one particular table Vs. the potential for wiping out the whole database (and thus effecting the integrity of the whole SQL server) if everything is stored in one table. The idea is essentially de-centralizing vs. having a central table.
Also, correct me if I'm wrong, but It would seem that using a WHERE clause would take much more server-side resource than using a SELECT * clause, given a heavily populated data set with a WHERE clause (given the nature of the thing) versus a much less populated data set with a SELECT * clause where each account has it's own table. The real goal is to isolate the data as much as possible (to where potential attacks effect a minimal data set), while still making it accessible to the superuser and administrator accounts.
For example, if someone were able to inject a DROP TABLE command, if I have the data isolated into several tables, it only effects minimal data, vs. if it's all in one table, then everything gets dropped. This could buy more time to perhaps shut down a server to stop an attack.
-
5 minutes ago, requinix said:
Is it the same set of information for everyone?
Definitely do not do this. No offense, but it doesn't sound like you understand what databases do or how they work.
Thing is, you would never actually do that. You would run a SELECT query with a WHERE clause. And the system will be able to pull up the information far more efficiently than you could if you tried to do it yourself.
The idea is that by pulling data from a smaller table in which all the data would be used (e.g. given a SELECT * from table argument) would be faster and more efficient than having to use the form of Select * from table WHERE parameter1=x and parameter2=y type of argument.) because it would seem that the latter takes much more time and resources to execute, and would be more secure because we're partitioning tables.
-
10 minutes ago, requinix said:
Probably. Going to need to hear more about it than just how it's "individualized information".
Definitely not.
The application I'm building issues an account and then basically stores a set of property information (such as make/model, serial number, etc.) to the individual account and allows the user to recall the information in his own account but not in other user's accounts. It also will allow "marking" certain property missing, etc. to display to super users (e.g. people manually verified to have a right to know the info and then granted a superuser status) using an administrative "level" model , while the system administrator has access to all of the information everywhere.
My first instinct is to give each user their own table in the same database (which will be a seperate database from login credentials) in order to keep a while loop from having to cycle through like half a million rows to select only the pertinent info. in that displaying a whole table is more straight forward than using a WHERE statement.
-
This seems to be because you're mixing obsolete sql with sqli. You can't use both in the same database call. The big problem I see with this is that you should be using prepared statements in the first place.
-
Following the KISS principle, The way I would handle this is to have each button submit to a different php script and do a header redirect if need be. This approach is particularly useful if you can't use mod_rewrite such as if you're using nginx.
-
What is the best table structure when constructing what will be a potentially large multi-user platform?
Example- Each user inputs their own individualized information into a table, for recall only to that specific user and to certain other users defined as administrators or half-administrators super users.
Would it be better to store this all in a single table, or to give each user their own individual table on formation of the account?
-
4 hours ago, mac_gyver said:
the username is a value that originally came from external submitted data. depending on your registration code's validation logic, it could contain anything, such as a hexadecimal encoded string, consisting of just letters and numbers (a hexadecimal encoded string, in a non-string context, will be decoded into whatever string it actually contains), or it could contain single-quotes, that if put directly into an sql query will allow sql injection.
it sounds like you think that using a prepared query ONCE, when the data was first submitted and stored makes the value safe to use in all future queries. it does not. it only made that first query safe.
any value that ever came from external, unknown, or dynamic data (recently, a year ago, or a year from now, when your application gets updated to get usernames via a call to an external api, where you don't know what type of characters it might contain) must treat the value as unsafe in whatever context the value is being used in (sql, html/css/javascript, email header, filename, system/shell, ...)
I was under the impression that a prepared statement acted in the same manner of permanency as a function such as htmlspecialchars - taking a permanent parameter binding when we call bindparam in the prepared statement. e.g. that the parameters remain bound for the rest of the statement any time you call SQL for the rest of the session. Thanks for the correction.
-
16 minutes ago, requinix said:
It's not just about user input. It's about not knowing right there at that moment whether the value is safe. Can you guarantee that there is no possible way a username could have anything wrong with it? Not just in the database but also the value stored in the session?
Modern day "hacks" are not about finding a single problem that gives someone complete access. They're about finding a series of small vulnerabilities that combine to form something large. In your case, perhaps there's a way to get a username that's kinda invalid into the database, and then maybe there's a flaw in some code that loads the username into the session, and then maybe there's a flaw in this particular script where the bad username in the session can turn into SQL injection.
That's why application security is so difficult: to protect yourself you have to make sure that everything is covered, but for a malicious user all they have to do is find one or two problems.
I would have never really thought about it that way, in that I would have to double check the login script to make sure I'm not inadvertently setting a session var using post data. I'm still quite new to SQL though have dabbled a bit in php, though mostly have done content not involving databases until now. Thanks for your help.
-
4 minutes ago, requinix said:
"Quoted" is the term ("escaped" is like what you might do with backslashes) but yes: just like how strings in PHP need quotes around them, so do strings in SQL.
But adding quotes isn't enough because there's a risk the username will have something harmful in it. Use a prepared statement.
Why do I need a prepared statement IF I'm not accepting user input, and the material already in the database was derived from a prepared statement? It would seem that a prepared statement isn't necessary here because, correct me if I'm wrong" injection only concerns parsing user input into a database, not reading a database.
-
let me guess, $user needs to be escaped in single-quotes for SQL to be able to read the variable?
-
8 minutes ago, requinix said:
while ($row = $result->fetch_assoc()) { $priv === $row["su"]; }
What is that second line doing?
My first thought. (and it actually worked with the issue with $user, as I was able to successfully echo the variable to demonstrate it had been fixed.) However changing it to $priv = $row['su'] still threw the same error, as if to say that SQL isn't properly returning $row['su'] at all- but if this were the case I'd expect an SQL error a )s opposed to variable undefined.
-
8 minutes ago, Barand said:
I've read that- in fact it was my fist stop in approaching this- though I'm not sure how it applies here, as none of the examples there seem to be directly on point.
-
5 hours ago, requinix said:
You're putting the entire table in there. The only thing you need you need to repeat is the data row (that being the second <tr> and what's inside it).
You'll also have a bit of a problem with where your code is. The loop is before your <html> so you can't put the table row in there. Instead you need to move the loop itself.
That also means moving the mysqli_close, since you can't very well read the query results once you've closed the connection. The good news is that you don't need to close the connection in the first place. PHP will do it for you when the script ends.Of course, i realized to put the entire table in there and close the sql connection just before closing the function. (Mind you I ended up wrapping all of this within a function, and then calling the function in the HTML code after closing the main php tag and issuing <!DOCTYPE HTML> due to the complexity of the HTML code everywhere else to keep the HTML halfway readable, other than the code for the table itself which doesn't make a difference, as well as to keep the end user from reading the php code.)
But you helped me figure out the problem- namely that I can't write these things to variables and then simply call the variable in HTML.
-
My login script stores the user's login name as $_SESSION[ 'name'] on login. For some unapparent reason, i'm getting errors stating that $user and $priv are undefined variables, though I've attempted to define $user as being equal to $_SESSION['name'], using $user to look up the the user's privilege level (stored as the su column ) in the SQL table, and then where the result of the sql query is $priv which is then evaluated in an if statement. I can't seem to figure out why this might not be working.
The code I'm using:
<?php session_start(); function verify() { //verify that the user is logged in via the login page. Session_start has already been called. if (!isset($_SESSION['loggedin'])) { header('Location: /index.html'); exit; } //if user is logged in, we then lookup necessary privleges. $_SESSION['name'] was written with the login name upon login. Privleges // are written in db as a single-digit integer of of 0 for users, 1 for administrators, and 2 for special users. $user === $_SESSION['name']; //Connect to Databse $link = mysqli_connect("127.0.0.1", "database user", "password", "database"); if (!$link) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } //SQL Statement to lookup privlege information. if ($result = mysqli_query($link, "SELECT su FROM accounts WHERE username = $user", MYSQLI_STORE_RESULT)) { //LOOP TO CYCLE THROUGH SQL RESULTS AND STORE Privlege information as vairable $priv. while ($row = $result->fetch_assoc()) { $priv === $row["su"]; } } // close SQL connection. mysqli_close($link); // Verify privleges and take action. Only a privlege of "1" is allowed to view this page. A privlege of "2" indicates special //accounts used in other scripts that have certain indermediate additional functions, but are not trusted administrators. if ($priv !== 1) { echo $_SESSION['name']; echo "you have privlege level of $priv"; echo "<br>"; echo 'Your account does not have the privleges necessary to view this page'; exit; } } verify(); ?>
-
29 minutes ago, requinix said:
That's true, as far as I can tell what you're talking about. But that loop you have only uses some variables. Every time through the loop it updates those same five variables to have five new values. By the time the loop ends, all you have left is whatever the five latest values were - everything that happened before is lost.
You've got a loop that can go through all the results. You've got some HTML for a table row. Instead of using the loop for some variables, use the loop for the table row.
so, if I'm understanding you correctly, then the code would look something like this?
if ($result = mysqli_query($link, "SELECT * FROM accounts", MYSQLI_STORE_RESULT)) { while ($row = $result->fetch_assoc()) { echo '<table><th></th>'; echo '<td>$row["id"]</td>'; // and so on for each column... } } mysqli_close($link);
-
I thought that was what the whole point of what that loop did as it is written, or at least that is the explanation on practically every site I've seen, in that this is the code example given practically everywhere to do what I'm trying to do.
-
1 minute ago, requinix said:
Besides the header, your table has exactly one row in it. If you want to display multiple data rows then you're going to need multiple table rows too.
So then how would you do this if there's no way to hard code the exact number of rows into the script?
-
I'm trying to simply display the contents of an entire database for the purpose of building an administrative super-user into the back-end to remove login credentials without having to resort to having to manually interact with Mariadb or PHPmyadmin. However, the code is only returning the last row that was entered into the database. I can't seem to figure out why it's not working as intended. The code is intended to simply display the entire contents of the database into an html table.
The code i'm using:
<?php $link = mysqli_connect("127.0.0.1", "database user", "password", "login"); if (!$link) { echo "Error: Unable to connect to MySQL." . PHP_EOL; echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL; echo "Debugging error: " . mysqli_connect_error() . PHP_EOL; exit; } if ($result = mysqli_query($link, "SELECT * FROM accounts", MYSQLI_STORE_RESULT)) { while ($row = $result->fetch_assoc()) { $id = $row["id"]; $username = $row["username"]; $pass = $row["password"]; $email = $row["email"]; $su = $row["su"]; } } mysqli_close($link); ?> <!DOCTYPE html> <html> <head> </head> <body> <table> <tr> <th>id</th><th><th>user name</th><th>password</th><th>email</th><th>elevated privlege</th></tr> <tr> <td> <?php echo $id; ?> </td> <td> <?php echo $username; ?> </td> <td> <?php echo $pass; ?> </td> <td> <?php echo $email; ?> </td> <td> <?php echo $su; ?> </td> </tr> </table> </body> </html>
Why (isset($_POST['submit'])) function not working?
in PHP Coding Help
Posted · Edited by bakertaylor28
This should be <form action="/multi.php" ...> because, assuming multi.php is in the root directory of the server, you need the forward slash to denote any address that is on the resident server and is not a GET Request via http. If it is in a subdirectory, it will be action="/path/to/file.php"