CODEWITHSUNDEEP
phpmysqlregexbashsed
Cody Savage
Cody Savage

Reputation: 73

Selectively Using Sed to Replace SQL syntax

I have a bunch of php code I'm scouring through recently and the SQL Syntax varies in case alot. I've been going through it and fixing it to conform for readability.

The current command I'm using is:

sed -i 's/select/SELECT/g;s/from/FROM/g;s/where/WHERE/g;s/limit/LIMIT/g;s/and/AND/g' /master/dashboard.php

The issue however is some of these words pop up in html forms error messages etc and I'm killing time fixing those errors. I'd rather be working smarter than harder.

Assuming every mysql is formatted similar to so

mysqli_query($mysqli_con,"select * from contacts where username='$username' and password='$password' and active='1'");

How do I get sed's regex to search for query part that first then perform replacements inside of it?

Advanced hail mary I'll love you forever question: If I can get regex to use the query as the replacement area is it possible to feed/pipe sed a list of words from a file to enclose in ` characters?

ie: I have a file with contacts, username, password, companies, etc; and sed -> `contacts`, `username`, `password`, `etc`.

Example File Input Snipit:

session_start();
$username = preg_replace("%[^-_.0-9A-Za-z@]%","",$_SESSION['username']);
$password = preg_replace("%[^-! _.0-9A-Za-z@]%","",$_SESSION['password']);
$date = date("r");
$ip = $REMOTE_ADDR;

$query = mysqli_query($mysqli_con,"select * from contacts where username='$username' and password='$password' and active='1'");
$data = mysqli_fetch_array($query);


if((mysqli_num_rows($query) == "0"))
{
    $_SESSION['message'] = "<font color='red'><b>" . _("Invalid login") . ":</b> " . _("The username and password you entered are not valid.") . "</font>";
    header('Location: /index.php');
}
else
{
$_SESSION['username'] = $data['username'];
$_SESSION['password'] = $data['password'];
$_SESSION['accountid'] = $data['accountid'];
$_SESSION['role'] = $data['role'];

$getCompany = mysqli_query($mysqli_con,"select * from companies where companyid='" . $data['accountid'] . "'");
$dataCompany = mysqli_fetch_array($getCompany);

include('/master/html/header.inc');
?>
<script type="text/javascript">
    $(document).ready(function()
    {
        resizeDashboard();

        $(window).resize(function() {

Upvotes: 1

Views: 455

Answers (1)

miken32
miken32

Reputation: 42714

Assuming your queries stay on one line, you can limit sed to look for only those lines:

sed -i '/mysqli_query/s/select/SELECT/...

The first part /mysqli_query/ is an address, such that only matching lines will be affected by the substitution command.

You can replace all the sed commands with one as well:

sed -i -E '/mysqli_query/s/\b(select|from|where|limit|and)\b/\U\1/g' /master/dashboard.php

The \U tells sed to use uppercase until further notice; the \1 is a backreference to the values caught in the parentheses of the search.

The case change is a GNU extension, and won't work if you're using a BSD sed (on macOS for example) or some other unusual sed.

Upvotes: 1

Related Questions