根据PHP和MySQL中的另一个表（关系数据库），无法从表中选择所有结果

问题描述：

I am trying to finish this website I am currently creating, but I am kind of stuck.

I want to create a table called "orders" in my DB. I want this table to be related to my users table so that when the user goes to his "orders.php" page (once logged in already) he sees all his current and previous orders.

These would be my table fields/cols:

id username ordernumber description quantity total

This is my approach:

Whenever a new order is created, insert all the table fields/cols depending on the user's choice (selected stuff for the order), but the username would be the only value gathered from a $_SESSION or $_COOKIE variable, which holds the username. Then, once the user goes to orders.php, I will execute a query to show all the orders that only that username has ordered. Please note that I do sanitize all my input/output and I do not store sensitive data in my cookies. My system is designed so it only uses the session as the method of authentication, therefore you need to login every time you close the browser but that is fine.

1) Is this a safe approach? Do you have any suggestions/comments?

2) Could you help me construct the query?

I haven't really worked with relational databases, so I am kind of lost. How can I call all the orders from table "orders" where username = "username from the session"?

So far I have this:

"SELECT * FROM orders WHERE username = ? " //(Using PDO)

I know that this will work but my concern is in case of getting a session hijacked or something like that, then a user would be able to retrieve any users' orders, or not?

Thank you for explaining this a little bit further and helping me out!

Cheers!

我正在尝试完成我正在创建的这个网站，但我有点卡住了。 p> \ n

我想在我的数据库中创建一个名为“orders”的表。我希望这个表与我的用户表相关，这样当用户进入他的“orders.php”页面时（一旦登录），他就会看到他当前和之前的所有订单。 p>

这些将是我的表字段/ cols： p>

id username ordernumber description quantity total p>

这是我的方法： p>

每当创建新订单时，根据用户的选择（订单的选定内容）插入所有表字段/列，但用户名将是从$ _SESSION收集的唯一值或 $ _COOKIE变量，它包含用户名。然后，一旦用户转到orders.php，我将执行一个查询以显示只有该用户名已订购的所有订单。请注意，我确实清理了所有输入/输出，并且我没有将敏感数据存储在我的cookie中。我的系统设计为只使用会话作为身份验证方法，因此每次关闭浏览器时都需要登录，但这很好。 p>

1）这是一种安全的方法吗？？你有什么建议/意见吗？ p>

2）你能帮我构建一下这个查询吗？ p>

我还没有真正使用过关系数据库，所以我有点迷茫。如何调用表“orders”中的所有订单，其中username =“会话中的用户名”？ p>

到目前为止，我有这个： p>

  “SELECT * FROM orders WHERE username =？”//（使用PDO）
  code>  pre> 
 
 我知道这会有效，但我担心的是 一个会话被劫持或类似的东西，那么用户是否能够检索任何用户的订单？ p> 
 
 
感谢您进一步解释这一点并帮助我！  / p> 
 
 
干杯！ p> 
  div>

答

Be careful! Please don't create a plain text cookie containing a human-readable user id (like user2345995 or OllieJones). It's far too easy for a badguy to fake a cookie like that just by guessing, and then your users' information leaks out.

You're working in php. Therefore you can use php's session mechanism to store your userid and other values. php uses hard-to-guess session ids (SIDs) and stores them in either a cookie or as a sid=1234abcd9875 parameter in URLs.

For the sake of your system's integrity, please read up on this. It's actually a pretty well-designed feature and it's been in the wild for fifteen years or so: it's debugged.

http://php.net/manual/en/session.idpassing.php

If you're using the session system, you basically do this in your first page, your login page.

session_start();
...
$_SESSION['username'] = $username;  /* which you get by logging in */
...

On your order lookup page you do something similar to retrieve the username and use it in a query.

session_start();
...
$orderstmt = $pdoconn->prepare("SELECT * FROM orders WHERE username = :username");
$orderstmt->execute( array(':username' => $_SESSION['username']) );
...
while ($row = $orderstmt->fetch()) {
   /* use the row's data */
}
$orderstmt->closeCursor();

根据PHP和MySQL中的另一个表（关系数据库），无法从表中选择所有结果

相关推荐