Selectively Using Sed to Replace SQL syntax

Question

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() {

You wON't LIKE the results, WHEREIN SELECTed wORds will be mangled IN seemINgly unLIMITed ways. There are a lot of KEYwORds. — Rick James
– Rick James, Commented Mar 22, 2017 at 5:47

miken32 · Accepted Answer · 2017-10-04 02:04:23Z

1

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.

edited Oct 4, 2017 at 2:04

answered Mar 20, 2017 at 22:21

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cody Savage Over a year ago

This worked! Thank you for the breakdown of the flags and whatnot, I really appreciate it.

Collectives™ on Stack Overflow

Selectively Using Sed to Replace SQL syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related