Connecting to Google Cloud SQL in Google App Engine

Question

Basically I am deploying an simple website that has register and login. I have deployed into AppEngine and my Cloud SQL is in the same AppEngine Project. Project: http://cc-lab4.appspot.com/register.php

I created database and table in Cloud SQL, I want my register.php to connect to the cloud sql database and perform insert query.

I really want to use mysqli_connect() since I am familiar with this, PDO connection is new to me.

I don't know the exact way of connecting yet, havent been successfull at all. Anyone have idea how to use mysqli_connect() to Cloud SQL would be great.

After Editing by using one of the answers, still didn't work:

 //Variables for Database connection
 $user = "root";
 $pw = "root";
 $socket = '/cloudsql/'. $ENV{"cc-lab4:australia-southeast1:my-sql-artworks"};
 $dbname = "artworks";

 //Registration values from <Form>
 $username = $_POST['username'];
 $password = $_POST['password'];

 //Database connection
 $db = mysqli_connect(NULL, $user, $pw, $dbname, NULL, $socket);

 if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 }

 //Table Member (id, username,password,reg_date)
 $q = "insert into member values(null, '$username', SHA('$password'), now())";
 mysqli_query($db, $q);

**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) — Dharman, Oct 16 '19 at 17:56

score 2 · Answer 1 · answered Oct 16 '19 at 14:59

There's no specific reason you can't use mysqli_connect.

I do see your database user variable is user and not $user which might be an issue.

Have a look at the cloud sql documentation here and you should be able to adapt to the PHP syntax.

The Node.js setup looks like this:

let pool;
const createPool = async () => {
  pool = await mysql.createPool({
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    // If connecting via unix domain socket, specify the path
    socketPath: `/cloudsql/${process.env.CLOUD_SQL_CONNECTION_NAME}`,
    // If connecting via TCP, enter the IP and port instead
    // host: 'localhost',
    // port: 3306,

    //...
  });
};
createPool();

So the key observation is that the connection name to a socket is stored in an environment variable.

Your PHP might look like this:

$user = "whatever_user";
$password = "whatever_password";
$dbname = "whatever_database";
$socket = '/cloudsql/'.$ENV{"CLOUD_SQL_CONNECTION_NAME"};
$database = mysqli_connect(NULL, $user, $password, $dbname, NULL, $socket);

My Cloud SQL socket is : "mysql:unix_socket=/cloudsql/cc-lab4:australia-southeast1:my-sql-artworks;dbname=artworks"; I am going to try this: $user = "root"; $pw = "root"; $socket = '/cloudsql/'. $ENV{"cc-lab4:australia-southeast1:my-sql-artworks"}; $dbname = "artworks"; $db = mysqli_connect(NULL, $user, $pw, $dbname, NULL, $socket); — Yitasha, Oct 16 '19 at 16:36
Probably the environment variable you specified isn't correct - try the env variable listed in my example, or just use the socket as a string if you're confident it won't change. — PaulProgrammer, Oct 16 '19 at 17:23
Thanks, I am using PDO connection now, and successfull. I gave up on mysqli_connect() , but thank you very much — Yitasha, Oct 17 '19 at 18:31

Chris32 · Answer 2 · 2019-10-16T15:03:40.687

To connect your App Engine to your Cloud SQL instance you just need to specify the User, Password and DNS of your Cloud SQL instance in your App.yaml file, here is an example:

# Use the PHP 7.3 runtime (BETA) by replacing "php72" below with "php73"
runtime: php72

env_variables:
  # Replace USER, PASSWORD, DATABASE, and CONNECTION_NAME with the
  # values obtained when configuring your Cloud SQL instance.
  CLOUDSQL_USER:
  CLOUDSQL_PASSWORD:
  CLOUDSQL_DSN: "mysql:dbname=DATABASE;unix_socket=/cloudsql/CONNECTION_NAME"

For more info you can see the quickstart guide of App Engine in php

score 0 · Accepted Answer · answered Oct 17 '19 at 18:30

In the end, I managed to connect to my Cloud SQL with PDO method.

$dsn = getenv('MYSQL_DSN');
$user = getenv('MYSQL_USER');
$pw = getenv('MYSQL_PASSWORD');

//Database connection
$db = new PDO($dsn, $user, $pw);

//register value from <form> inputs
$username = $_POST['username'];
$password = $_POST['password'];

//insertion success
$statement = $db->prepare("insert into member values(null, '$username', SHA('$password'), now())");
$statement->execute();

My .yaml file configuration

Connecting to Google Cloud SQL in Google App Engine

3 Answers3