Creating a user in MySQL DB

Question

Hi I am trying to write a script to create a user for a secure login/registration page. Part of the proposed solution is:

CREATE USER "sec_user"@'localhost' IDENTIFIED BY 'eKcGZr59zAa2BEWU';
GRANT SELECT, INSERT, UPDATE ON "secure_login".* TO "sec_user"@'localhost';

When I use this - it highlights errors on the @ sign, if I remove all quote marks it highlights an error on the 'BY' - I am not sure why.

Why are you trying to create multiple MySQL users? You're application should have it's own users table and possibly also it's on registrations table — SpacePhoenix, Apr 24 '18 at 12:56
Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — Nico Haase, Apr 24 '18 at 13:09
@SpacePhoenix Thanks for the post. That is what I am trying to do. Creat a registration page and logon page for a web app. — Chris, Apr 24 '18 at 20:31
This does not make any sense @Chris. You are mistaking database users with application users. — Mike Doe, Apr 26 '18 at 13:09
@emix thanks for the comment. But to register a user do I not need to write to a table with a user account with limited functionality? Sorry if I have gotten this set up wrong. — Chris, Apr 26 '18 at 15:42

Paddy Popeye · Accepted Answer · 2018-04-26T13:03:05.400

I think your issue may be the way you are mixing the single and double quotes. Try the exact syntax as shown below this might solve the problem

CREATE USER 'sec_user'@'localhost' IDENTIFIED BY 'eKcGZr59zAa2BEWU'; GRANT SELECT, INSERT, UPDATE ON secure_login.* TO 'sec_user'@'localhost';

Above should work to create a user but as you're objective is actually to create a registration for users below is an implementation from one of my own projects. You firstly will need to create a customer table in youe database and then something like below....

<?php


        $con = mysqli_connect("<yourHOST>","<yourUSER>",",<yourPASSWORD">,"<yourDBname>");
    if (mysqli_connect_errno()) {
        echo"
            Failed to connect to the MySQL DB:".mysqli_connect_error();
        }

    /getting the ip address of the customer
function getIp() {

    $ip = $_SERVER['REMOTE_ADDR'];
        if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
            $ip = $_SERVER['HTTP_CLIENT_IP'];}
        elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
            $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
        }

    return $ip;

    if (isset($_POST['cust_acc'])) {
        global $con;
        $ip = getIP();
        $cust_name = $_POST['cust_name'];
        $cust_email = $_POST['cust_email'];
        $cust_pass = $_POST['cust_pass'];
        $cust_image = $_FILES['cust_img']['name'];
        $cust_image_tmp = $_FILES['cust_img']['tmp_name'];
        $cust_ctry = $_POST['cust_ctry'];
        $cust_city = $_POST['cust_city'];
        $cust_contact = $_POST['cust_cont'];
        $cust_addr = $_POST['cust_addr'];


        $check_email = "SELECT * FROM customers WHERE customer_email='$cust_email'";
        $run_check_email = mysqli_query($con, $check_email);
        $check_rows = mysqli_fetch_array($run_check_email);
        $checked_email = $check_rows['customer_email'];

        if ($checked_email==$cust_email){
            echo "
                <script>alert('User Already Exists with submitted Email')</script>";
            echo "<script>window.open('checkout.php','_self')</script>
            ";

        }
        else{
        move_uploaded_file($cust_image_tmp,"customer/customer_images/$cust_image");
        $insert_cust = "INSERT INTO customers(customer_ip,customer_name,customer_email,customer_pass,customer_country,customer_city,customer_contact,customer_image,customer_address) 
VALUES('$ip','$cust_name','$cust_email','$cust_pass','$cust_ctry','$cust_city','$cust_contact','$cust_image','$cust_addr')";

        $run_insert = mysqli_query($con, $insert_cust);

Hi Paddy Popeye - I tried the above and get - error lines underneath the 'sec_user' with a message saying "Expecting ID or QUOTED_ID". — Chris, Apr 24 '18 at 20:55
hi.. have just tested this and created two users sec_user and sec_user2 with no problems. Did you copy and paste the command? What version are you using? — Paddy Popeye, Apr 24 '18 at 22:37
also try running the following and check the output ..... USE DATABASE secure_login; SELECT User FROM mysql.user; — Paddy Popeye, Apr 24 '18 at 22:40
Thanks for that - sorry I haven't looked into this recently. Been distracted. Thank you for the help! — Chris, May 23 '18 at 11:55

Creating a user in MySQL DB

1 Answers1